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

Error Message#

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:

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:

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:

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:

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.

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

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:

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:

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:

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:

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:

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:

# 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):

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:

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:

Find them:

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:

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:

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.