Fix MySQL "Specified Key Was Too Long" in Django Migrations

Problem statement

A migration that creates an index on a text column fails on MySQL or MariaDB:

django.db.utils.OperationalError: (1071, 'Specified key was too long;
max key length is 767 bytes')

or, on newer versions:

(1071, 'Specified key was too long; max key length is 3072 bytes')

InnoDB limits how many bytes an index entry may use. With the utf8mb4 character set, each character can take up to 4 bytes, so a CharField(max_length=255) needs up to 1,020 bytes in an index. Older row formats allow only 767 bytes per index column. Composite indexes and unique_together across several CharFields can exceed even the newer 3,072-byte limit.

1. Check the row format: SELECT ROW_FORMAT FROM information_schema.TABLES WHERE TABLE_NAME = 'app_table';
2. On MySQL 5.7+ or MariaDB 10.2+, use DYNAMIC row format, which raises the limit to 3,072 bytes.
3. Shorten indexed fields: max_length=191 fits 767 bytes in utf8mb4 (191 × 4 = 764).
4. For composite unique indexes, reduce lengths so the total stays under 3,072 bytes.
5. Do not switch to utf8 (utf8mb3) to make it fit; it cannot store emoji and many characters.

Step-by-step solution

1. Find the limit that applies

SELECT VERSION();
SELECT @@innodb_default_row_format;
SHOW TABLE STATUS WHERE Name = 'accounts_profile';

• COMPACT or REDUNDANT row formats: 767 bytes per index column.
• DYNAMIC or COMPRESSED: 3,072 bytes per index.

MySQL 5.7.9 and later default to DYNAMIC, as does MariaDB 10.2.2 and later. Tables created on older servers and upgraded keep their old format.

2. Convert old tables to DYNAMIC

ALTER TABLE accounts_profile ROW_FORMAT=DYNAMIC;

This rebuilds the table. On large tables, use an online schema change tool (gh-ost, pt-online-schema-change) or a maintenance window. Make sure innodb_default_row_format = dynamic in the server config so new tables are created correctly.

3. Shorten indexed CharFields

For columns where 191 characters are enough, such as slugs, codes, and usernames, set max_length=191. For email addresses, 254 is the practical maximum, which fits under the 3,072-byte limit with DYNAMIC rows. Generate a migration that alters the field before adding the index.

4. Fix composite indexes

A UniqueConstraint(fields=["tenant", "name", "slug"]) with two CharField(max_length=500) columns needs 4,000 bytes plus the foreign key. Options:

• Reduce max_length on the fields involved.
• Add a separate hash column (for example SHA-256 of the long value) and make it unique instead.
• Use a functional or prefix index for lookups that do not need uniqueness.

5. Use prefix indexes when you only search

For a long TextField or CharField that you filter with startswith, MySQL supports prefix indexes. Django does not generate them directly, so add one with RunSQL:

migrations.RunSQL(
    "CREATE INDEX blog_post_title_prefix ON blog_post (title(191));",
    reverse_sql="DROP INDEX blog_post_title_prefix ON blog_post;",
)

Prefix indexes cannot enforce uniqueness of the full value.

Explanation

InnoDB stores index entries in B-tree pages, and each entry's key is bounded so that several entries fit on a 16 KB page. The 767-byte limit comes from older row formats that stored a 768-byte prefix of variable-length columns inline. DYNAMIC stores long columns off-page and allows index keys up to 3,072 bytes. Because MySQL reserves the maximum possible size for the character set, the declared max_length, not your actual data, determines whether the index can be created.

Edge cases / notes

• Third-party apps. Older package migrations with max_length=255 unique fields fail on servers stuck with COMPACT rows. Converting the server default is the cleanest fix.
• Managed MySQL. RDS, Cloud SQL, and Azure use DYNAMIC by default on current versions; tables imported from old dumps may still carry ROW_FORMAT=COMPACT in their CREATE TABLE statements.
• Collation choice. The byte limit does not depend on collation, only on character set.

Next steps

• Make sure the character set itself is right with Fix MySQL Incorrect String Value (utf8mb4).
• Review server settings with Configure Django With MySQL in Production.
• Recover the failed release with Fix Django Migration Failed During Deploy.

FAQ

Why do so many projects use max_length=191?

Because 191 characters times 4 bytes is 764 bytes, just under the old 767-byte index limit for utf8mb4.

Should I switch the table to utf8 to fix it?

No. MySQL's utf8 is a 3-byte encoding that cannot store emoji and some scripts. Use utf8mb4 with DYNAMIC rows.

Does PostgreSQL have this problem?

Not in the same way. PostgreSQL B-tree entries can be up to about 2,700 bytes of actual data, checked at insert time rather than declared length.

Will the migration be rolled back on MySQL?

No. MySQL DDL is not transactional, so a migration may be partly applied. Check the table and the django_migrations row before rerunning.