Migrations

MySQL Error 1071 "Specified key was too long": Causes and Safe Fixes

Error 1071 shows up when an index needs more bytes than InnoDB allows, usually right after a switch to utf8mb4 or a restore onto an older server. Here is how the limit is calculated, which fix fits which index, and how to apply it on big tables.

· 13 min read · AG Data Team

MySQL error 1071, "Specified key was too long", means an index needs more bytes than InnoDB will store in one index key. It almost never shows up on a fresh project. It shows up when someone converts a schema from utf8mb3 to utf8mb4, restores an old dump on a new server, or replays a migration on a replica that was created years ago with different defaults.

The fix is usually simple once you know which limit you hit. The trouble is that the simple fix on a 500 GB table can lock writes for hours. Below: how the limit is calculated, the four practical fixes, and what changes when the table is large.

The two messages and what they mean

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

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

Both are listed in the MySQL server error reference: 1071 is ER_TOO_LONG_KEY and 1709 is ER_INDEX_COLUMN_TOO_LONG. The number in the message tells you which limit you hit:

How the bytes are counted

The confusing part is that limits are in bytes while lengths in your DDL are in characters. The CREATE INDEX documentation says prefix lengths for CHAR, VARCHAR and TEXT are counted in characters, while the limits are measured in bytes. MySQL reserves the worst case for every character, so for utf8mb4 each character counts as 4 bytes.

That is why 191 is such a common number in old schemas: 191 × 4 = 764 bytes, the largest utf8mb4 prefix that fits under 767. The InnoDB limits page uses exactly that example. It is also why the error appears during charset migrations: the column did not change length, but every character suddenly counts for more bytes. Keep in mind that utf8mb3 is deprecated and expected to be removed in a future major release, so this migration is one you will have to do eventually.

Composite indexes count all columns. In our experience the limit is checked against the whole index key, so two utf8mb4 columns of VARCHAR(500) in one index fail even though each one alone would fit. Add up every column in the index when you do the math.

Find tables that are still on the 767-byte limit

Since innodb_default_row_format defaults to DYNAMIC (see the row format documentation), new tables are fine. Old ones are not, because an explicit ROW_FORMAT stays with the table through upgrades. This query lists them:

SELECT table_schema, table_name, row_format, table_collation
FROM information_schema.tables
WHERE engine = 'InnoDB'
  AND row_format IN ('Compact', 'Redundant')
  AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY table_schema, table_name;

Run it on the primary and on every replica. The documentation shows exactly this trap: a table with a 3070-character prefix index is created on a source with DYNAMIC as the default, and the same statement fails on a replica that uses COMPACT. Replication then stops with an error, not with a warning.

Four ways to fix it

1. Switch the table to DYNAMIC

If the message says 767 bytes, this is usually the right fix. It removes the old limit for every index on the table:

ALTER TABLE legacy_users ROW_FORMAT=DYNAMIC, ALGORITHM=INPLACE, LOCK=NONE;

The online DDL documentation lists changing ROW_FORMAT as in place, with concurrent DML allowed, but it rebuilds the table and calls it an expensive operation. Also note a quiet side effect: a table without an explicit ROW_FORMAT takes the current innodb_default_row_format on every rebuild, including OPTIMIZE TABLE.

2. Use a shorter column or a prefix index

Ask whether the column really needs 1000 characters. Email addresses, slugs and codes often do not, and VARCHAR(255) in utf8mb4 fits easily under 3072 bytes. Shortening a column with data in it needs ALGORITHM=COPY, so check the longest stored value first.

For a non-unique lookup index, a prefix is often enough. Most values differ in the first 100 to 200 characters, and the full value is still compared when rows are read:

-- Non-unique lookup index: index only the first 191 characters
ALTER TABLE pages ADD INDEX idx_url (url(191)), ALGORITHM=INPLACE, LOCK=NONE;

Do not use a prefix for a UNIQUE index unless you mean it. The documentation is clear that values then must be unique within the prefix length, so two different URLs that share the first 191 characters will collide. TEXT and BLOB columns always need a prefix.

3. Index a hash of the value

For uniqueness on long values such as URLs or file paths, index a fixed-size hash instead. A 32-byte SHA-256 fits under any limit. InnoDB supports secondary indexes on generated columns, including virtual ones:

ALTER TABLE pages
  ADD COLUMN url_hash BINARY(32)
      GENERATED ALWAYS AS (UNHEX(SHA2(url, 256))) VIRTUAL,
  ADD UNIQUE INDEX uq_url_hash (url_hash);

-- Lookups must use the hash, then compare the full value
SELECT * FROM pages
WHERE url_hash = UNHEX(SHA2('https://example.com/a', 256))
  AND url = 'https://example.com/a';

Two things to check before you choose this. The hash is byte-exact, while a utf8mb4 column with a case-insensitive collation treats A and a as equal, so the uniqueness rule changes. Normalise the value inside the expression (for example with LOWER()) if that matters. And range queries such as LIKE 'https://example.com/%' cannot use a hash index.

4. Rethink the index

Sometimes the honest answer is that a B-tree index on a 2000-character text column is the wrong tool. Full-text search, a separate lookup table with a numeric key, or an index on a shorter derived column often serves the real query better.

What changes on large tables

Key takeaways

MySQL InnoDB Indexes utf8mb4
Work with us

Stuck on a utf8mb4 migration?

We plan and run charset and row format migrations on large MySQL tables, with online schema change tools and no surprises on replicas.

Get in touch More from the blog