[MDEV-28936] ERROR 1074 Column Length too long, recommends unobtainable value Created: 2022-06-23  Updated: 2023-04-14

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.6.7
Fix Version/s: 10.6

Type: Bug Priority: Minor
Reporter: Daniel Black Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

10.6.7-MariaDB-log managed by https://aws.amazon.com/rds/


 Description   

The 16383 max size recommended isn't achievable, recommendation should have been 16354.

MariaDB [bb]> alter table bookings modify notes varchar(32000) character set utf8mb4;
 
ERROR 1074 (42000): Column length too big for column 'notes' (max = 16383); use BLOB or TEXT instead
 
MariaDB [bb]> alter table bookings modify notes varchar(16383) character set utf8mb4;
 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 
 
 
MariaDB [bb]> alter table bookings modify notes varchar(16354) character set utf8mb4;
 
Query OK, 257 rows affected (0.147 sec)            
Records: 257  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [bb]> alter table bookings modify notes varchar(16355) character set utf8mb4;
 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 
 
 
CREATE TABLE `bookings` (
 
  `hotel` varchar(10) NOT NULL,
 
  `nights` int(11) DEFAULT NULL,
 
  `customer` varchar(60) DEFAULT NULL,
 
  `price` decimal(40,3) DEFAULT NULL,
 
  `id` varchar(10) NOT NULL,
 
  `checkin` date NOT NULL,
 
  `checkout` date NOT NULL,
 
  `guests` int(11) DEFAULT NULL,
 
  `notes` varchar(16354) CHARACTER SET utf8mb4 DEFAULT NULL,
 
  PERIOD FOR `booking` (`checkin`, `checkout`),
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `hotel` (`hotel`,`booking` WITHOUT OVERLAPS)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1



 Comments   
Comment by Daniel Black [ 2022-07-05 ]

To correct the ER_TOO_BIG_ROWSIZE message for innodb used by CREATE TABLE, a ha_innobase::max_supported_record_length() that returns innodb page size/2 would create an almost (excludes 20 bytes per blob/text/large varchar) accurate error for:

MariaDB [test]> CREATE TABLE `bookings` (    `hotel` varchar(10) NOT NULL,    `nights` int(11) DEFAULT NULL,    `customer` varchar(60) DEFAULT NULL,    `price` decimal(40,3) DEFAULT NULL,    `id` varchar(10) NOT NULL,    `checkin` date NOT NULL,    `checkout` date NOT NULL,    `guests` int(11) DEFAULT NULL,    `notes` varchar(16383) CHARACTER SET utf8mb4 DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

(error emitted by pack_header()).

ER_TOO_BIG_FIELDLENGTH, from Column_definition::prepare_blob_field, is purely looking at the column, so its probably ok as is.

MariaDB [test]> alter table bookings modify notes varchar(32000) character set utf8mb4;
 
ERROR 1074 (42000): Column length too big for column 'notes' (max = 16383); use BLOB or TEXT instead

Generated at Thu Feb 08 10:04:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.