MySQL Maximum Row Size Limit Explained

This article provides a clear overview of the maximum row size limits in MySQL. It details the hard limits imposed by the MySQL server, the engine-specific restrictions of the default InnoDB storage engine, and practical solutions for handling “Row size too large” errors.

The MySQL Server Row Limit: 65,535 Bytes

At the database engine level, MySQL enforces an absolute maximum row size limit of 65,535 bytes for any standard table. This limit applies to the cumulative total of all columns in a single row.

Even if your storage engine can theoretically support larger rows, the MySQL server boundary cannot be exceeded. However, there are two important caveats to this limit:

• NULL Columns: MySQL uses a small amount of space (usually 1 bit per NULL-allowed column, rounded up to the nearest byte) to track NULL values, which slightly reduces the actual usable byte limit.
• BLOB and TEXT Columns: Large data types like BLOB and TEXT do not store their entire contents directly inside the row. Instead, MySQL stores a 9- to 12-byte pointer inside the row, while the actual content is stored in off-page memory. This means you can store megabytes of data in these columns without hitting the 65,535-byte row limit.

The InnoDB Storage Engine Limit: 8,126 Bytes

Because InnoDB is the default and most widely used storage engine in MySQL, its internal limits are often more restrictive in practice than the global MySQL limit.

InnoDB stores data in “pages,” with a default page size of 16KB. To ensure database efficiency, InnoDB requires that at least two rows fit into a single page. Consequently, the maximum row size for an InnoDB table is slightly less than half a page—specifically 8,126 bytes.

If you attempt to define a table row that exceeds 8,126 bytes of inline data, MySQL will trigger a “Row size too large” error.

Row Formats and Off-Page Storage

How InnoDB handles columns that exceed the page limit depends entirely on the table’s ROW_FORMAT.

• DYNAMIC and COMPRESSED: Under these modern row formats (which are the default in MySQL 5.7 and later), InnoDB automatically stores long variable-length columns (like VARCHAR, VARBINARY, BLOB, and TEXT) off-page. It leaves only a 20-byte pointer in the main row page. This allows you to easily bypass the 8,126-byte limit.
• COMPACT and REDUNDANT: In these older row formats, InnoDB attempts to store the first 768 bytes of variable-length columns directly in the row page. This legacy behavior quickly exhausts the 8,126-byte limit if you have multiple wide VARCHAR columns.

How to Avoid Row Size Limit Errors

If you encounter row size limits in MySQL, you can resolve them using the following best practices:

1. Convert VARCHAR to TEXT: Change large VARCHAR columns to TEXT or BLOB types. This forces MySQL to store the data off-page, reducing the inline row size to a small pointer.

2. Use DYNAMIC Row Format: Ensure your tables are defined with ROW_FORMAT=DYNAMIC. You can alter an existing table using:

   ALTER TABLE table_name ROW_FORMAT=DYNAMIC;

3. Normalize Your Database: If a table has too many columns, normalize your database schema by splitting the wide table into two or more smaller tables linked by a one-to-one relationship.