ERROR 1071 (42000): Specified Key Was Too Long; Max Key Length Is 767 Bytes in MySQL

Error Message#

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

You'll also see the 3072 bytes variant on newer servers, where InnoDB allows a larger maximum index key length (for example with DYNAMIC/COMPRESSED row formats):

The number is the maximum index length in bytes for the storage engine and row format in play. The error is raised at DDL time — CREATE TABLE, ADD INDEX, ADD PRIMARY KEY, or ADD UNIQUE — never during normal DML. Because the limit is in bytes, not characters, the character ceiling depends entirely on the column's charset: under utf8mb4 (up to 4 bytes/char) a 767-byte limit is only 191 characters, and a 3072-byte limit is 768 characters.

What Triggers This Error#

1071 fires when the bytes required to index one or more columns exceed the engine's per-index maximum:

• A VARCHAR(255) index under utf8mb4 — the classic case. 255 × 4 = 1020 bytes, over the 767-byte limit on older InnoDB row formats. This is why migrations that ran fine under utf8 break after a charset switch to utf8mb4.
• Old InnoDB row format (REDUNDANT or COMPACT) capped at 767 bytes, often because innodb_large_prefix is off on MySQL 5.6 / early 5.7
• A composite index whose columns sum past the limit — three VARCHAR(100) utf8mb4 columns = 1200 bytes
• CHAR/VARCHAR columns with a wide charset (utf8mb4, utf16) where the byte length is 2–4× the declared length
• A primary key on a long string column — PKs are indexes too and hit the same ceiling
• Framework defaults — older Rails/Django/Laravel migrations created string columns as VARCHAR(255) and then indexed them, generating 1071 the moment the database charset is utf8mb4
• MyISAM's 1000-byte limit — even lower than InnoDB; rarely seen today but still possible on legacy tables

Fix by Scenario#

A VARCHAR(255) UNIQUE/index under utf8mb4 (the common one)#

CREATE TABLE users (
  email VARCHAR(255) NOT NULL,
  UNIQUE KEY uniq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ERROR 1071: Specified key was too long; max key length is 767 bytes

The robust fix on any modern server is to enable the larger index prefix so 3072-byte keys are allowed, then shrink the column to a realistic size. On MySQL 5.7.7+ / 8.0 the defaults already support 3072 bytes when the table uses the DYNAMIC row format and innodb_default_row_format is DYNAMIC (the default). Make the row format explicit if you're unsure:

CREATE TABLE users (
  email VARCHAR(255) NOT NULL,
  UNIQUE KEY uniq_email (email)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4;

Even with 3072 bytes available, prefer to size the column to reality — an email column does not need 255 utf8mb4 characters:

ALTER TABLE users MODIFY email VARCHAR(191) NOT NULL;  -- 191 × 4 = 764 bytes, fits even 767

191 is the well-known safe maximum for a fully-indexed utf8mb4 column under the 767-byte limit, which is why older frameworks adopted it as the default index length.

Legacy server stuck at 767 bytes (5.6 / early 5.7)#

If you can't change column sizes, raise the limit at the server level:

SET GLOBAL innodb_large_prefix = ON;       -- 5.6 / 5.7 only; removed in 8.0 (always on)
SET GLOBAL innodb_file_format = Barracuda;  -- required for large prefix
SET GLOBAL innodb_file_per_table = ON;

Then create the table with ROW_FORMAT=DYNAMIC or COMPRESSED so the 3072-byte prefix applies. Persist these in my.cnf so they survive a restart. On MySQL 8.0 these knobs are gone — the larger prefix is always available with DYNAMIC/COMPRESSED row formats.

A prefix index is the right answer (long text columns)#

When you genuinely need to index a column longer than the limit — a URL, a long descriptor — index only a prefix of it rather than the whole column:

-- Index the first 191 characters instead of the full column
ALTER TABLE pages ADD INDEX idx_url (url(191));

-- For a UNIQUE constraint on long data, a prefix unique index enforces
-- uniqueness on the prefix only — verify that's acceptable for your data:
ALTER TABLE pages ADD UNIQUE KEY uniq_url_prefix (url(191));

A prefix UNIQUE enforces uniqueness on the prefix bytes only, so two values that differ only after character 191 would collide. If full-value uniqueness matters, store and index a hash instead (next scenario).

Composite index that overflows in total#

ALTER TABLE memberships ADD UNIQUE KEY uniq_org_email (org_slug, email);
-- org_slug VARCHAR(100) + email VARCHAR(255), utf8mb4 → way over the limit

Shrink the columns to realistic lengths, use prefixes on the wide parts, or — for guaranteed uniqueness on long composite keys — index a generated hash column:

ALTER TABLE memberships
  ADD COLUMN org_email_hash BINARY(32)
    AS (UNHEX(SHA2(CONCAT(org_slug, '\\0', email), 256))) STORED,
  ADD UNIQUE KEY uniq_org_email_hash (org_email_hash);

A BINARY(32) SHA-256 is 32 bytes regardless of input length, enforces exact full-value uniqueness, and stays well under any index limit. Keep a regular (non-unique) index on the raw columns if you also query by them.

Framework migration generates the index (Rails / Django / Laravel)#

The fix is to make the framework emit a shorter indexed length, not to hand-edit the database:

• Laravel — set Schema::defaultStringLength(191) in AppServiceProvider::boot(); this is the documented utf8mb4 fix.
• Rails — specify limit: 191 on the string column, or add the index with length: 191.
• Django — use max_length=191 on the CharField you intend to index under utf8mb4.

After changing the default, regenerate/re-run the migration against a utf8mb4 database.

Prevention#

• Size indexed string columns to reality — VARCHAR(191) (or smaller) for anything you index under utf8mb4; reserve VARCHAR(255) for columns you never index.
• Standardize on MySQL 8.0 (or 5.7 with DYNAMIC row format + large prefix) so 3072-byte keys are available, and make ROW_FORMAT=DYNAMIC explicit in CREATE TABLE for clarity.
• For long URLs/descriptors, decide up front between a prefix index (fast, prefix-only uniqueness) and a hashed column (exact uniqueness, 32 bytes).
• Set the framework's default indexed string length (Laravel defaultStringLength, Rails length:, Django max_length) once, so new migrations don't reintroduce 1071.
• Test schema migrations against a database created with the production charset (utf8mb4), not the framework's historical utf8 default — 1071 only appears under the wider charset.