🚨 MySQL Error: 'ERROR 1118 (42000): Row size too large' — Cause, Fix, and Troubleshooting Guide

Overview

ERROR 1118 (42000) means a single row exceeds InnoDB’s internal size limit for the current row format and page size:

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.

InnoDB stores a row within a 16KB page, and roughly half a page (~8126 bytes) must hold the row’s inline data. Tables with many wide VARCHAR/CHAR columns can blow past that even when no individual value is large. TEXT/BLOB columns can be stored off-page, which is why the message suggests converting to them.

Symptoms

• A CREATE TABLE or ALTER TABLE ADD COLUMN fails with 1118.
• The table has dozens of VARCHAR(255) columns or several CHAR(255) columns.
• The error mentions “BLOB prefix of 0 bytes is stored inline” — a hint that ROW_FORMAT is REDUNDANT or COMPACT.

Common Root Causes

1. Too many wide columns for the page

CREATE TABLE wide (
  id INT PRIMARY KEY,
  c1 VARCHAR(255), c2 VARCHAR(255) /* ... many more ... */
) CHARACTER SET utf8mb4;

Under utf8mb4 each VARCHAR(255) reserves up to 1020 bytes of inline metadata; a few dozen of them exhaust the ~8KB budget.

2. ROW_FORMAT=COMPACT/REDUNDANT stores column prefixes inline

COMPACT and REDUNDANT keep a 768-byte prefix of every variable-length column on the page. DYNAMIC/COMPRESSED store only a 20-byte pointer, freeing the page.

3. Wide CHAR columns

CHAR(255) under utf8mb4 is a fixed 1020 bytes on the page — far more expensive than VARCHAR.

How to diagnose

Check the table’s current row format and the server default:

SELECT NAME, ROW_FORMAT FROM information_schema.INNODB_TABLES
WHERE NAME = 'appdb/wide';

SELECT @@innodb_default_row_format;

Estimate the widest columns:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='appdb' AND TABLE_NAME='wide'
ORDER BY CHARACTER_MAXIMUM_LENGTH DESC;

Fixes

Switch to ROW_FORMAT=DYNAMIC, which stores long-column overflow off-page (this alone resolves most cases):

ALTER TABLE wide ROW_FORMAT=DYNAMIC;

Make it the server default so new tables inherit it:

[mysqld]
innodb_default_row_format = DYNAMIC

Convert genuinely large columns to TEXT/BLOB, which store off-page:

ALTER TABLE wide MODIFY notes TEXT;

If the row is simply modeling too much, split rarely-used wide columns into a companion 1:1 table:

CREATE TABLE wide_detail (
  wide_id INT PRIMARY KEY,
  extra_json JSON,
  FOREIGN KEY (wide_id) REFERENCES wide(id)
);

What to watch out for

• ROW_FORMAT=DYNAMIC requires the file-per-table and Barracuda formats — defaults on modern MySQL, but verify innodb_file_per_table=ON on older installs.
• The limit scales with page size: innodb_page_size=8k halves the budget again. Do not shrink page size on wide tables.
• utf8mb4 triples byte reservations versus latin1; over-wide VARCHAR(255) columns that only hold ASCII are the usual culprit — right-size them.
• ALTER TABLE ... ROW_FORMAT=DYNAMIC rebuilds the table; schedule it and consider an online DDL tool for large tables.

Related

• ERROR 1071: Specified key was too long — the index-length cousin, also driven by wide utf8mb4 columns.
• ERROR 1406: Data too long for column — a per-value length overflow rather than a whole-row limit.
• ERROR 1114: The table is full — a different capacity ceiling worth ruling out.