| Bug #69336 | "Row size too large" error thrown even when it's not. | ||
|---|---|---|---|
| Submitted: | 28 May 2013 17:04 | Modified: | 3 Jun 2014 19:28 |
| Reporter: | Ryan Lowe | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.5/5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[28 May 2013 21:47]
MySQL Verification Team
Thank you for the bug report. `col225` text, `col226` text, `col227` text, `col228` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC 1 row in set (0.02 sec) mysql> SHOW VARIABLES LIKE "%versio%"; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | protocol_version | 10 | | version | 5.1.71-debug | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | unknown-linux-gnu | +-------------------------+---------------------+ 5 rows in set (0.01 sec) [miguel@tikal 5.5]$ bi/mysql -uroot test bash: bi/mysql: No such file or directory [miguel@tikal 5.5]$ bin/mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.33-debug Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> source /home/miguel/build0522/5.5/bin/m2b.sql Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.07 sec) ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. mysql> [miguel@tikal build0522]$ 5.6/bin/mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.13-debug Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> source /home/miguel/build0522/m2b.sql Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.01 sec) Query OK, 0 rows affected (0.08 sec) ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. mysql> alter table foo engine InnoDB; -- removing row_format dynamic Query OK, 0 rows affected (1.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table foo engine InnoDB row_format = dynamic; ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. mysql>
[25 Nov 2013 11:28]
Arnaud Adant
Reproduced with the 5.1.73 + plugin. ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
[25 Nov 2013 13:00]
Arnaud Adant
Note that the problem occurs in 2 cases : - innodb_strict_mode = 1, whatever the row_format - innodb_strict_mode = 0, row_format=compressed or row_format=dynamic
[3 Jun 2014 19:28]
Daniel Price
The "Row size too large (> 8126)" error is not a bug. "This behavior is expected because inoodb design requires the record to fit in the B-tree leaf page and if unsuccessful we return error. During the creation of BLOB or TEXT field we allocate 41 Bytes (dynamic or compressed row format) for the field which is considered for row size calculation. During insertion if the BLOB or TEXT is less than 40 Bytes we store it internally with in these 41 bytes and if it is more than 40 bytes, we store it off page and store 20 Bytes reference to the page." The following changelog entry has been added to the 5.7.5 release notes: The error log message that is printed on "CREATE TABLE" when the number of "BLOB" or "TEXT" fields exceed the row size limit did not provide sufficient information. The error message now provides the maximum row size, current row size, and the field that causes the maximum row size to be exceeded. Reference Manual updates were made to the following pages: http://dev.mysql.com/doc/refman/5.5/en/innodb-row-format-dynamic.html http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html http://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html http://dev.mysql.com/doc/refman/5.5/en/innodb-compression-internals.html http://dev.mysql.com/doc/refman/5.6/en/innodb-compression-internals.html http://dev.mysql.com/doc/refman/5.7/en/innodb-compression-internals.html "For tables created in "ROW_FORMAT=DYNAMIC" or "ROW_FORMAT=COMPRESSED", "TEXT" and "BLOB" columns that are less than or equal to 40 bytes are always stored in-line. "
[18 Jun 2015 11:00]
MySQL Verification Team
http://bugs.mysql.com/bug.php?id=77398 marked as duplicate of this one.
[14 Aug 2018 10:34]
Valeriy Kravchuk
The statement about error message fixed is not true, neither in recent 5.7, nor in 8.0.12:
...
-> , `h_7` tinyint unsigned null
-> , `h_8` tinyint unsigned null
-> , `h_9` tinyint unsigned null
, primary key (element_id, daytime)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT= -> , primary key (element_id, daytime)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=dynamic;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12 |
+-----------+
1 row in set (0,00 sec)
Description: Run the following script and then run the resulting .sql file against the database. It will throw the following error: ERROR 1118 (42000) at line 239: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. This, even though there are only 228 text columns ... How to repeat: SCRIPT ===== #!/bin/sh n=$1 : ${n:=228} cat<<GO > m2b.sql use test; set global innodb_file_format=Barracuda; set global innodb_file_format_max=Barracuda; set global innodb_file_per_table=1; drop table if exists foo; create table foo ( id int auto_increment primary key, v varchar(32) GO for i in `seq 1 $n` ; do echo ", col$i text" done >> m2b.sql echo ") ENGINE=MyISAM;" >> m2b.sql echo "alter table foo engine=innodb row_format=dynamic;" >> m2b.sql