MySQL Error 1118 (Row size too large) when restoring Django-mailer database

Question

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

Frascati

May be this helps MySQL "Row size too large" when saving many text fields – Uniformize

Answer 1

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:

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

Obvert

Thanks, this worked for me as well, but at first I was reluctant to increase the limits to the size suggested. I had 20 meg files stored as blobs in InnoDB, and I really had to increase the log_file_size to 200M and the buffer size to 800M to get them imported correctly. Sick. – Spindry

Answer 2

If this is useful for anybody, the @klasske solution did not work for me, however writing this line in 'my.cnf' did:

innodb_file_format=Barracuda

Leshalesher

What does this actually do? – Tomi

Allows the database to create tables in the newer Barracuda format. But this doesn't mean it will (depending on your version of MySQL and other settings). You may also need to add innodb_file_per_table and have ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC in your table creation statement. This solved the rowsize issue for me on MySQL 5.5 – Vondavonni

Answer 3

I encountered the same error in my project. I tried a lot of suggestions, such as increasing innodb_log_file_size, innodb_buffer_pool_size or even disabling strict mode innodb_strict_mode=0 in the my.cnf file, but nothing worked for me.

What worked for me was the following:

1. Changing the offending CharFields with a big max_length to TextFields. For example, models.CharField(max_length=4000) to models.TextField(max_length=4000)
2. Splitting the table into multiple tables after the first solution wasn't enough on its own.

It was only after doing that I got rid of the error.

---

Recently, the same error haunted me again on the same project. This time, when I was running python manage.py test. I was confused because I had already split the tables and changed the CharFields to TextFields.

So I created another dummy Django project with a different database from my main project. I copied the models.py from the main project into the dummy project and run migrate. To my surprise, everything went fine.

It dawned on me that something could be wrong with my main project migrations. Perhaps running manage.py test uses my earlier migrations with the offending CharFields? I don't know for sure.

So I disabled the migrations when running tests by editing settings.py and adding the following snippet at the end the file. It disables the migrations when testing and solves the error.

class DisableMigrations(object):                                                                                                                     
                                                                                                                                                     
    def __contains__(self, item):                                                                                                                    
        return True                                                                                                                                  
                                                                                                                                                     
    def __getitem__(self, item):                                                                                                                     
        return None                                                                                                                                  
                                                                                                                                                     
                                                                                                                                                     
if 'test' in sys.argv[1:]:                                                                                                                           
    MIGRATION_MODULES = DisableMigrations() 

Doing that solved the problem for me when testing. I hope someone else finds it useful.

Source for the snippet settings_test_snippet.py

Jamestown

Answer 4

ERROR 1118 (42000) at line 1852: 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.
[mysqld]
innodb_log_file_size = 512M
innodb_strict_mode = 0

ubuntu 16.04 edit path : nano /etc/mysql/mysql.conf.d/mysqld.cnf
it work!!….

[http://dn59-kmutnb.blogspot.com/2017/06/error-1118-42000-at-line-1852-row-size.html][1]

Antipater