MySQL refuses to create an index: specified key was too long
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytesShort answer
Error 1071 means one index key is larger than the engine allows. InnoDB caps a key at 767 bytes for COMPACT and REDUNDANT tables and 3072 bytes for DYNAMIC and COMPRESSED ones. A utf8mb4 character takes up to 4 bytes, so a varchar(255) key needs 1020. Switch the table to DYNAMIC, or shorten the indexed columns.
Fix it fast
1Find out which cause you have
Read the number in the message (767 or 3072), then ask the server which row format new tables get.
SELECT @@version, @@innodb_default_row_format; SHOW VARIABLES LIKE 'character_set_database';2Match what you see, then jump to its fix
- 1
The message says max key length is 767 bytes.The table uses the COMPACT or REDUNDANT row formatFix - 2
The failing statement comes from php artisan migrate and indexes a string column, such as the unique index on users.email.Laravel builds a string index on a server too old for utf8mb4 keysFix - 3
The message says max key length is 3072 bytes, or the 767 fix did not help.A composite or long index is simply bigger than 3072 bytesFix
- 1
What does the error look like?
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytesERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
Cause 1 of 3
The table uses the COMPACT or REDUNDANT row format
1Check
Those two formats have the 767-byte limit. A table is created with the server default unless the statement names a format, so an old server or a changed default gives you COMPACT.
SELECT @@innodb_default_row_format;
SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'mydb/%';2Fix
Make DYNAMIC the default for new tables, and rebuild an existing table in that format. DYNAMIC raises the limit to 3072 bytes.
SET GLOBAL innodb_default_row_format = DYNAMIC;
ALTER TABLE users ROW_FORMAT = DYNAMIC;3Verify
The row format shows Dynamic and the CREATE INDEX statement runs.
SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = 'mydb/users';Confirmed by dev.mysql.com
Cause 2 of 3
Laravel builds a string index on a server too old for utf8mb4 keys
1Check
Laravel uses utf8mb4. On MySQL older than 5.7.7 or MariaDB older than 10.2.2 a varchar(255) key does not fit. Check the server version.
mysql -e "SELECT VERSION();"2Fix
Best fix: upgrade the server. If you cannot, set a shorter default string length in the boot method of AppServiceProvider (191 characters x 4 bytes = 764 bytes).
use Illuminate\Support\Facades\Schema;
public function boot(): void
{
Schema::defaultStringLength(191);
}3Verify
Drop the half-built tables and migrate again.
php artisan migrate:freshConfirmed by laravel.com
Cause 3 of 3
A composite or long index is simply bigger than 3072 bytes
1Check
Add up the bytes of every column in the index: characters times 4 for utf8mb4. The limit also applies to a whole-column key, not only to prefixes. A single 191-character prefix is the example the MySQL manual gives for 767.
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'users';2Fix
Shorten the indexed columns so the total stays under 3072 bytes (768 utf8mb4 characters across the whole key), or index fewer columns.
ALTER TABLE users MODIFY email VARCHAR(190) NOT NULL;3Verify
Create the index again.
ALTER TABLE users ADD UNIQUE INDEX users_email_unique (email);Confirmed by dev.mysql.com
How do I stop it coming back?
- Run a server new enough for DYNAMIC rows by default, and keep the dev, staging and production versions equal.
- Check the byte size of a composite index when you design it, not when the migration fails.
- Do not lower the page size (innodb_page_size) without recalculating key limits: they shrink with it.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Laravel bulk insert fails with 'too many placeholders'
SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders
Laravel sends one placeholder per value, so one insert of many rows times many columns, or a whereIn with a huge list, creates a statement MySQL refuses.
3 causes, each with a fixDatabases
Laravel query fails: conversion from one collation into another is impossible
SQLSTATE[HY000]: General error: 3988 Conversion from collation utf8_unicode_ci into utf8mb4_0900_ai_ci impossible for parameter
MySQL is comparing or binding a value whose character set cannot be converted into the one on the other side.
3 causes, each with a fixDatabases
Laravel migration fails on managed MySQL because a table has no primary key
Add a primary key to the table or unset this variable to avoid this message.
The server has sql_require_primary_key switched on, so every CREATE TABLE or ALTER TABLE must leave the table with a primary key.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fix