Troubleshooting Row Size Too Large Errors with InnoDB
Complete InnoDB row size troubleshooting: innodb_strict_mode, ALTER TABLE ROW_FORMAT=DYNAMIC, VARCHAR/VARBINARY(256) overflow, and BLOB/TEXT solutions.
Overview
With InnoDB, users can see the following message as an error or warning:
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.And they can also see the following message as an error or warning in the error log:
[Warning] InnoDB: Cannot add field col in table db1.tab because after adding it,
the row size is 8478 which is greater than maximum allowed size (8126) for a
record on index leaf page.These messages indicate that the table definition allows rows that InnoDB row format can't store.
These messages are raised in the following cases:
If InnoDB strict mode is enabled and if a DDL statement is executed that touches the table, such as CREATE TABLE or ALTER TABLE, InnoDB raises an error with the above message.
If InnoDB strict mode is disabled and if a DDL statement is executed that touches the table, such as CREATE TABLE or ALTER TABLE, InnoDB raises a warning with the above message.
Regardless of whether InnoDB strict mode is enabled, if a DML statement is executed that attempts to write a row that the table's InnoDB row format can't store, InnoDB raises an error with the above message.
Does the Problem Affect me?
The cause of the problem is described here. In very short, it affected old MariaDB versions (up to 10.2.26, 10.3.17, and 10.4.7). If you're on a newer version, the problem could yet still come up if the following applies:
You created tables with a MariaDB version that has the problem.
The tables weren't changed by any DML statements in a newer MariaDB version since then.
If that's the case, you could still face the Row size too large error, particularly when issuing statements like ALTER TABLE or OPTIMIZE TABLE.
Take into account that the Row size too large error can come up for a good reason, for instance, when issuing DDL statements that actually exceed the row size.
For tables created in old MariaDB versions, an additional issue could come up: Tables were created whose row size wasn't calculated correctly. Creating those tables should have failed with the Row size too large error, but didn't.
With such tables, you can get failures, both for DML (when inserted or updated data actually exceed the row size limit), and for DDL operations that should not affect the row size, like TRUNCATE TABLE, CREATE TABLE LIKE, or OPTIMIZE TABLE, or even dropping columns with ALTER TABLE ... DROP COLUMN which makes the row size shorter.
Example of the Problem
Here is an example of the problem:
Root Cause of the Problem
The root cause is that InnoDB has a maximum row size that is roughly equivalent to half of the value of the innodb_page_size system variable. See InnoDB Row Formats Overview: Maximum Row Size for more information.
The InnoDB row formats work around this limit by storing certain kinds of variable-length columns on overflow pages. However, different row formats can store different types of data on overflow pages. Some row formats can store more data in overflow pages than others. For example, the DYNAMIC and COMPRESSED row formats can store the most data in overflow pages. To learn how the various InnoDB row formats use overflow pages, see the following pages:
Checking Existing Tables for the Problem
InnoDB does not currently have an easy way to check all existing tables to determine which tables have this problem. See MDEV-20400 for more information.
One method to check a single existing table for this problem is to enable InnoDB strict mode, and then try to create a duplicate of the table with CREATE TABLE ... LIKE. If the table has this problem, then the operation fails:
Finding All Tables That Currently Have the Problem
The following shell script will read through a MariaDB server to identify every table that has a row size definition that is too large for its row format and the server's page size. It runs on most common distributions of Linux.
To run the script, copy the code below to a shell-script named rowsize.sh, make it executable with the command chmod 755 ./rowsize.sh, and invoke it with the following parameters:
When the script runs, it displays the name of the temporary database it creates, so that if the script is interrupted before cleaning up, the database can be easily identified and removed manually.
As the script runs it will output one line reporting the database and tablename for each table it finds that has the oversize row problem. If it finds none, it will print the following message: "No tables with rows size too big found."
In either case, the script prints one final line to announce when it's done: ./rowsize.sh done.
Solving the Problem
There are several potential solutions available to solve this problem.
Converting the Table to the DYNAMIC Row Format
If the table is using either the REDUNDANT or the COMPACT row format, then one potential solution to this problem is to convert the table to use the DYNAMIC row format instead.
If your tables were originally created on an older version of MariaDB or MySQL, then your table may be using one of InnoDB's older row formats:
In MariaDB 10.1 and before, and in MySQL 5.6 and before, the COMPACT row format was the default row format.
In MySQL 4.1 and before, the REDUNDANT row format was the default row format.
The DYNAMIC row format can store more data on overflow pages than these older row formats, so this row format may actually be able to store the table's data safely. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.
Therefore, a potential solution to the Row size too large error is to convert the table to use the DYNAMIC row format:
You can use the INNODB_SYS_TABLES table in the information_schema database to find all tables that use the REDUNDANT or the COMPACT row formats. This is helpful if you would like to convert all of your tables that you still use the older row formats to the DYNAMIC row format. For example, the following query can find those tables, while excluding InnoDB's internal system tables:
In MariaDB 10.2 and later, the DYNAMIC row format is the default row format. If your tables were originally created on one of these newer versions, then they may already be using this row format. In that case, you may need to try the next solution.
Fitting More Columns on Overflow Pages
If the table is already using the DYNAMIC row format, then another potential solution to this problem is to change the table schema, so that the row format can store more columns on overflow pages.
In order for InnoDB to store some variable-length columns on overflow pages, the length of those columns may need to be increased.
Therefore, a counter-intuitive solution to the Row size too large error in a lot of cases is actually to increase the length of some variable-length columns, so that InnoDB's row format can store them on overflow pages.
Some possible ways to change the table schema are listed below.
Converting Some Columns to BLOB or TEXT
BLOB or TEXTFor BLOB and TEXT columns, the DYNAMIC row format can store these columns on overflow pages. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.
Therefore, a potential solution to the Row size too large error is to convert some columns to the BLOB or TEXT data types.
Increasing the Length of VARBINARY Columns
VARBINARY ColumnsFor VARBINARY columns, the DYNAMIC row format can only store these columns on overflow pages if the maximum length of the column is 256 bytes or longer. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.
Therefore, a potential solution to the Row size too large error is to ensure that all VARBINARY columns are at least as long as varbinary(256).
Increasing the Length of VARCHAR Columns
VARCHAR ColumnsFor VARCHAR columns, the DYNAMIC row format can only store these columns on overflow pages if the maximum length of the column is 256 bytes or longer. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.
The original table schema shown earlier on this page causes the Row size too large error, because all of the table's VARCHAR columns are smaller than 256 bytes, which means that they have to be stored on the row's main data page.
Therefore, a potential solution to the Row size too large error is to ensure that all VARCHAR columns are at least as long as 256 bytes. The number of characters required to reach the 256 byte limit depends on the character set used by the column.
For example, 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):
And 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):
And when using InnoDB's DYNAMIC row format and a default character set of utf8mb4 (which requires up to 4 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(64):
Working Around the Problem
There are a few ways to work around this problem.
If you would like a solution for the problem instead of just working around it, then see the solutions mentioned in the previous section.
Refactoring the Table into Multiple Tables
A safe workaround is to refactor the single wide table, so that its columns are spread among multiple tables.
This workaround can even work if your table is so wide that the previous solutions have failed to solve them problem for your table.
Refactoring Some Columns into JSON
A safe workaround is to refactor some of the columns into a JSON document.
The JSON document can be queried and manipulated using MariaDB's JSON functions.
The JSON document can be stored in a column that uses one of the following data types:
MEDIUMTEXT: The maximum size of a MEDIUMTEXT column is 16 MB.
This workaround can even work if your table is so wide that the previous solutions have failed to solve them problem for your table.
Disabling InnoDB Strict Mode
An unsafe workaround is to disable InnoDB strict mode. InnoDB strict mode can be disabled by setting the innodb_strict_mode system variable to OFF.
For example, even though the following table schema is too large for most InnoDB row formats to store, it can still be created when InnoDB strict mode is disabled:
But as mentioned above, if InnoDB strict mode is disabled and if a DDL statement is executed, then InnoDB will still raise a warning with this message. The SHOW WARNINGS statement can be used to view the warning:
As mentioned above, even though InnoDB is allowing the table to be created, there is still an opportunity for errors. Regardless of whether InnoDB strict mode is enabled, if a DML statement is executed that attempts to write a row that the table's InnoDB row format can't store, then InnoDB will raise an error with this message. This creates a somewhat unsafe situation, because it means that the application has the chance to encounter an additional error while executing DML.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?