# ERROR 1062 (23000): Duplicate Entry for Key in MySQL

Source: https://www.bytebase.com/reference/mysql/error/1062-duplicate-entry/

---

## Error Message

```sql
ERROR 1062 (23000): Duplicate entry '42' for key 'users.PRIMARY'
```

The error message names the conflicting value and the index that rejected it. Common variants:

```
ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'users.email'
ERROR 1062 (23000): Duplicate entry '42-premium' for key 'subscriptions.user_plan_idx'
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'orders.PRIMARY'
```

The SQLSTATE is `23000` (`integrity_constraint_violation`). MySQL emits 1062 whether the conflict is on a primary key, a `UNIQUE` index, or a multi-column unique constraint.

## What Triggers This Error

1062 fires whenever MySQL refuses an INSERT or UPDATE that would create a row whose value(s) on a unique-indexed column(s) match an existing row. The most common triggers:

- **`AUTO_INCREMENT` exhaustion** — `INT` columns top out at 2,147,483,647; once you hit it, every subsequent INSERT keeps requesting the same max value and fires 1062 on every retry until you switch to `BIGINT`
- **`AUTO_INCREMENT` drift after a `mysqldump` restore** — the counter lands lower than `MAX(id) + 1`, and the next INSERT collides on its first attempt
- **`INSERT IGNORE` or `ON DUPLICATE KEY UPDATE` masking real intent** — using `IGNORE` to "fix" 1062 in dev quietly drops rows you actually wanted inserted
- **Composite unique-key collision** — application code checks one column for uniqueness but the unique index spans multiple columns, or vice-versa
- **Statement-based replication conflicts** — auto-increment values generated on the primary diverge from values generated on the replica when DDL or recovery interrupts the stream
- **Concurrent INSERTs after an existence check** — two transactions both read "no match" then both try to insert
- **Webhook or idempotency-key retries** — the same idempotency value arrives twice because the producer retried on a timeout
- **Migration adds `UNIQUE` to a column with existing duplicates** — historical rows already violate the new constraint
- **Case-insensitive collation hash collision** — `'Alice'` and `'alice'` both hash to the same index entry under `utf8mb4_0900_ai_ci`

## Fix by Scenario

### AUTO_INCREMENT exhaustion on INT

Once the column hits its max value, every INSERT fails with 1062 against the same conflicting value (the max). Check what you have:

```sql
SELECT MAX(id) AS current_max, ~0 >> 33 AS int_max FROM orders;
-- current_max = 2147483647, int_max = 2147483647
```

Migrate to `BIGINT`. Note that `ALTER TABLE ... MODIFY COLUMN id BIGINT` rewrites the entire table — InnoDB will lock the table for the duration without `ALGORITHM=INPLACE` support. Use an online schema-change tool for production tables — `gh-ost` is the conventional choice:

```bash
gh-ost \
  --user="..." --password="..." \
  --host=... --database=app --table=orders \
  --alter="MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT" \
  --execute
```

For brand-new tables, default to `BIGINT` — the 4 extra bytes per row are cheap compared to an emergency table rewrite at the worst possible moment.

### AUTO_INCREMENT drift after mysqldump restore

`mysqldump` writes `AUTO_INCREMENT=N` into the `CREATE TABLE` statement, but if you restore into an existing table — or your dump was taken without `--single-transaction` and rows kept arriving — the counter can land lower than `MAX(id) + 1`. After every restore, force the counter forward:

```sql
SET @next_id := COALESCE((SELECT MAX(id) + 1 FROM users), 1);
SET @sql := CONCAT('ALTER TABLE users AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
```

(InnoDB doesn't accept a subquery directly inside `ALTER TABLE ... AUTO_INCREMENT = ...`, so you have to build the statement dynamically.)

Bake this into your restore script — running it manually once a quarter doesn't work.

### INSERT IGNORE and ON DUPLICATE KEY UPDATE silently dropping rows

`INSERT IGNORE` converts 1062 into a warning, not an error. Sometimes that's what you want; often it isn't — you wanted those rows inserted, and silently dropping them creates ghost bugs that are very hard to diagnose later.

```sql
-- Hides 1062 errors, returns rows-affected = 0 for duplicates
INSERT IGNORE INTO users (email, name) VALUES ('alice@example.com', 'Alice');
```

Always inspect `SHOW WARNINGS` immediately after `INSERT IGNORE`:

```sql
INSERT IGNORE INTO users (email, name) VALUES ('alice@example.com', 'Alice');
SHOW WARNINGS;
-- Level   | Code | Message
-- Warning | 1062 | Duplicate entry 'alice@example.com' for key 'users.email'
```

When you actually want upsert semantics, use `ON DUPLICATE KEY UPDATE` and be explicit about what fields to update:

```sql
INSERT INTO users (email, name, updated_at)
VALUES ('alice@example.com', 'Alice', NOW()) AS new
ON DUPLICATE KEY UPDATE name = new.name, updated_at = new.updated_at;
```

Prefer the `AS new` row-alias form over `VALUES(name)` — `VALUES()` inside `ON DUPLICATE KEY UPDATE` is deprecated as of MySQL 8.0.20.

### Composite unique-key collision

The error message names the index. If you see `Duplicate entry '42-premium' for key 'subscriptions.user_plan_idx'`, the conflict is on a multi-column unique index:

```sql
SHOW INDEX FROM subscriptions WHERE Key_name = 'user_plan_idx';
-- Column_name | Sub_part | Seq_in_index
-- user_id     |  NULL    | 1
-- plan_id     |  NULL    | 2
```

If application logic only checks `user_id` for uniqueness, code and constraint disagree. Either change the application to check the same compound key, or — if the multi-column scope was unintentional — drop the composite and add a single-column unique:

```sql
ALTER TABLE subscriptions DROP INDEX user_plan_idx;
ALTER TABLE subscriptions ADD UNIQUE INDEX user_idx (user_id);
```

Confirm first that no legitimate row has the same `user_id` with different `plan_id` — if there is, dropping the composite would break valid data.

### Statement-based replication conflicts

With `binlog_format=STATEMENT`, auto-increment values generated on the source and the replica can diverge after a failover, partial outage, or recovery. Switch to row-based or mixed replication:

```sql
SET PERSIST binlog_format = 'ROW';
```

For a replication stream that's already stalled on a 1062, you may need to skip the offending event after confirming the row is safe to skip:

```sql
SET GLOBAL sql_replica_skip_counter = 1;
START REPLICA;
```

This is a manual operation — automated replication tools (Group Replication, Galera, async with ProxySQL) are usually better than hand-patching the binlog stream.

### Concurrent INSERTs after an existence check

Application-level "check then insert" without locking races under load:

```python
# WRONG — races under concurrency
existing = cursor.execute("SELECT id FROM users WHERE email = %s", (email,)).fetchone()
if not existing:
    cursor.execute("INSERT INTO users (email, name) VALUES (%s, %s)", (email, name))
    # Fails with 1062 if another transaction inserted between SELECT and INSERT
```

Use `INSERT ... ON DUPLICATE KEY UPDATE` (or `INSERT IGNORE` if you're sure you want to drop conflicts):

```sql
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE id = id;
-- rows-affected = 0 → duplicate (no-op)
-- rows-affected = 1 → inserted
```

Setting `id = id` is a no-op trick that turns the statement into "INSERT or do nothing", and the rows-affected count tells you which path ran.

### Idempotency-key collision from webhook retries

Webhook providers retry on 5xx and sometimes on timeouts even when the original request succeeded. Store the idempotency key in a unique column and turn the 1062 into a no-op:

```sql
INSERT INTO webhook_events (idempotency_key, payload, received_at)
VALUES (?, ?, NOW())
ON DUPLICATE KEY UPDATE received_at = received_at;
-- rows-affected = 0 → duplicate retry, return 200 to caller
-- rows-affected = 1 → new event, process downstream side effects
```

Do not perform downstream side effects (email, downstream API calls) inside the database transaction. Instead, confirm the insert path committed successfully and then enqueue or process the side effect once; for stronger reliability, write a transactional outbox record in the same transaction and have a worker deliver it after commit.

### Migration adds UNIQUE on a column with existing duplicates

`ALTER TABLE users ADD UNIQUE (email)` fails when duplicates exist:

```
ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'users.email'
```

Find them:

```sql
SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING n > 1
ORDER BY n DESC;
```

Resolve before the migration runs — merge rows (preserve oldest, redirect foreign keys), drop newer duplicates, or expand the constraint to include another column. For large production tables, use `pt-online-schema-change` or `gh-ost` to add the index without long-locking the table.

### Case-insensitive collation equality

With `utf8mb4_0900_ai_ci` (the MySQL 8 default), `'Alice'` and `'alice'` are treated as equal by a `UNIQUE` index — this accent-insensitive, case-insensitive collation considers them the same value:

```sql
INSERT INTO users (email) VALUES ('alice@example.com'); -- OK
INSERT INTO users (email) VALUES ('Alice@example.com'); -- ERROR 1062
```

If you want case-sensitive uniqueness, declare the column with a binary collation:

```sql
ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_bin NOT NULL;
```

For emails specifically, normalize to lowercase at the application layer — it's safer than relying on collation rules to define equality across language boundaries.

## Prevention

- Choose `BIGINT` for new auto-increment columns. The storage cost is 4 extra bytes per row; the operational cost of running out of `INT` IDs in production is measured in hours of downtime.
- After every `mysqldump` restore, reset every auto-increment counter to `MAX(column) + 1`. Bake it into your restore script.
- Don't use `INSERT IGNORE` to "make the error go away" — read `SHOW WARNINGS` after every batch insert that uses it, and treat 1062 warnings as bugs unless the duplicate-drop is explicitly desired.
- Push uniqueness into a single statement with `INSERT ... ON DUPLICATE KEY UPDATE`. Application-level "check then insert" races under load.
- When adding a `UNIQUE` constraint, run a duplicate-finder query first and resolve hits before the migration ships.
- Use row-based or mixed replication (`binlog_format=ROW`), not statement-based, to avoid auto-increment divergence after failover.

> **Note:** [Bytebase's SQL Review](https://docs.bytebase.com/sql-review/review-rules/) flags `ADD UNIQUE` migrations against tables with existing duplicates before they ship, catching the most common 1062 source during change review. See also [ERROR 1048: Column Cannot Be Null](/reference/mysql/error/1048-column-cannot-be-null) and [ERROR 1364: Field Doesn't Have a Default Value](/reference/mysql/error/1364-field-doesnt-have-a-default-value) — both are integrity-constraint siblings of 1062.