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.
SHOW CREATE TABLE
; then we can explain what direction to go. We need to see how many text tables, and exactly what datatypes, plus other things.