🚨 MySQL Error Guide: 'Specified key was too long; max key length is 767 bytes' — Fix Index Limits

Overview

MySQL raises error 1071 when the total byte length of an index exceeds InnoDB’s per-index maximum:

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

On newer servers with the larger prefix enabled, the same error names a bigger cap:

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

The number is a byte limit, not a character count. Because utf8mb4 uses up to 4 bytes per character, a VARCHAR(255) column can consume up to 1020 bytes — which is why this error explodes the moment a schema migrates from utf8/latin1 to utf8mb4.

Symptoms

• CREATE TABLE or CREATE INDEX fails on a VARCHAR column that indexed fine before a charset change.
• A PRIMARY KEY (email) or UNIQUE(slug) on a long VARCHAR(255) is rejected.
• A framework migration (WordPress, Rails, Laravel, Django) fails only on older MySQL 5.6/5.7 or MariaDB with default settings.
• A composite index over several string columns fails even though each column alone is fine — their byte lengths sum.

Common Root Causes

• utf8mb4 multiplies bytes — VARCHAR(255) × 4 bytes = 1020 bytes, over both the 767 and 3072 caps for a single-column index.
• The 767-byte legacy limit — MySQL 5.6 and older, or InnoDB tables in REDUNDANT/COMPACT row format, cap index prefixes at 767 bytes.
• innodb_large_prefix disabled — on 5.6/5.7 the 3072-byte limit requires innodb_large_prefix=ON, innodb_file_format=Barracuda, and ROW_FORMAT=DYNAMIC or COMPRESSED.
• Composite index byte sum — several string columns in one index add up past the cap.
• Indexing a full long column instead of a prefix when the full length is unnecessary for selectivity.

Diagnostic Workflow

Identify the column’s charset and effective byte width — this is usually the whole story:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'app' AND TABLE_NAME = 'users';

Check the table’s row format and the server’s prefix settings (these decide whether your cap is 767 or 3072):

SELECT TABLE_NAME, ROW_FORMAT, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app' AND TABLE_NAME = 'users';

SHOW VARIABLES LIKE 'innodb_large_prefix';      -- 5.6/5.7 only
SHOW VARIABLES LIKE 'innodb_file_format';        -- 5.6/5.7 only
SHOW VARIABLES LIKE 'innodb_default_row_format'; -- 5.7+ / 8.0

Estimate the index byte length: (character length) × (bytes per char for the charset). For utf8mb4 that is ×4, for utf8/utf8mb3 ×3, for latin1 ×1.

Inspect the failing index definition:

SHOW CREATE TABLE users\G

Example Root Cause Analysis

A WordPress-style migration converting the database from utf8 to utf8mb4 failed on MySQL 5.7 when re-creating a UNIQUE index on VARCHAR(255) option_name:

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

The math: 255 characters × 4 bytes (utf8mb4) = 1020 bytes, well over 767. The table was in COMPACT row format, so it was pinned to the legacy 767-byte cap.

Two fixes were viable. The durable one raised the effective cap by switching to DYNAMIC row format and enabling the large prefix, giving 3072 bytes:

SET GLOBAL innodb_large_prefix = ON;   -- 5.6/5.7 only; default/removed on 8.0
ALTER TABLE users ROW_FORMAT = DYNAMIC;
ALTER TABLE users ADD UNIQUE (option_name);   -- 1020 bytes < 3072, now OK

Where the full string was not needed for uniqueness, a prefix index was simpler and portable across versions:

ALTER TABLE users ADD UNIQUE (option_name(191));  -- 191 * 4 = 764 < 767

191 is the well-known safe utf8mb4 prefix length under the 767-byte cap (191 × 4 = 764). On MySQL 8.0 the 3072-byte limit is the default with DYNAMIC row format, so the migration would have worked untouched.

Prevention Best Practices

• On MySQL 8.0 there is nothing to configure — innodb_default_row_format=DYNAMIC gives the 3072-byte limit out of the box; keep it there.
• On 5.7 or MariaDB, set innodb_default_row_format=DYNAMIC (and, on 5.6/5.7, innodb_large_prefix=ON with Barracuda) before large migrations.
• Only index the prefix you actually need — for long free-text columns, a (column(191)) prefix is usually as selective as the full value.
• Avoid full-length unique indexes on VARCHAR(255) utf8mb4 columns; use a shorter column, a prefix, or a hash column for exact-match uniqueness.
• Standardize charset (utf8mb4) and row format across environments so a schema that builds in dev does not fail in prod.

Quick Command Reference

-- effective byte width of the column:
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='app' AND TABLE_NAME='users';

SELECT TABLE_NAME, ROW_FORMAT FROM information_schema.TABLES
WHERE TABLE_SCHEMA='app' AND TABLE_NAME='users';

SHOW VARIABLES LIKE 'innodb_large_prefix';       -- 5.6/5.7
SHOW VARIABLES LIKE 'innodb_default_row_format'; -- 5.7+/8.0

ALTER TABLE users ROW_FORMAT = DYNAMIC;          -- raise cap to 3072
ALTER TABLE users ADD UNIQUE (option_name(191)); -- portable prefix index

Conclusion

Error 1071 is a byte-budget problem, not a character-count one. utf8mb4 quadruples the byte cost per character, so a VARCHAR(255) overshoots InnoDB’s 767-byte (legacy) or 3072-byte (Barracuda/DYNAMIC) index limit. The fix is one of three: raise the cap with ROW_FORMAT=DYNAMIC (plus innodb_large_prefix on 5.6/5.7), shorten the indexed prefix ((191) is the safe utf8mb4 value under 767 bytes), or index a narrower column.

Version matters here. MySQL 8.0 defaults to the 3072-byte limit and has removed innodb_large_prefix entirely, so most 1071 errors are seen on MySQL 5.6/5.7 and older MariaDB. MariaDB honors the same row-format and prefix rules; on current MariaDB releases DYNAMIC is likewise the default, so the prefix-index approach is the most portable fix across the whole family.