Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

CREATE TABLE or ALTER TABLE fails with Row size too large

ErrorERROR 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

Short answer

Error 1118 means the declared columns cannot fit in one row. There are two limits: 65,535 bytes for the whole row in MySQL itself, and about half a data page (just under 8 KB by default) for what InnoDB keeps inside the page. Look at which message you got, then turn big VARCHAR columns into TEXT or shorten them.

  • Applies to MySQL 8.4, InnoDB, 16 KB pages
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Compare the number in the message. 65535 means the MySQL-wide limit; a smaller figure such as 8126 means the InnoDB in-page limit. Then list the columns of the table that fails.

    SHOW CREATE TABLE my_table\G
    SELECT COLUMN_NAME, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'my_table';
  2. 2Match what you see, then jump to its fix

    1. 1The message ends with 'is 65535. This includes storage overhead' and the table has many wide VARCHAR columns.The VARCHAR columns add up to more than 65,535 bytesFix
    2. 2The message says 'Row size too large (> 8126)' and the table has many CHAR columns.Wide fixed-length columns fill the InnoDB pageFix
    3. 3ROW_FORMAT=COMPACT or REDUNDANT appears in SHOW CREATE TABLE, often on a table created by an old dump.The table uses an older row format that keeps a prefix of long columns in the rowFix

What does the error look like?

  • 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.

Cause 1 of 3

The VARCHAR columns add up to more than 65,535 bytes

1Check

Add up the bytes, not the characters. Each VARCHAR counts its length times the largest byte size of its character set, plus length bytes. The manual's own figure is 255 x 3 + 2 = 767 bytes for a utf8mb3 VARCHAR(255), so a utf8mb4 column is even larger.

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND DATA_TYPE = 'varchar'
ORDER BY CHARACTER_MAXIMUM_LENGTH DESC;

2Fix

Change the columns that really hold long text to TEXT. A TEXT or BLOB column adds only 9 to 12 bytes to the row size limit because its content is stored apart from the row. Shorten the other VARCHAR columns to what the data needs.

ALTER TABLE my_table
  MODIFY notes TEXT,
  MODIFY address VARCHAR(255);

3Verify

The statement that failed before now runs without error.

SHOW CREATE TABLE my_table\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

Wide fixed-length columns fill the InnoDB page

1Check

InnoDB must keep a row, minus whatever it can push off-page, inside slightly less than half a 16 KB page. Variable-length columns can move off-page, but CHAR columns stay inline. The manual's example fails with 33 columns of CHAR(255).

SHOW VARIABLES LIKE 'innodb_page_size';
SELECT COUNT(*) AS char_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'my_table' AND DATA_TYPE = 'char';

2Fix

Make the wide CHAR columns VARCHAR, shorten them, or move rarely used columns into a second table joined by the primary key.

ALTER TABLE my_table
  MODIFY code_a VARCHAR(255),
  MODIFY code_b VARCHAR(255);

3Verify

Re-run the original statement; the (> 8126) message no longer appears.

SHOW CREATE TABLE my_table\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The table uses an older row format that keeps a prefix of long columns in the row

1Check

COMPACT and REDUNDANT keep the first 768 bytes of every long variable-length value in the row. DYNAMIC can store long values fully off-page and leaves only a 20-byte pointer. The default for new tables is DYNAMIC.

SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = 'mydb/my_table';

2Fix

Rebuild the table as DYNAMIC. Put ROW_FORMAT=DYNAMIC in the CREATE TABLE for a dump that fails on import.

ALTER TABLE my_table ROW_FORMAT=DYNAMIC;

3Verify

The row format column now reads Dynamic.

SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = 'mydb/my_table';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Pick VARCHAR lengths from the real data, not 255 for everything, and use TEXT for free-form content.
  • Do not set a table's ROW_FORMAT to COMPACT or REDUNDANT unless an old server needs it.
  • Keep rarely read wide columns in a side table instead of one very wide table.
  • Remember that a character set with more bytes per character makes the same VARCHAR(n) bigger.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.