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:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S3 (Non-critical)
Version:5.5/5.6 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[28 May 2013 17:04] Ryan Lowe
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
[28 May 2013 21:47] Miguel Solorzano
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] Miguel Solorzano
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)