blob data length is greater than 10% of the total redo log size

On MySQL 5.6.20, and above you might be getting errors when trying to import tables, databases, if you innodb_log_file_size is too small.

If you are seeing error message like this, you will need to increase your innodb_log_file_size inside your my.cnf or my.ini MySQL configuration file.

The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.
2015-09-07 17:29:33 12298 [ERROR] InnoDB: The total blob data length (10066357) is greater than 10% of the total redo log size (100663296). Please increase total redo log size.

As of MySQL version 5.6.20 changes were implemented in regards to InnoDB and BLOB data size in you tables, and you InnoFB log should be at least 10 times higher 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).

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html#mysqld-5-6-20-innodb

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

Once you increase innodb_log_file_size inside your my.cnf or my.ini file, error should be resolved.

2015-09-07 17:29:38 5345 [Note] InnoDB: Highest supported file format is Barracuda.
2015-09-07 17:29:38 5345 [Warning] InnoDB: Resizing redo log from 2*3072 to 2*65536 pages, LSN=702949287837
2015-09-07 17:29:38 5345 [Warning] InnoDB: Starting to delete and rewrite log files.
2015-09-07 17:29:38 5345 [Note] InnoDB: Setting log file ./ib_logfile101 size to 1024 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
2015-09-07 17:29:42 5345 [Note] InnoDB: Setting log file ./ib_logfile1 size to 1024 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
2015-09-07 17:29:46 5345 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-09-07 17:29:46 5345 [Warning] InnoDB: New log files created, LSN=702949287837

Reference:

http://stackoverflow.com/questions/25277452/how-to-configure-mysql-5-6-longblob-for-large-binary-data

2 thoughts on “blob data length is greater than 10% of the total redo log size”

Leave a Reply