# ERROR 1071 (42000): Specified Key Was Too Long; Max Key Length Is 767 Bytes in MySQL

Source: https://www.bytebase.com/reference/mysql/error/1071-specified-key-was-too-long/

---

## Error Message

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

You'll also see the `3072 bytes` variant on newer servers, where InnoDB allows a larger maximum index key length (for example with `DYNAMIC`/`COMPRESSED` row formats):

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

The number is the **maximum index length in bytes** for the storage engine and row format in play. The error is raised at DDL time — `CREATE TABLE`, `ADD INDEX`, `ADD PRIMARY KEY`, or `ADD UNIQUE` — never during normal DML. Because the limit is in **bytes, not characters**, the character ceiling depends entirely on the column's charset: under `utf8mb4` (up to 4 bytes/char) a 767-byte limit is only 191 characters, and a 3072-byte limit is 768 characters.

## What Triggers This Error

1071 fires when the bytes required to index one or more columns exceed the engine's per-index maximum:

- **A `VARCHAR(255)` index under `utf8mb4`** — the classic case. `255 × 4 = 1020` bytes, over the 767-byte limit on older InnoDB row formats. This is why migrations that ran fine under `utf8` break after a charset switch to `utf8mb4`.
- **Old InnoDB row format** (`REDUNDANT` or `COMPACT`) capped at 767 bytes, often because `innodb_large_prefix` is off on MySQL 5.6 / early 5.7
- **A composite index whose columns sum past the limit** — three `VARCHAR(100)` utf8mb4 columns = 1200 bytes
- **`CHAR`/`VARCHAR` columns with a wide charset** (`utf8mb4`, `utf16`) where the byte length is 2–4× the declared length
- **A primary key on a long string column** — PKs are indexes too and hit the same ceiling
- **Framework defaults** — older Rails/Django/Laravel migrations created string columns as `VARCHAR(255)` and then indexed them, generating 1071 the moment the database charset is `utf8mb4`
- **MyISAM's 1000-byte limit** — even lower than InnoDB; rarely seen today but still possible on legacy tables

## Fix by Scenario

### A VARCHAR(255) UNIQUE/index under utf8mb4 (the common one)

```sql
CREATE TABLE users (
  email VARCHAR(255) NOT NULL,
  UNIQUE KEY uniq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ERROR 1071: Specified key was too long; max key length is 767 bytes
```

The robust fix on any modern server is to enable the larger index prefix so 3072-byte keys are allowed, then shrink the column to a realistic size. On MySQL 5.7.7+ / 8.0 the defaults already support 3072 bytes when the table uses the `DYNAMIC` row format and `innodb_default_row_format` is `DYNAMIC` (the default). Make the row format explicit if you're unsure:

```sql
CREATE TABLE users (
  email VARCHAR(255) NOT NULL,
  UNIQUE KEY uniq_email (email)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4;
```

Even with 3072 bytes available, prefer to **size the column to reality** — an email column does not need 255 utf8mb4 characters:

```sql
ALTER TABLE users MODIFY email VARCHAR(191) NOT NULL;  -- 191 × 4 = 764 bytes, fits even 767
```

`191` is the well-known safe maximum for a fully-indexed `utf8mb4` column under the 767-byte limit, which is why older frameworks adopted it as the default index length.

### Legacy server stuck at 767 bytes (5.6 / early 5.7)

If you can't change column sizes, raise the limit at the server level:

```sql
SET GLOBAL innodb_large_prefix = ON;       -- 5.6 / 5.7 only; removed in 8.0 (always on)
SET GLOBAL innodb_file_format = Barracuda;  -- required for large prefix
SET GLOBAL innodb_file_per_table = ON;
```

Then create the table with `ROW_FORMAT=DYNAMIC` or `COMPRESSED` so the 3072-byte prefix applies. Persist these in `my.cnf` so they survive a restart. On MySQL 8.0 these knobs are gone — the larger prefix is always available with `DYNAMIC`/`COMPRESSED` row formats.

### A prefix index is the right answer (long text columns)

When you genuinely need to index a column longer than the limit — a URL, a long descriptor — index only a **prefix** of it rather than the whole column:

```sql
-- Index the first 191 characters instead of the full column
ALTER TABLE pages ADD INDEX idx_url (url(191));

-- For a UNIQUE constraint on long data, a prefix unique index enforces
-- uniqueness on the prefix only — verify that's acceptable for your data:
ALTER TABLE pages ADD UNIQUE KEY uniq_url_prefix (url(191));
```

A prefix `UNIQUE` enforces uniqueness on the **prefix bytes only**, so two values that differ only after character 191 would collide. If full-value uniqueness matters, store and index a hash instead (next scenario).

### Composite index that overflows in total

```sql
ALTER TABLE memberships ADD UNIQUE KEY uniq_org_email (org_slug, email);
-- org_slug VARCHAR(100) + email VARCHAR(255), utf8mb4 → way over the limit
```

Shrink the columns to realistic lengths, use prefixes on the wide parts, or — for guaranteed uniqueness on long composite keys — index a generated hash column:

```sql
ALTER TABLE memberships
  ADD COLUMN org_email_hash BINARY(32)
    AS (UNHEX(SHA2(CONCAT(org_slug, '\\0', email), 256))) STORED,
  ADD UNIQUE KEY uniq_org_email_hash (org_email_hash);
```

A `BINARY(32)` SHA-256 is 32 bytes regardless of input length, enforces exact full-value uniqueness, and stays well under any index limit. Keep a regular (non-unique) index on the raw columns if you also query by them.

### Framework migration generates the index (Rails / Django / Laravel)

The fix is to make the framework emit a shorter indexed length, not to hand-edit the database:

- **Laravel** — set `Schema::defaultStringLength(191)` in `AppServiceProvider::boot()`; this is the documented utf8mb4 fix.
- **Rails** — specify `limit: 191` on the string column, or add the index with `length: 191`.
- **Django** — use `max_length=191` on the `CharField` you intend to index under utf8mb4.

After changing the default, regenerate/re-run the migration against a `utf8mb4` database.

## Prevention

- Size indexed string columns to reality — `VARCHAR(191)` (or smaller) for anything you index under `utf8mb4`; reserve `VARCHAR(255)` for columns you never index.
- Standardize on MySQL 8.0 (or 5.7 with `DYNAMIC` row format + large prefix) so 3072-byte keys are available, and make `ROW_FORMAT=DYNAMIC` explicit in `CREATE TABLE` for clarity.
- For long URLs/descriptors, decide up front between a prefix index (fast, prefix-only uniqueness) and a hashed column (exact uniqueness, 32 bytes).
- Set the framework's default indexed string length (Laravel `defaultStringLength`, Rails `length:`, Django `max_length`) once, so new migrations don't reintroduce 1071.
- Test schema migrations against a database created with the production charset (`utf8mb4`), not the framework's historical `utf8` default — 1071 only appears under the wider charset.

> **Note:** [Bytebase's SQL Review](https://docs.bytebase.com/sql-review/review-rules/) flags indexes on over-long `utf8mb4` string columns before the migration runs and can enforce a maximum indexed-column length as a review rule, catching 1071 during change review instead of on a failed deploy. See also [ERROR 1146: Table Doesn't Exist](/reference/mysql/error/1146-table-doesnt-exist) and [ERROR 1062: Duplicate Entry for Key](/reference/mysql/error/1062-duplicate-entry) — common siblings when creating and indexing tables.