// MySQL error reference

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
Error message
Specified key was too long; max key length is %d bytes

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 utf8mb4 VARCHAR, 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

  1. Use the DYNAMIC row format (the default since MySQL 5.7).

    ALTER TABLE users ROW_FORMAT = DYNAMIC;
  2. Index a prefix of the column, which works for non-unique indexes.

    CREATE INDEX idx_users_bio ON users (bio(191));
  3. Shorten the column if long values aren't needed, for example VARCHAR(191) for an indexed email in utf8mb4 under the 767-byte limit.

Related errors

More keys and constraints errors

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.

// fewer errors, faster fixes

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.