Understanding MySQL Row Size Limits and the Maximum Number of TEXT Columns

In a project with many varchar(1000) and varchar(2000) columns, MySQL reported ERROR 1118 (42000): Row size too large (> 8126) because the table had 242 varchar fields exceeding the row size limit.

MySQL imposes two layers of limits: the server layer (maximum row size 65,535 bytes) and the InnoDB storage‑engine layer, which restricts a single record to roughly half of the innodb_page_size (default 16 KB), i.e., about 8 126 bytes.

InnoDB stores rows using B+ trees; to avoid a single row filling an entire page, it requires at least two rows per page, limiting the record size.

Field‑count limits are also defined: the server allows up to 4 096 columns per table, while InnoDB caps it at 1 017 columns.

When converting the many varchar columns to TEXT , the same row‑size error persists because TEXT fields still consume space in the record header and pointers.

TEXT storage differs between COMPACT and DYNAMIC row formats. In COMPACT, the first 768 bytes are stored inline plus a 20‑byte pointer; in DYNAMIC, large values are moved to overflow pages, leaving a 20‑byte pointer.

Using the storage calculations, the maximum number of TEXT columns that fit in a row under strict mode is 196, derived from the inequality:

$5 + ceil(x/8) + 6 + 6 + 7 + x * 41 <= 8126

Testing with a table of 196 TEXT columns succeeded, while 197 columns failed with the same error.

Disabling innodb_strict_mode relaxes the check, allowing up to 402 TEXT columns (using pointer size 20 bytes), but MySQL still issues a warning. The relevant inequality becomes:

$5 + ceil(x/8) + 6 + 6 + 7 + x * 20 <= 8126

Further testing shows that up to 1 017 TEXT columns can be created (the InnoDB column limit), yet inserts may fail because strict mode is off.

The article recommends keeping innodb_strict_mode=ON and, for tables with many fields, considering the JSON data type (available in MySQL 5.7) to store large amounts of data in a single column.

/* Filename:./storage/innobase/dict/dict0dict.cc 第 2504 行 */
if (rec_max_size >= page_rec_max) {   /* 居然是 >= */
    ib::error_or_warn(strict)
        << "Cannot add field " << field->name
        << " in table " << table->name
        << " because after adding it, the row size is "
        << rec_max_size
        << " which is greater than maximum allowed"
        << " size (" << page_rec_max << ") for a record on index leaf page.";
    return(TRUE);
}

create table c_196( f1 text, f2 text, f3 text, ..., f196 text );

create table c_402( f1 text, f2 text, f3 text, ..., f402 text );