EC
1071
MySQLERRORNotableSchemaHIGH confidence

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

What this means

Error 1071 is raised when a CREATE INDEX or CREATE TABLE creates an index on a column (or combination of columns) whose maximum byte length exceeds the storage engine's index key limit. For InnoDB with the default ROW_FORMAT, the limit is 767 bytes. With innodb_large_prefix enabled (MariaDB 10.3+ default), the limit rises to 3072 bytes.

Why it happens
  1. 1Creating a UNIQUE or index on a VARCHAR(255) column with utf8mb4 encoding (255 * 4 = 1020 bytes > 767)
  2. 2Creating a composite index where the total of all column max lengths exceeds the limit
  3. 3Using utf8mb4 on a column that was previously indexed with utf8 (which uses 3 bytes per char)
How to reproduce

A UNIQUE index on a VARCHAR(255) utf8mb4 column with a small ROW_FORMAT.

trigger — this will error
trigger — this will error
-- With innodb_large_prefix OFF (older MariaDB / legacy config)
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  UNIQUE KEY idx_email (email)  -- 255 * 4 bytes = 1020 > 767
) CHARACTER SET utf8mb4;

expected output

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Fix 1

Enable innodb_large_prefix and use DYNAMIC row format

WHEN On MariaDB 10.2 and earlier where large prefix is not the default.

Enable innodb_large_prefix and use DYNAMIC row format
SET GLOBAL innodb_large_prefix = ON;
SET GLOBAL innodb_file_format = Barracuda;

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  UNIQUE KEY idx_email (email)
) CHARACTER SET utf8mb4 ROW_FORMAT=DYNAMIC;

Why this works

innodb_large_prefix increases the maximum index prefix length from 767 to 3072 bytes when combined with DYNAMIC or COMPRESSED row formats, allowing full-length utf8mb4 VARCHAR(255) columns to be indexed.

Fix 2

Use a prefix index

WHEN When changing the server config is not possible.

Use a prefix index
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  UNIQUE KEY idx_email (email(191))  -- 191 * 4 = 764 bytes < 767
) CHARACTER SET utf8mb4;

Why this works

A prefix index (email(191)) indexes only the first 191 characters of the column. This fits within the 767-byte limit (191 * 4 = 764 bytes) while still providing useful uniqueness guarantees for most email addresses.

What not to do

✕ Switch the column to utf8 (3-byte) to avoid the limit

utf8 (the MySQL/MariaDB 3-byte variant) cannot store the full Unicode character set including emoji. Use utf8mb4 for correct Unicode support.

Version notes
MariaDB 10.3+

innodb_large_prefix is ON by default and ROW_FORMAT=DYNAMIC is the default. Error 1071 should not occur with default settings for VARCHAR(255) utf8mb4.

Sources
Official documentation ↗

MariaDB Server error code 1071 / ER_TOO_LONG_KEY

MariaDB InnoDB limitations ↗

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

← All MySQL errors