CREATE TABLE or ALTER TABLE fails with Row size too large
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 BLOBsShort 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.
Fix it fast
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';2Match what you see, then jump to its fix
- 1
The 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
The message says 'Row size too large (> 8126)' and the table has many CHAR columns.Wide fixed-length columns fill the InnoDB pageFix - 3
ROW_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
- 1
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 BLOBsERROR 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\GConfirmed by dev.mysql.com
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\GConfirmed by dev.mysql.com
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix