EC
1074
MySQLERRORCommonDDLHIGH confidence

Specified key was too long; max key length is 3072 bytes

Production Risk

Low — DDL fails; no data loss.

What this means

ER_TOO_LONG_KEY (1074, SQLSTATE 42000) is raised when an index definition exceeds the storage engine's maximum key length. For InnoDB with innodb_large_prefix (MySQL 5.7+), the limit is 3072 bytes; for older configurations it is 767 bytes.

Why it happens
  1. 1Index on a VARCHAR column with a length that exceeds the byte limit (e.g., VARCHAR(255) with utf8mb4 = 1020 bytes)
  2. 2Composite index whose total column bytes exceed the limit
  3. 3innodb_large_prefix is disabled on MySQL 5.6
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (name VARCHAR(800) CHARACTER SET utf8mb4, INDEX idx_name (name));

expected output

ERROR 1074 (42000): Specified key was too long; max key length is 3072 bytes

Fix

Reduce the indexed column length or use a prefix index

WHEN Index exceeds the byte limit.

Reduce the indexed column length or use a prefix index
-- Use a prefix:
CREATE TABLE t (name VARCHAR(800) CHARACTER SET utf8mb4, INDEX idx_name (name(191)));
-- Or shorten the column:
CREATE TABLE t (name VARCHAR(191) CHARACTER SET utf8mb4, INDEX idx_name (name));

Why this works

191 characters × 4 bytes/char (utf8mb4) = 764 bytes, just under the 767-byte legacy limit.

What not to do

✕ Switch the column to latin1 to bypass the limit

Changing charset to fit an index loses multibyte character support; use a prefix index instead.

Version notes
MySQL 5.7

innodb_large_prefix is enabled by default, raising the limit from 767 to 3072 bytes for DYNAMIC and COMPRESSED row formats.

Sources
Official documentation ↗

MySQL 8.0 — 1074 ER_TOO_LONG_KEY

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors