MySQL Error 1118 (Row size too large) when restoring Django-mailer database
I dumped a working production database from a django app and am trying to migrate it to my local development environment. The production server runs MySQL 5.1, and locally I have 5.6.
When migrating the django-mailer's "messagelog" table, I'm running into the dreaded Error 1118:
ERROR 1118 (42000) at line 2226: 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.
I've read lots of stuff online about this error, but none of it has solved my problem.
N.B. This error is not coming from the creation of the table, but rather the insertion of a row with pretty large data.
Notes:
- The innodb_file_format and innodb_file_format_max variables are set to Barracuda.
- The ROW_FORMAT is set to DYNAMIC on table creation.
The table does not have very many columns. Schema below:
+----------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message_data | longtext | NO | | NULL | | | when_added | datetime | NO | | NULL | | | priority | varchar(1) | NO | | NULL | | | when_attempted | datetime | NO | | NULL | | | result | varchar(1) | NO | | NULL | | | log_message | longtext | NO | | NULL | | +----------------+------------+------+-----+---------+----------------+
Again, the error happens ONLY when I try to insert a quite large (message_data is about 5 megabytes) row; creating the table works fine, and about 500,000 rows are added just fine before the failure.
I'm out of ideas; I've tried DYANMIC and COMPRESSED row formats, and I've triple checked the values of the relevant innodb variables:
mysql> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
The creation code (from SHOW CREATE TABLE) looks like:
CREATE TABLE `mailer_messagelog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message_data` longtext NOT NULL,
`when_added` datetime NOT NULL,
`priority` varchar(1) NOT NULL,
`when_attempted` datetime NOT NULL,
`result` varchar(1) NOT NULL,
`log_message` longtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=869906 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
Answer
According to one of the answers to this question, your problem might be caused by changes in MySQL 5.6 (see the InnoDB Notes on http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html):
InnoDB Notes
Important Change: 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, the innodb_log_file_size setting should be 10 times larger than 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). No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data.
Note In MySQL 5.6.22, the redo log BLOB write limit is relaxed to 10% of the total redo log size (innodb_log_file_size * innodb_log_files_in_group).
(Bug #16963396, Bug #19030353, Bug #69477)
Does it help if you change innodb_log_file_size to something bigger than 50M? (Changing that variable needs some steps to work correctly: