How to Fix MySQL Error 1071: Specified Key Was Too Long

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes is raised when you attempt to create an index (including PRIMARY KEY, UNIQUE, or regular index) whose key prefix exceeds the maximum byte length allowed by the storage engine and row format in use. The error symbol is ER_TOO_LONG_KEY.

Impact

The CREATE TABLE, CREATE INDEX, ALTER TABLE, or ADD INDEX statement fails immediately and no index is created. The table itself is not modified. Because this error occurs at DDL time rather than at query runtime, it surfaces when you first deploy a schema migration — during rails db:migrate, flyway migrate, Django migrate, or a direct ALTER TABLE call. Applications that rely on the missing index (for uniqueness guarantees or performance) will either fail to start or silently miss constraints.

The byte limit varies by storage engine and InnoDB row format: InnoDB with ROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT enforces a 767-byte maximum per index prefix, while ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED (the default since MySQL 5.7.9) allows up to 3072 bytes. The larger limit requires innodb_large_prefix=ON on MySQL 5.6 (it is always on in MySQL 5.7+).

Common Causes

utf8mb4 column indexed without a prefix length. utf8mb4 uses up to 4 bytes per character. A VARCHAR(255) column indexed in full consumes up to 255 × 4 = 1020 bytes, which exceeds the 767-byte limit under COMPACT/REDUNDANT row formats and will also hit 3072 bytes for very wide columns.
utf8 (utf8mb3) columns on older MySQL versions. Even utf8 (3 bytes/char) produces 255 × 3 = 765 bytes — just under 767. Indexing a VARCHAR(256) column crosses the limit.
Composite indexes whose combined column lengths exceed the limit. Each column's byte contribution is summed. Two VARCHAR(200) utf8mb4 columns in a composite index = 200 × 4 + 200 × 4 = 1600 bytes, exceeding the 767-byte limit.
Table or column using COMPACT/REDUNDANT row format explicitly. Even on MySQL 5.7+, if the table was created with ROW_FORMAT=COMPACT the old 767-byte limit applies.
innodb_large_prefix disabled on MySQL 5.6. The system variable innodb_large_prefix defaults to OFF on MySQL 5.6, restricting all InnoDB tables to the 767-byte limit regardless of row format.
ORM-generated migrations using default column widths. Django, Hibernate, and other ORMs may generate VARCHAR(255) indexed columns, which exceed the limit when the charset is utf8mb4.

Troubleshooting and Resolution Steps

Identify the row format and charset of the affected table.

SHOW CREATE TABLE your_table\G

Look for ROW_FORMAT, CHARSET, and COLLATE in the output. Also check the default for the database:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'your_database';

Switch to DYNAMIC row format (recommended for MySQL 5.7+).

The simplest fix for most cases: change the row format so the 3072-byte limit applies.

ALTER TABLE your_table ROW_FORMAT=DYNAMIC;

Ensure the InnoDB file format supports it:

SHOW VARIABLES LIKE 'innodb_file_format';       -- MySQL 5.6: must be 'Barracuda'
SHOW VARIABLES LIKE 'innodb_large_prefix';       -- MySQL 5.6: must be ON

On MySQL 5.6, enable both in my.cnf and restart if they are not already set:

innodb_file_format     = Barracuda
innodb_large_prefix    = ON
innodb_file_per_table  = ON

Add a prefix length to the index instead of indexing the full column.

If you cannot change the row format, limit the index to the first N characters:

-- Index only the first 191 characters (191 × 4 = 764 bytes, under 767)
ALTER TABLE users ADD INDEX idx_email (email(191));

-- For a UNIQUE constraint
ALTER TABLE users ADD UNIQUE KEY uniq_token (token(191));

Note that prefix indexes do not enforce uniqueness beyond the prefix length; if full-column uniqueness is required, prefer fixing the row format instead.

Reduce the column width in the schema.

If the column does not need to hold 255 characters, shrink it:

ALTER TABLE users MODIFY COLUMN username VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL;

Switch the column charset to a narrower encoding.

For columns that will only ever contain ASCII data (emails, tokens, URLs), latin1 or ascii uses 1 byte/char:

ALTER TABLE tokens MODIFY COLUMN token VARCHAR(255)
    CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL;

Use this with caution — utf8mb4 is the correct choice for any user-visible text.

Fix ORM migrations by overriding the column width.

For Django (which generates VARCHAR(255) for CharField with unique=True or db_index=True):

# In your model
username = models.CharField(max_length=100, unique=True)  # reduce from 191/255

Or set utf8mb4 globally and ensure the database uses DYNAMIC row format in Django's DATABASES settings via OPTIONS.

Additional Information

The SQLSTATE for this error is 42000 (syntax error or access rule violation class).
On MySQL 8.0, ROW_FORMAT=DYNAMIC is the default for new InnoDB tables and innodb_large_prefix is always on, so the 767-byte limit is rarely hit unless tables were created with an explicit older row format.
MariaDB shares this error code and behaves similarly; innodb_large_prefix defaults to ON since MariaDB 10.2.
In Django, the utf8mb4 migration advisory (Django docs 2.x+) specifically recommends setting VARCHAR fields to max_length=191 when using utf8mb4 with MySQL 5.6.
MyISAM uses a different limit: 1000 bytes per key. MyISAM tables are uncommon in modern MySQL deployments.
Related error: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes is a different message surfaced in some contexts for the same underlying restriction.

Frequently Asked Questions

Why is the limit 767 bytes and where does that number come from?

The 767-byte limit comes from the InnoDB internal page format for COMPACT and REDUNDANT rows. InnoDB B-tree index pages have a fixed overhead, and 767 bytes was chosen as the safe maximum for an index entry so that at least two entries fit per 16 KB page. The DYNAMIC format lifted this to 3072 bytes (768 × 4 characters), allowing full VARCHAR(768) utf8mb4 columns.

Will adding a prefix index (e.g., email(191)) affect query performance?

For equality lookups (WHERE email = 'x@example.com'), a prefix index works well — MySQL reads index entries matching the prefix, then fetches the full row to verify. For range scans or high-cardinality columns where many rows share the same prefix, performance degrades. In most practical cases, switching to ROW_FORMAT=DYNAMIC and indexing the full column is the better long-term fix.

My migration worked in development but fails in production — why?

Development environments often run a newer MySQL version (8.0, with DYNAMIC as default) while production runs MySQL 5.6 or 5.7 with an older default row format or innodb_large_prefix=OFF. Always verify innodb_file_format, innodb_large_prefix, and ROW_FORMAT match between environments, or make your migrations explicitly set ROW_FORMAT=DYNAMIC.

Can I change innodb_large_prefix at runtime without restarting MySQL?

On MySQL 5.6 and 5.7, innodb_large_prefix is a global dynamic variable and can be changed without a restart: SET GLOBAL innodb_large_prefix = ON;. However, this only affects newly created or altered tables — existing tables retain their current row format until explicitly altered.