ERROR 1118 (42000): Row size too large (> 8126)

What it means

Error 1118 comes in two forms, for two different limits:

• “The maximum row size for the used table type, not counting BLOBs, is 65535.” The server adds up the most every column could hold, in bytes, and refuses a table whose total is over 65,535. TEXT and BLOB columns count only a few bytes each, because their data is stored separately. With utf8mb4 a varchar(255) counts 1,020 bytes (plus 2 for its length), so a table of 64 of them can be created and one of 65 can’t.
• “Row size too large (> 8126).” InnoDB keeps rows in 16 KB pages, and each row must fit in about half a page. Long values can be moved off the page (only a pointer stays), but only TEXT, BLOB, and VARCHAR or VARBINARY columns that can hold more than 255 bytes. Short CHAR and VARCHAR columns always stay on the page, and if they add up to more than 8,126 bytes the row doesn’t fit.

With innodb_strict_mode on (the default), InnoDB checks the second limit when you create the table, assuming every column is full. MySQL doesn’t always catch it then: in the test below, a table of 100 varchar(100) columns was created, and the error came later, from the first INSERT of a full-length row.

Common causes

1. A very wide table: dozens of varchar(255) columns (form fields, spreadsheet imports, one column per attribute).
2. Many short CHAR or VARCHAR columns in a single-byte character set, which can’t move off the page.
3. An old table with ROW_FORMAT=COMPACT or REDUNDANT, which keeps the first 768 bytes of every long column on the page, so even TEXT columns fill it up. The message then mentions “BLOB prefix of 768 bytes”.
4. Converting to utf8mb4, which multiplies the size every string column counts by four.

How to fix it

Change the widest columns to TEXT

TEXT (up to 64 KB) and MEDIUMTEXT hold their data off the page and count only a few bytes towards both limits:

ALTER TABLE wide MODIFY notes text, MODIFY description text;

Change the largest columns first, until the table fits. The trade-offs: a TEXT column can only be indexed with a prefix, and before MySQL 8.0.13 it couldn’t have a default. Changing a column’s type rebuilds the table; see changing a column type.

Make short columns long enough to move

A column that can hold more than 255 bytes can go off the page; one of 255 bytes or fewer can’t. In the test below, 40 varchar(255) CHARACTER SET latin1 columns failed on insert, while 40 varchar(256) ones worked. With utf8mb4, every varchar of 64 characters or more qualifies.

Move old tables to DYNAMIC

SELECT table_name, row_format FROM information_schema.tables WHERE table_schema = DATABASE();
ALTER TABLE wide ROW_FORMAT=DYNAMIC;

DYNAMIC (the default since MySQL 5.7 and MariaDB 10.2) keeps only a 20-byte pointer for each off-page value, rather than a 768-byte prefix.

Split the table

If many columns are rarely used, or are really one value per attribute, move them to a second table with the same primary key, or to rows of a key-value table, or into one JSON column.

Don’t turn off innodb_strict_mode

With it off, InnoDB creates the table with a warning, and the error comes back later, on the INSERT or UPDATE that writes a row that doesn’t fit. On MariaDB 11.4, the 40 × char(255) table below was created with Warning 139, rows with only an id went in, and a full row failed with 1118. (On MySQL 8.4, changing innodb_strict_mode even for your own session needs the SESSION_VARIABLES_ADMIN privilege.)

Reproduce it

On MySQL 8.4.11 (innodb_page_size 16384, innodb_default_row_format dynamic, innodb_strict_mode on), each table with an id int PRIMARY KEY and:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
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.
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

MariaDB 11.4.13 gave the same messages, but refused the two tables MySQL created and then couldn’t fill (100 × varchar(100) and 40 × varchar(255) latin1) at CREATE TABLE.

In Inlet

Inlet’s structure editor changes column types in one ALTER TABLE, shows the DDL before it runs, and notes that MySQL may copy the table while it runs. When the statement fails, Inlet shows the error and links to this page.