Unable to fix ROW size too large even with innodb_file_per_table

Question

I am having a row length error I've been unable to find a solution for. The error messages are all variations on "Row size too large"

Error executing 'CREATE TABLE IF NOT EXISTS `db`.`table`
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..
    SQL Error: 1118

We might just be hitting the limits about we can do in the way our database is currently constructed. I’ve seen many recommendations in similar situations, nothing has worked.

I realize when it comes to large row size a better solution is vertical partitioning. But at the moment restructuring the database is not an option. Is it possible to fix this in configuration? Some of the answers to similar questions make it seem like it is.

In lots of reports I found people were using VARCHARS, which end up increasing the row sizes, and people recommended moving to TEXT or BLOB, in some of our problematic tables we’re using LONGTEXT, I tried converting them all to TINYTEXT and still ended up with my row size issue

---

What I have tried unsuccessfully, on multiple servers, some of which were with fresh installs of MySQL and fresh databases.

First always make sure the following set, before creating any databases:

innodb_file_format              = Barracuda
innodb_file_per_table           = 1

Then a few of the various changes I tried:

• Increase the log file size up to 8G - ‘innodb_log_file_size = 8G’
• ‘ROW_FORMAT=COMPRESSED’ on an individual table - I was unable to get this to work.
• ‘innodb_default_row_format = dynamic’ globally
• ‘internal_tmp_disk_storage_engine=MyISAM’ - Bug report
• Increase page size ‘innodb_page_size = 64K’ - Doc info

Complete innodb settings:

innodb_file_format              = Barracuda
innodb_large_prefix             = 1
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_thread_concurrency       = 16
innodb_write_io_threads         = 16
innodb_read_io_threads          = 16
innodb_buffer_pool_size         = 256M
innodb_log_file_size            = 8G

---

I did also find a bit of information on a bug report saying that the error is incorrect, and it was supposed to have been corrected, but I’m not sure if that applies here. Maybe we are actually ok, and it can be ignored? When he says it’s not a bug, I’m not sure he means it’s not an error?

[3 Jun 2014 19:28] Daniel Price

The “Row size too large (> 8126)” error is not a bug.

Answer 1

"Hundreds of LONGTEXT" means hundreds of 20-byte pointers to where the text actually is.

If any of those text columns comprise array(s), they should really be rows in another table, not columns. Example: address1, address2, address3...

Otherwise, no amount of compression, file_per_table, etc will avoid the battle between 8KB row size limit and 20 bytes per column.

The 8KB limit can be changed to 16KB, but not further, by changing the block size to 32K or 64K. But this is a rather complex task, and may not suffice.

So, that leaves only "Vertical Partitioning". This is where you pick a group of columns to pull out of the table to put into another table. (Or perhaps several groups to several new tables.)

If some of these text columns are usually empty, then take advantage of that by using NULL, not just 'empty', and not having as many rows in the extra table.

The tables would JOIN on the PRIMARY KEY of the main table. However, if the main id is AUTO_INCREMENT, skip that part of the datatype in the other tables.

If you would like to provide a representative sample of the column names, I may think of other tips.

Answer 2

I've encountered something similar with Azure MySQL version 8.0.

I found a fix was to split a large CREATE statement into first a small CREATE with only the indexed fields, followed by an ALTER ... ADD ... statement for 99% of the rest of the columns.

The ALTER statement does not fail with the "Row size too large (> 8126)" error for some reason, the table appears to be in full working order!

Answer 3

Set the following variable in your MySQL conf file and try

innodb_strict_mode=OFF