MySQL 1062 Duplicate Entry — Upsert or Ignore

ERROR 1062 (23000): Duplicate entry 'a@x.com' for key 'users.email'. It's the most honest error in MySQL — it names the value and the constraint — yet teams still lose hours to it. Sometimes it's a genuine data problem: two real users, one email. Sometimes it's infrastructure: a retried webhook, a timed-out commit the app replayed, or two requests racing through a check-then-insert gap.

The cost of mishandling it is real. Ignore every 1062 and you silently drop legitimate writes. Retry blindly and you amplify storms. Remove the unique key to 'stop the errors' and you trade loud rejections for silent duplicate rows that corrupt reports for months.

This guide shows the decision tree: reading the key name, reproducing the duplicate, choosing between INSERT, INSERT IGNORE, and ON DUPLICATE KEY UPDATE, closing check-then-insert races, and designing idempotent writes so retries become safe by construction. The error never lies about what collided — only your write path knows why it arrived twice, and that's the part this guide teaches you to read.

Read the Key Name in the Message

Duplicate entry 'a@x.com' for key 'users.email' contains the full diagnosis: the colliding value and the constraint that rejected it. Key names map straight to schema: 'PRIMARY' means the primary key (check whether it's an app-supplied reference rather than auto-increment), 'email' or 'users.email' names the unique key and, by convention, its column. Multi-column keys report the key name, not the columns — SHOW CREATE TABLE translates the name into the column list.

Confirm with two queries before acting. SHOW CREATE TABLE shows every key, its columns, and whether the supposedly-unique business rule is actually enforced. SHOW INDEX FROM adds cardinality and uniqueness flags per key. Together they answer the question the message can't: is the constraint right and the data wrong, or is the data right and the constraint wrong? A unique key on a column that legitimately repeats (a nullable 'external_id' backfilled with zeros) is a schema bug wearing an error costume.

Log the message verbatim in tickets. The value-plus-key pair lets the next engineer reproduce in one SELECT instead of re-deriving the collision from app logs — and trend analysis on key names shows whether 1062s cluster on one constraint (a path bug) or scatter (a data-quality drift).

-- Translate the key name into columns + uniqueness
SHOW CREATE TABLE users;
SHOW INDEX FROM users;

-- The row that already won (compare with the failing write)
SELECT * FROM users WHERE email = 'a@x.com';

-- Is the constraint sane? (NULLs, zeros, and placeholders)
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1 LIMIT 20;

Reproduce the Duplicate Before Choosing the Fix

Never pick IGNORE versus upsert from the error alone — reproduce the collision and classify it. The GROUP BY ... HAVING COUNT(*) > 1 query surfaces every violated value at once: one row with yesterday's timestamp is a genuine business collision; thousands of rows in the last hour is a replay storm or a race. Volume and timing pick the fix, not philosophy.

Check for placeholder collisions specifically. Backfills that wrote '' or 0 into newly-constrained columns manufacture thousands of 'duplicates' that aren't real dupes — they're missing data wearing a shared mask. The remedy there is cleaning the placeholders (NULL them, then add the key), not upserting around them. Similarly, case-only collisions ('A@x.com' vs 'a@x.com') under a case-insensitive collation need a normalization rule, not a bigger hammer.

Write the reproduction as a runnable query in the ticket, with the violating values attached. Fixes for 1062 get reverted more than most ('why did signups drop?'), and a saved repro lets the next engineer verify the fix against the exact collision instead of re-discovering it from graphs. Include first_seen and last_seen per value — a burst within one hour means replay or race, while scattered months-old dupes mean data drift with a different owner.

-- Every violated value at once (volume + timing classify it)
SELECT email, COUNT(*) AS n,
  MIN(created_at) AS first_seen, MAX(created_at) AS last_seen
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n DESC
LIMIT 20;

-- Placeholder collisions? (backfill zeros/empties sharing one mask)
SELECT email, COUNT(*) FROM users
WHERE email IN ('', 'unknown', '0')
GROUP BY email;

Three Writes: INSERT vs IGNORE vs ON DUPLICATE KEY UPDATE

Plain INSERT fails loud on conflict — the right default for paths where a duplicate is genuinely unexpected, because the 1062 becomes your alert. INSERT IGNORE downgrades conflicts (and several other errors) to warnings and skips the row: right for best-effort bulk loads where some rows may already exist, dangerous as a habit since it also swallows NOT NULL and truncation complaints you'd want to hear. Know that IGNORE skips the row entirely — no update, no counter, just a warning count.

INSERT ... ON DUPLICATE KEY UPDATE is the merge workhorse: new values insert, conflicting rows update the columns you name. Use VALUES(col) (or the 8.0.19+ alias syntax) to reference the would-be-inserted value, and keep the update list explicit — updating every column including created_at destroys audit history on every replay. For sync jobs and webhook consumers, upsert is almost always the answer: first delivery inserts, redelivery refreshes, nothing ever errors.

Avoid REPLACE unless you mean delete-then-insert. It removes the conflicting row and inserts fresh, which burns auto-increment values, fires DELETE triggers, and breaks foreign keys that the upsert would have preserved. REPLACE in a retry path is a data-loss footgun with a convenient syntax.

-- Default: fail loud (duplicates should page you here)
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ann');

-- Best-effort bulk: skip conflicts silently (check warnings after)
INSERT IGNORE INTO users (email, name) VALUES ('a@x.com', 'Ann');
SHOW WARNINGS;

-- Merge workhorse: insert fresh, refresh on conflict
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ann Lee')
ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = NOW();

-- Avoid: REPLACE deletes the old row first (triggers, FKs, auto-inc burn)

Races: Check-Then-Insert Always Loses

The pattern looks safe: SELECT to check existence, INSERT if absent. Under concurrency it's a guaranteed race — two requests both see 'absent' and both insert, and the loser's 1062 is the constraint saving you from a duplicate row. The error rate scales with traffic overlap, which is why the path 'works in staging, 1062s in prod': staging never runs the two requests in the same millisecond.

The fix is making the database the arbiter with a single atomic statement. INSERT ... ON DUPLICATE KEY UPDATE collapses check and write into one InnoDB operation: concurrent duplicates serialize on the unique index, one wins the insert, the other becomes an update. No application lock, no distributed mutex, no timing window. The unique key isn't just a guardrail here — it's the concurrency control.

For multi-step creations (user + profile + preferences), wrap the upsert plus dependent writes in one transaction, keyed on the same idempotency value. And never 'fix' races by removing the unique key: without the arbiter, both inserts succeed and you trade loud 1062s for silent duplicate rows that poison reports for quarters. When dependent rows must follow (profiles, preferences), wrap the upsert and the follow-ups in one transaction keyed on the same idempotency value — partial creations are just duplicates with extra steps.

-- Race-prone (two requests can both see 'absent'): avoid
-- SELECT id FROM users WHERE email = 'a@x.com';
-- INSERT INTO users (email) VALUES ('a@x.com');

-- Atomic: InnoDB arbitrates via the unique index
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ann')
ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = NOW();

-- Multi-step creation, one idempotency value, one transaction
START TRANSACTION;
INSERT INTO users (email) VALUES ('a@x.com')
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
INSERT INTO profiles (user_id) VALUES (LAST_INSERT_ID())
ON DUPLICATE KEY UPDATE user_id = user_id;
COMMIT;

Idempotency: Make Retries Safe by Construction

Retries are a fact of distributed life: timeouts lose acknowledgments, providers redeliver, deploys restart workers mid-batch. A write path is idempotent when replaying it N times has the effect of doing it once — and the recipe is a unique key on the natural deduplication value (provider reference, idempotency token, request id) plus upsert-or-ignore semantics on every insert. With that pair, redelivery is a no-op by construction instead of an incident.

Wire the token through the whole path: the client generates it, the API stores it in a UNIQUE column alongside the payload, and the worker upserts on it. Test it the way production will exercise it — fire the same webhook twice in CI and assert a single row with the merged state. The replay test below does exactly that against staging in ten seconds.

Route repeated key violations to a dead-letter queue with an alert, not back into the retry loop. A 1062 on a path you believe idempotent means the merge logic is wrong (updating columns it shouldn't) or two different business events share a token — both need human eyes, not a 31st retry. Log the token with every attempt so support can trace a double-charge scare to its two deliveries in seconds.

#!/bin/bash
# Fire the same webhook twice: exactly one payment row must exist
set -euo pipefail
TOKEN="idem-$(date +%s)"
curl -s -X POST https://staging.example.com/hooks/pay \
  -H "Idempotency-Key: $TOKEN" -d '{"ref":"PAY-881","amount":5000}' > /dev/null
curl -s -X POST https://staging.example.com/hooks/pay \
  -H "Idempotency-Key: $TOKEN" -d '{"ref":"PAY-881","amount":5000}' > /dev/null
mysql -u app -h db-staging -N -e \
  "SELECT COUNT(*) FROM payments WHERE provider_ref='PAY-881';"
# expect: 1 (replay merged, not duplicated)

Cleanup: Removing Dupes Before Adding the Constraint

Sometimes the 1062 arrives while creating the unique key itself: ALTER TABLE ... ADD UNIQUE fails because duplicates already exist. Don't force it — reconcile first. The self-join DELETE below keeps the earliest row per value and removes the rest; run it as SELECT first to eyeball what would die, then as DELETE inside a transaction with a snapshot taken minutes before. On large tables, batch the delete to avoid a megatransaction that stalls replication.

Decide keep-rules per case, not by default: earliest row, most recently updated, or the row with the richest payload. Log the doomed ids to an archive table before deleting — 'we removed 40k backfill zeros' needs evidence when finance asks about the row count next quarter. After cleanup, add the constraint and immediately rerun the GROUP BY/HAVING check to prove zero violations.

Backfill hygiene prevents the sequel. New UNIQUE columns should default to NULL (NULLs don't collide in MySQL unique indexes) rather than '' or 0, and backfills should write real values or NULL — never a shared placeholder that manufactures 40,000 instant duplicates. Test the new constraint in staging with production-shaped data first — a key that builds on 10k staging rows can still fail on 50M production rows with edge-case dupes.

-- Preview what would die (run first, read carefully)
SELECT email, COUNT(*) AS n FROM users
GROUP BY email HAVING COUNT(*) > 1;

-- Archive, then keep earliest row per value
CREATE TABLE users_dedupe_archive AS
SELECT u.* FROM users u
JOIN (SELECT email, MIN(id) AS keep_id FROM users GROUP BY email HAVING COUNT(*) > 1) d
USING (email) WHERE u.id <> d.keep_id;

DELETE u FROM users u
JOIN (SELECT email, MIN(id) AS keep_id FROM users GROUP BY email HAVING COUNT(*) > 1) d
USING (email) WHERE u.id <> d.keep_id;

ALTER TABLE users ADD UNIQUE KEY uniq_email (email);