Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL refuses to create an index: specified key was too long

ErrorSQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Short 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.

  • Applies to MySQL 8.0, MySQL 8.4 LTS, MariaDB 10.11, Laravel 12
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1The message says max key length is 767 bytes.The table uses the COMPACT or REDUNDANT row formatFix
    2. 2The 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. 3The 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

What does the error look like?

  • SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
  • ERROR 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

Did this fix it?

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:fresh

Confirmed by laravel.com

Did this fix it?

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

Did this fix it?

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:

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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.