0

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.

  • What query are you doing? What MySQL version? some generic limits and innodb limits which you've probably seen. – danblack Feb 1 at 4:42
  • Please provide 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. – Rick James Feb 1 at 5:16
  • @danblack 5.7, I'm using 64K page size, my biggest row is around 850 columns of mostly LONGTEXT. I added my innodb config. – johnramsden Feb 1 at 7:08
  • @RickJames I don't have access to the database right now, I can grab them tomorrow. I know one of the problematic tables is a few BIGINTS and hundreds of LONGTEXT. – johnramsden Feb 1 at 7:15
0

Set the following variable in your MySQL conf file and try

innodb_strict_mode=OFF
  • All this does is hide the warnings and the actual error still there. – johnramsden Feb 1 at 7:03
  • It is not an error. We are using this kind of table for the past 2 years. – jithin giri Feb 1 at 7:07
  • @jihingiri It is an error Error executing 'CREATE TABLE IF NOT EXISTS . Disabling strict mode is not a good idea, it will just hide warnings and errors and let them crop up and bite you later. – johnramsden Feb 1 at 7:12
  • then I am wondering why Mysql 5.6 is allowing this, only from mysql 5.7 is restricting this – jithin giri Feb 1 at 7:51
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.