Webinar | Multcloud Disaster Recovery with MariaDB
Watch Now
Ctrlk
Download MariaDBContact Us
For the complete documentation index, see llms.txt. This page is also available as Markdown.
Edit

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 only old, unsupported MariaDB versions. If you're on a supported 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 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 checks every InnoDB table on a MariaDB server and reports each table whose row size is too large for its row format and the server's page size. It requires Bash and the mariadb command-line client.

For each table, the script creates an empty copy with CREATE TABLE ... LIKE in a temporary database, with InnoDB strict mode enabled, and sets the copy to the row format the original table actually uses. If the copy fails with a "Row size too large" error, the table has the problem. The script does not read or change any data.

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 mariadb client options needed to connect to the server. To keep the password off the command line, put the credentials in an option file and pass it with --defaults-extra-file:

The account needs the SELECT privilege on the tables it checks, and the CREATE, ALTER, and DROP privileges for the temporary database.

When the script runs, it displays the name of the temporary database it creates. The script drops this database when it finishes, or when it is interrupted. If the script is killed before it can clean up, use this name to identify and drop the database manually.

The script outputs one line for each table that has the problem, with the database name, the table name, and the row format. If it finds none, it prints the following message: "No tables with row size too large found." Tables it cannot check are reported as warnings on standard error.

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 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:

The DYNAMIC row format is the default row format. If your tables were originally created on a recent version, 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

For 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

For 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

For 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:

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

spinner
PreviousInnoDB REDUNDANT Row Format
NextInnoDB Tablespaces

Last updated

Was this helpful?