MySQL "Duplicate Entry for Key PRIMARY" (Error 1062)

A unique key collided. MySQL's purpose-built answers (ON DUPLICATE KEY UPDATE / INSERT IGNORE) handle concurrent writes correctly — and for AUTO_INCREMENT, the sync-the-counter fix is a one-liner.

What you'll see

Root causes

AUTO_INCREMENT counter behind max(id)

Restores or explicit-id inserts leave the table's counter below existing ids: next insert collides instantly. SELECT AUTO_INCREMENT FROM information_schema.TABLES vs MAX(id) shows the gap.

Concurrent find-or-create races on natural keys

SELECT-then-INSERT has a TOCTOU gap; two sessions insert the same email/slug and the loser gets 1062. ON DUPLICATE KEY UPDATE makes it atomic.

Fix it

  1. Resync the auto_increment counter (id collisions)
    ALTER TABLE <t> AUTO_INCREMENT = (SELECT MAX(id)+1 FROM <t>);   -- or INSERT nothing: OPTIMIZE TABLE rebuilds it
  2. Make the write atomic (natural keys)
    INSERT INTO t (email, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name);
  3. Skip-only semantics for idempotent imports
    INSERT IGNORE INTO t ...   -- 1062 rows become warnings, import continues (check SHOW WARNINGS)
  4. Audit existing duplicates against that index before enforcing it
    SELECT col, COUNT(*) c FROM t GROUP BY col HAVING c > 1;

Field note

INSERT IGNORE converts OTHER errors (bad values, truncation) to warnings too — narrower fix: ON DUPLICATE KEY UPDATE id=id (a no-op update). Error 1022/1062 family also covers unique secondary indexes: the key name in the error tells you which index collided.

Common questions

The table is empty but still throws duplicate entry '2147483647'?

That's an AUTO_INCREMENT overflow signature (hit the int ceiling once) — the counter pinned at max. Change the column to BIGINT UNSIGNED and reseed. Empty now, poisoned forever until fixed.

ON DUPLICATE KEY UPDATE vs INSERT IGNORE?

INSERT IGNORE skips the row entirely (and swallows other errors); ON DUPLICATE KEY UPDATE performs an update (or a no-op) and leaves other errors intact. Prefer the no-op update for dedupe-imports.

Related fixes

Ship it right the first time

Our most-documented failures, packaged as ready-to-ship starter kits: Docker, Kubernetes, and Terraform.

Browse the template store →

One-time. Yours to modify. Instant download from the NinjaOps template store.