SwapSQL

Migration errors

Fix: ERROR 1071 — Specified key was too long; max key length is 767 bytes

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

When it happens

Creating or indexing a utf8mb4 VARCHAR column on MySQL 5.6 / early 5.7 — classically an indexed VARCHAR(255), the default in Laravel and WordPress.

Why it happens

An InnoDB index prefix is capped at 767 bytes when the table uses the older COMPACT/REDUNDANT row format (or innodb_large_prefix is off). With utf8mb4, MySQL reserves 4 bytes per character, so VARCHAR(255) needs 255×4 = 1020 bytes and overflows. The cap rises to 3072 bytes with the DYNAMIC row format, which is the default on MySQL 5.7.7+ and 8.0.

How to fix it

1. Shorten the indexed column to fit (the 191 trick)

ALTER TABLE t MODIFY email VARCHAR(191);

191×4 = 764 bytes, just under 767. In Laravel set Schema::defaultStringLength(191) in AppServiceProvider::boot().

2. Or index only a prefix of the column

CREATE INDEX idx_email ON t (email(191));

3. Or switch the table to the DYNAMIC row format (raises the cap to 3072)

ALTER TABLE t ROW_FORMAT=DYNAMIC;
-- On MySQL 5.6/5.7 also: SET GLOBAL innodb_large_prefix=ON;
-- and innodb_file_format=Barracuda

DYNAMIC is already the default on MySQL 5.7.7+ and 8.0, so upgrading the server removes the limit too.

4. Converting to PostgreSQL?

PostgreSQL B-tree indexes have no per-character byte cap like this, so indexed text columns just work — our converter maps your utf8mb4 VARCHARs to UTF-8 columns without prefix tricks.

Migrating between MySQL and PostgreSQL?

Our converter handles this and dozens of other gotchas automatically — upload a dump, download working SQL.

Try the converter free

More: all migration guides & error fixes