What happened
Was doing a batch INSERT — migration script, data import, manual query — and MySQL threw this:
ERROR 1062 (23000): Duplicate entry '42' for key 'PRIMARY'
The number between the quotes is whichever ID already exists in the table. MySQL refuses to insert a row whose primary key is already taken, and rolls back that row (or the whole transaction if you're in one).
Three situations cause this almost every time. First, you're inserting rows with explicit ID values that collide with existing data. Second, your AUTO_INCREMENT counter drifted out of sync — common after a database restore or manual ID edits. Third, you're running a script that inserts blindly without checking whether those rows already exist.
Debug process
1. Find out which ID is conflicting
The error message tells you directly — the number in quotes is the duplicate value. For a bulk insert where you want the full picture:
-- Check what's already in the table at that ID
SELECT id FROM your_table WHERE id = 42;
-- Check the current AUTO_INCREMENT value
SHOW TABLE STATUS LIKE 'your_table'\G
Look at Auto_increment in the output. If it's lower than your max existing ID, that's your problem right there.
2. Find the actual max ID in the table
SELECT MAX(id) FROM your_table;
Compare this against the Auto_increment value from above. Say MAX(id) is 500 but Auto_increment shows 100 — MySQL will start assigning IDs from 100 and immediately crash into existing rows.
3. Check if you're inserting explicit IDs
If your INSERT looks like this:
INSERT INTO users (id, name, email) VALUES (42, 'Alice', 'alice@example.com');
You're specifying the ID manually. Row 42 exists? It fails. This is typical in migration scripts and data imports where IDs come straight from the source database.
Solutions
Option 1: Reset AUTO_INCREMENT to the correct value
Restored a database, truncated and re-seeded, or manually changed IDs? The auto_increment counter is probably stale.
-- Set it explicitly
ALTER TABLE your_table AUTO_INCREMENT = 501;
-- Or let MySQL figure it out:
ALTER TABLE your_table AUTO_INCREMENT = 1;
-- MySQL will set it to MAX(id)+1 when the value you give is too low
After this, new inserts without explicit IDs will start from a safe value.
Option 2: Use INSERT IGNORE to skip duplicates
Running a script that may re-insert already-existing rows and you just want to skip them silently:
INSERT IGNORE INTO users (id, name, email)
VALUES (42, 'Alice', 'alice@example.com');
Duplicate rows are silently skipped. No error, no rollback. Only reach for this when skipping is the right behavior — not when you intend to update existing rows.
Option 3: Use INSERT ... ON DUPLICATE KEY UPDATE
Need to update the existing row if it's already there? That's the upsert pattern:
INSERT INTO users (id, name, email)
VALUES (42, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);
For sync jobs and idempotent scripts, make this your default. One query handles both new rows and updates — no conditional logic needed.
Option 4: Use REPLACE INTO (with caution)
REPLACE INTO deletes the conflicting row first, then inserts the new one. It works, but watch out: it resets any columns not in your INSERT, fires DELETE triggers, and can change internal row IDs in edge cases.
REPLACE INTO users (id, name, email)
VALUES (42, 'Alice', 'alice@example.com');
Only use this when you're certain about those side effects.
Option 5: Drop explicit IDs from the INSERT
Don't actually need specific ID values? Just remove id from the INSERT and let MySQL handle assignment:
-- Before (fails if id=42 exists)
INSERT INTO users (id, name, email) VALUES (42, 'Alice', 'alice@example.com');
-- After (MySQL assigns next available id)
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
Verify the fix
-- 1. Confirm AUTO_INCREMENT looks sane
SHOW TABLE STATUS LIKE 'your_table'\G
-- Auto_increment should be MAX(id) + 1 or higher
-- 2. Insert a test row without explicit ID
INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');
SELECT LAST_INSERT_ID();
-- Should return a new ID above the current max
-- 3. Re-run the original failing query
-- Should succeed now
Preventing this in future migrations
Build these habits and you'll rarely see this error again:
- After restoring a database dump, run
ALTER TABLE t AUTO_INCREMENT = 1on every auto-increment table. MySQL corrects it to max+1 automatically. - In migration scripts that insert seed data with explicit IDs, add a guard:
INSERT INTO ... SELECT ... WHERE NOT EXISTS (...). - For sync jobs pulling data from an external source, default to
ON DUPLICATE KEY UPDATE— it covers both new rows and updates in a single query. - Using an ORM? Make sure it's not re-inserting objects that were already persisted. Check whether the entity already has an assigned ID before calling save or create.
Quick reference
-- Find the conflicting row
SELECT * FROM your_table WHERE id = <duplicate_value>;
-- Check and fix AUTO_INCREMENT
SHOW TABLE STATUS LIKE 'your_table'\G
ALTER TABLE your_table AUTO_INCREMENT = 1;
-- Skip duplicates on bulk insert
INSERT IGNORE INTO ...
-- Upsert pattern
INSERT INTO ... ON DUPLICATE KEY UPDATE ...;