InnoDB ERROR 1118 (42000) – Troubleshooting Row Size Too Large Errors with InnoDB

InnoDB ERROR 1118 (42000) – Troubleshooting Row Size Too Large Errors with InnoDB

InnoDB has a maximum row size in both MySQL and MariaDB, Which is almost equivalent to the half of innodb_page_size value. So what happens when you create a InnoDB row beyond the maximum row size limit ? You will immediately hit the error, ERROR 1118 (42000): 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. We have copied below an example to explain the scenario and also explained work around for the same error in this post:

There are several factors to determine the maximum row size of an InnoDB table. MariaDB also enforces maximum row size limit for all the storage engine and this applies to InnoDB tables also. If you try to create a table that exceeds MariaDB’s global limit on tables maximum row size, then you will see an error like what we have copied below:

What is the root cause of the error, InnoDB ERROR 1118 (42000) – Troubleshooting Row Size Too Large Errors with InnoDB ?

The root cause of this issue is InnoDB has maximum row size that is roughly equivalent to half of innodb_page_size  and you have created a InnoDB row beyond the maximum row size limit. All InnoDB row formats can store certain kinds of data in the overflow pages  and this makes maximum row size of an InnoDB possible than maximum amount of data that can be stored in the row’s main data page. InnoDB only considers using overflow pages if the table’s row size is more than half of innodb_page_sizeand when row size is greater than this, InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.

How to solve this problem ?

There are several ways you can fix this error. If your table is either in REDUNDANT or COMPACT row format, then you can solve by converting table to use DYNAMIC row format.

when using InnoDB’s DYNAMIC row format and a default character set of latin1 (which requires up to 1 byte per character), the 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(256)

when using InnoDB’s DYNAMIC row format and a default character set of utf8 (which requires up to 3 bytes per character), the 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(86):

More hacks to fix this problem

  • Consider breaking down the tables to multiple ones and so columns will be spread among several tables. This solution has worked for most of our customers.
  • Think about refactoring few of your columns to JSON document, MariaDB support JSON
  • The JSON document can be stored in a column which uses one of the following data types:
    • TEXT – Maximum size of TEXT column is 64KB
    • MEDIUMTEXT – Maximum size of MEDIUMTEXT column is 16MB
    • LONGTEXT – Maximum size of LONGTEXT column is 4GB
    • JSON – it’s just an alias for LONGTEXT datatype 

Disabling InnoDB Strict Mode

It’s unsafe to disable InnoDB strict mode and we don’t recommend you this setting . You can disable InnoDB strict mode by setting system variable innodb_strict_mode to OFF

References