Fix MySQL ERROR 1118: Row size too large (InnoDB)

Quick answer: Switch the table to DYNAMIC row format or convert large VARCHARs to TEXT, then recreate the table.

I know this error is infuriating — you write a perfectly reasonable CREATE TABLE statement, hit Enter, and MySQL throws ERROR 1118 (42000): Row size too large. The table looks fine, the columns aren't insane, but MySQL refuses. This tripped me up the first time I saw it too, back when I was running a help desk blog and a client's product catalog kept failing to import.

Here's what's happening: MySQL has a hard limit of 65,535 bytes per row. That limit applies to the sum of all column lengths, regardless of storage engine. For InnoDB, the actual bottleneck is often the row format. In the older COMPACT format, each VARCHAR column reserves its full declared length in the row's fixed-size portion, so a table with many VARCHAR(255) columns can blow past the limit quickly.

The fix isn't to redesign your entire schema — it's to change how InnoDB stores the row. Let me walk you through the steps that actually work.

Fix Step 1: Check Your Current Row Format

First, see what row format your table is using. If you get the error during CREATE TABLE, you can check the server default:

SHOW VARIABLES LIKE 'innodb_default_row_format';

If it says COMPACT or REDUNDANT, that's your problem. Those formats store all variable-length columns in the fixed-size part of the row, and they hit the 65,535-byte ceiling way faster.

Fix Step 2: Create the Table with DYNAMIC Row Format

You can override the default per-table. Wrap your CREATE TABLE statement with ROW_FORMAT=DYNAMIC:

CREATE TABLE large_table (
  id INT NOT NULL AUTO_INCREMENT,
  col1 VARCHAR(255),
  col2 VARCHAR(255),
  -- ... more columns ...
  PRIMARY KEY (id)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

DYNAMIC format stores long variable-length columns off-page, so only a 20-byte pointer stays in the row. That frees up massive space. I've seen tables with 30+ VARCHAR(255) columns pass with DYNAMIC when COMPACT failed at column 18.

Fix Step 3: If You Already Have the Table, ALTER It

If the table already exists (maybe you created it before the error appeared in another context), you can convert it:

ALTER TABLE large_table ROW_FORMAT=DYNAMIC;

This rebuilds the table in the background (with online DDL, it's usually non-blocking, but check your MySQL version — 5.7+ handles this well).

Alternative Fix: Convert Big VARCHARs to TEXT

If DYNAMIC row format isn't an option — say you're on MySQL 5.6 and can't change the default — the next best thing is to swap your largest VARCHAR columns for TEXT. TEXT and BLOB columns are always stored off-page in InnoDB, so they don't count toward the 65,535-byte row limit.

CREATE TABLE large_table (
  id INT NOT NULL AUTO_INCREMENT,
  col1 VARCHAR(255),
  col2 TEXT,  -- was VARCHAR(1000)
  PRIMARY KEY (id)
) ENGINE=InnoDB;

Be aware: TEXT columns can't have a default value (unless you're on MySQL 8.0.13+ with expression defaults), and they're stored differently, which can affect performance. But it's a solid workaround.

Alternative Fix: Split the Table

Sometimes the schema design is the real problem. If you have hundreds of columns, or if the columns are semantically separate, consider splitting into two tables with a one-to-one relationship. I've done this for legacy apps where the original developer dumped every field into one table. It's more work, but it's the cleanest long-term fix.

For example, put the frequently accessed columns in the main table, and the rarely used or huge text fields in a secondary table joined by primary key.

Prevention: Set DYNAMIC as the Default

Once you've fixed the immediate error, stop it from happening again. Set DYNAMIC as the default row format for all new InnoDB tables:

SET GLOBAL innodb_default_row_format = DYNAMIC;

Or in your my.cnf / my.ini file, under the [mysqld] section:

innodb_default_row_format = DYNAMIC

This requires a restart to take effect if you put it in the config file, but it's worth it. MySQL 8.0 defaults to DYNAMIC anyway, so if you're on 5.7 or earlier, this is your safety net.

One More Thing: Watch Out for the 255-Character Trap

Here's a subtle gotcha: VARCHAR(255) is a common choice because it's the max for an indexed column with utf8mb4 (which uses 4 bytes per character). But with utf8mb4, VARCHAR(255) actually reserves 1,020 bytes in the row (255 × 4). So 60 of those columns max out the row. If you don't need 255 characters, drop to VARCHAR(191) or VARCHAR(100). It's an easy win.

Note: The 65,535-byte limit applies to the row size, not the storage engine. Even with DYNAMIC, the row can't exceed that total — but because off-page columns only count 20 bytes each, you'll rarely hit it in practice.

Here's a quick reference table to help you decide:

If you've followed these steps and still hit the error, double-check that you're not using a weird charset like utf8mb4 on a huge number of columns. Sometimes the fix is as simple as reducing a few VARCHAR lengths. I've seen plenty of production tables running fine with a mix of VARCHAR and TEXT columns after this change. You've got this.