How to configure MySQL 5.6 LONGBLOB for large binary data

Question

Before I ask my question a little background: I'm doing the Data Export/Import using the MySQL Workbench 6.1 of a MySQL 5.5 database from one machine to a 5.6 on another. both machines are ubuntu one 32-bit the other 64-bit.

I dump the data no problem, but when I try to load it I get the:

ERROR 1118 (42000) at line 1807: 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.

Here is the table create:

CREATE TABLE `file_content` (
  `fileid` bigint(20) NOT NULL,
  `content` LONGBLOB NOT NULL,
  PRIMARY KEY (`fileid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have the following relevant my.cnf settings ...

max_allowed_packet=1G
innodb_file_per_table=1
innodb_file_format=Barracuda

I spend a lot of googling time trying to figure out what the problem is. BTW if I remove the primary key (no problem here, but another table that has a foreign key into this table complains.

As luck would have it, I have the ssh access to the box so I can see actual mysqldb.log. why I find there is really interesting ...

2014-08-12 20:42:12 25246 [ERROR] InnoDB: The total blob data length (14179167) is greater than 10% of the redo log file size (3072). Please increase innodb_log_file_size.

So increasing the redo log file size to 10x LONGBLOB size fixed my issue. However does that mean that to insert a 1G LONGBLOB (thats the actual maximum because of the packet size) I will need a 10G innodb_log_file_size.

Can anyone explain how "redo log size error" turns into "row size too large (>8126)" error.

BTW I have no control over the structure of this db so no "why are you storing large blobs in the database".

TIA

Answer 1

The reason for this issue is a change in MySQL 5.6.20 as one could read in the change log:

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.

To resolve your issue you have to increase the value of the innodb_log_file_size option in your my.ini below the [mysqld] section. Its default value is 48M. Setting it to

[mysqld]
innodb_log_file_size=256M

helped in my case.

Be careful when changing the value of innodb_log_file_size that you do this safely:

1. You need to shut the server down cleanly and normally.
2. Move away (don’t delete) the log files, which are named ib_logfile0, ib_logfile1, and so on.
3. Check the error log to ensure there was no problem shutting down.
4. Then restart the server and watch the error log output carefully. You should see InnoDB print messages saying that the log files don’t exist. It will create new ones and then start.
5. At this point you can verify that InnoDB is working, and then you can delete the old log files.