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
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
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?
The “Row size too large (> 8126)” error is not a bug.