Specified key was too long; max key length is 767 bytes — how to fix

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes

Quick fix: On MySQL 5.7.7+/8.0 and MariaDB 10.2.2+ use InnoDB with ROW_FORMAT=DYNAMIC (the default), which allows 3072-byte keys. On older servers either index a prefix (INDEX (email(191))), shorten the column to VARCHAR(191), or in Laravel set Schema::defaultStringLength(191);. A 1000-byte limit means the table is MyISAM: convert with ENGINE=InnoDB.

What it means

Index keys have a maximum size in bytes, not characters:

MySQL reserves the worst case per character: 4 bytes for utf8mb4, 3 for utf8mb3. So:

• VARCHAR(255) utf8mb4 = 1020 bytes → too long for 767 and 1000
• VARCHAR(191) utf8mb4 = 764 bytes → fits 767 (that's why "191" appears everywhere)
• VARCHAR(768) utf8mb4 = 3072 bytes → max for DYNAMIC

Composite indexes add up all their columns.

Check your server and table:

SELECT VERSION();
SHOW VARIABLES LIKE 'innodb_default_row_format';
SHOW TABLE STATUS WHERE Name = 'users';   -- Engine and Row_format columns

Fix 1: use InnoDB with DYNAMIC row format (modern servers)

On MySQL 5.7+/8.0 or MariaDB 10.2+ the default is already DYNAMIC; you only get the 767 error if the table was created with COMPACT or the server is configured that way:

ALTER TABLE users ROW_FORMAT=DYNAMIC;

On MySQL 5.6 / MariaDB 10.1 you also need these in my.cnf (they were removed later):

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = 1

Fix 2: the 1000-byte message means MyISAM

ALTER TABLE users ENGINE=InnoDB;

Set the default so new tables are InnoDB: default-storage-engine=InnoDB.

Fix 3: shorten the column or index a prefix

If you can't change the server, limit the indexed length.

-- shorter column
ALTER TABLE users MODIFY email VARCHAR(191) NOT NULL;

-- or keep the column, index only the first 191 characters
CREATE INDEX idx_users_email ON users (email(191));

A prefix index can't enforce uniqueness on the full value: a UNIQUE (email(191)) treats two values with the same first 191 characters as duplicates. For real emails that's fine; for long URLs or paths consider a hash column instead:

ALTER TABLE pages ADD url_hash BINARY(32) AS (UNHEX(SHA2(url, 256))) STORED,
                  ADD UNIQUE KEY uq_url_hash (url_hash);

Fix 4: Laravel

The classic Laravel error on old MySQL/MariaDB. In app/Providers/AppServiceProvider.php:

use Illuminate\Support\Facades\Schema;

public function boot(): void
{
    Schema::defaultStringLength(191);
}

Better: upgrade the database server, after which the default 255 works.

Fix 5: Django, Rails and others

• Django: CharField(max_length=255, unique=True) on old MySQL; reduce max_length to 191 or upgrade. Ensure OPTIONS: {'charset': 'utf8mb4'} matches your intent.
• Rails: set encoding: utf8mb4 and ensure ROW_FORMAT=DYNAMIC for the table, or limit: 191 on indexed strings.

Don't "fix" it by switching to utf8 (utf8mb3): it can't store emoji and leads to error 1366.

Last updated 2026-11-07.