EC
1709
MySQLERRORNotableDDLHIGH confidence

Index column size too large; the maximum column size is 767 bytes

Production Risk

Medium — index creation fails; table data is unaffected.

What this means

An index was defined on a column whose maximum byte size exceeds the 767-byte limit for non-large-index InnoDB tables, or 3072 bytes when innodb_large_prefix is enabled.

Why it happens
  1. 1Indexing a VARCHAR(255) column with utf8mb4 encoding without a prefix length (255 × 4 = 1020 bytes > 767).
  2. 2innodb_large_prefix is disabled (default in MySQL 5.6).
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (name VARCHAR(255)) CHARACTER SET utf8mb4;
ALTER TABLE t ADD INDEX idx_name (name);

expected output

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

Fix 1

Enable innodb_large_prefix and use Barracuda format

Enable innodb_large_prefix and use Barracuda format
SET GLOBAL innodb_large_prefix = ON;
SET GLOBAL innodb_file_format = Barracuda;
-- Then recreate the index.

Why this works

innodb_large_prefix raises the limit to 3072 bytes for DYNAMIC and COMPRESSED row formats.

Fix 2

Use a prefix index

Use a prefix index
ALTER TABLE t ADD INDEX idx_name (name(191));

Why this works

A 191-character prefix on utf8mb4 uses 764 bytes, just under the 767-byte limit.

What not to do

✕

Version notes

Sources
Official documentation ↗

MySQL 8.0 — 1709 ER_INDEX_COLUMN_TOO_LONG

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

← All MySQL errors