1071ERRORTier 2 — Caution✅ HIGH confidence

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

Category: SchemaVersions: All MariaDB / MySQL versions

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

1Creating a UNIQUE or index on a VARCHAR(255) column with utf8mb4 encoding (255 * 4 = 1020 bytes > 767)
2Creating a composite index where the total of all column max lengths exceeds the limit
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

-- 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;

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

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

fix

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 changing the server config is not possible.

fix

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

Why it's wrong: 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 docs: https://mariadb.com/kb/en/innodb-system-variables/#innodb_large_prefix

🔧 Source ref: MariaDB Server error code 1071 / ER_TOO_LONG_KEY

📖 Further reading: MariaDB InnoDB limitations

Confidence assessment

✅ HIGH confidence

Well-documented. Version 10.3 default behaviour change confirmed in MariaDB release notes.