MySQL error 1071: Specified key was too long
An index is wider than InnoDB allows (3072 bytes, or 767 bytes with older row formats), typically on a long utf8mb4 VARCHAR.
- Error number
1071- Name
ER_TOO_LONG_KEY- SQLSTATE
42000- Category
- Keys and constraints
What it means
The maximum key length counts bytes, not characters. With utf8mb4 each character can take 4 bytes, so VARCHAR(255) needs up to 1020 bytes per column. InnoDB with the DYNAMIC or COMPRESSED row format allows 3072 bytes per index; the older COMPACT and REDUNDANT formats allow 767, which is where VARCHAR(255) in utf8mb4 fails.
Common causes
- A unique index on a long
utf8mb4VARCHAR, especially on MySQL 5.6 or with an old row format. - A composite index whose columns add up to more than the limit.
How to fix it
-
Use the
DYNAMICrow format (the default since MySQL 5.7).ALTER TABLE users ROW_FORMAT = DYNAMIC;
-
Index a prefix of the column, which works for non-unique indexes.
CREATE INDEX idx_users_bio ON users (bio(191));
-
Shorten the column if long values aren't needed, for example
VARCHAR(191)for an indexed email inutf8mb4under the 767-byte limit.
Related errors
More keys and constraints errors
1062Duplicate entry for key1452Foreign key fails: cannot add child row1451Foreign key fails: cannot delete parent row1216Cannot add or update a child row1217Cannot delete or update a parent row1215Cannot add foreign key constraint3780Foreign key columns are incompatible1822Missing index for foreign key constraint
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.