Greg Humphreys Greg Humphreys - 6 months ago 74
MySQL Question

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:


  1. The innodb_file_format and innodb_file_format_max variables are set to Barracuda.

  2. The ROW_FORMAT is set to DYNAMIC on table creation.

  3. 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).

Does it help if you change innodb_log_file_size to something bigger than 50M? (Changing that variable needs some steps to work correctly:

http://dba.stackexchange.com/questions/1261/how-to-safely-change-mysql-innodb-variable-innodb-log-file-size ).

Comments