Row size error with MySQL

Question

I'm running a MySQL server on my Macbook (for testing). Version is 5.6.20 from Homebrew. I started running into "Row size too large" errors, and I've been able to reduce it down to this test case. Table:

mysql> describe test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| stuff | longtext | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

Table status:

mysql> show table status where Name = 'test';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| test | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |            0 |   5242880 |              2 | 2014-08-28 23:51:12 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

The error I'm getting when I try to insert a row into the table where the stuff column has more than 5033932 bytes.

mysql> select length(stuff) from test;
+---------------+
| length(stuff) |
+---------------+
|       5033932 |
+---------------+

mysql> update test set stuff = concat(stuff, 'a');
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

I've searched around for this error, most of the answers involve having too many TEXT columns, and each one has 768 bytes stored inline. As you can see, that is not the case for me. Also, the number 5033932 stays the same regardless of the number of columns I have in the table. In my original application, there were five columns, and the updates still failed when the column size exceeded 5033932.

I've also seen people resolve the issue by switching row formats, which I will try in a bit, but I would like to understand exactly what's causing this error.

Thanks in advance!

Answer 1

A change in the 5.6.20 release notes:

Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.

As a result of the redo log BLOB write limit introduced for MySQL 5.6, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). Failing to do so could result in "Row size too large" errors.

(emphasis mine)

The default for innodb_log_file_size is 50331648, which means the largest BLOB/TEXT value you can create, regardless of data type, is close to 5033164, and you discovered the precise value is 5033932. I suppose internally the calculation involves some fudge factor.

So you need to increase innodb_log_file_size if you want to store larger BLOB/TEXT data. Fortunately, changing the log file size is a lot easier in 5.6 than in earlier versions of InnoDB. Just add a line in your my.cnf with the new value, and restart mysqld.