Victor's room

Quick Summary: The error ERROR 1062 (23000): Duplicate entry '<value>' for key 'PRIMARY' (or for key 'table.PRIMARY') occurs in MySQL and MariaDB when an INSERT or UPDATE statement attempts to write a record with an identifier that already exists in the table’s primary key index. This is commonly triggered by desynchronized AUTO_INCREMENT counters, concurrent transaction race conditions, flawed data migration scripts, or importing un-sanitized SQL dump files.

Understanding the Root Causes

A table’s primary key enforces entity integrity and uniqueness across the relational database. When a transaction violates this fundamental invariant, MySQL aborts the operation to prevent index corruption. The primary triggers include:

• Desynchronized AUTO_INCREMENT Pointer: Manual inserts with explicit primary key IDs or partial table restorations can leave the internal AUTO_INCREMENT sequence set to a value lower than the actual MAX(id) in the table.
• Race Conditions under High Concurrency: Multiple application threads or microservices attempting to generate and insert the same application-level business key simultaneously.
• Flawed SQL Dumps & Data Seeds: Importing database backups or seed files containing conflicting static IDs without preceding TRUNCATE or DROP TABLE directives.
• Replication Conflicts: Bidirectional or multi-source master-master replication setups writing intersecting auto-increment ranges without configured offsets (e.g., auto_increment_increment and auto_increment_offset).

Step 1: Quick Fix – Identify Conflict and Resynchronize AUTO_INCREMENT

If your table uses an auto-incrementing integer primary key, the most frequent cause is a stale sequence counter. Compare the counter against the maximum existing ID and reset it:

-- 1. Identify the current maximum primary key in your table
SELECT MAX(id) FROM users;

-- 2. Inspect the current AUTO_INCREMENT counter
SELECT AUTO_INCREMENT 
FROM information_schema.tables 
WHERE table_schema = DATABASE() AND table_name = 'users';

-- 3. If AUTO_INCREMENT is <= MAX(id), reset it to the next available integer
ALTER TABLE users AUTO_INCREMENT = 1051; -- Set to MAX(id) + 1

Step 2: Handle Concurrency with Idempotent SQL Patterns

For applications where duplicate attempts occur naturally due to web retries, API webhooks, or batch processing, write idempotent queries that gracefully handle existing keys:

-- Option A: Update existing fields on collision (UPSERT pattern)
INSERT INTO users (id, username, email, updated_at) 
VALUES (42, 'johndoe', 'john@example.com', NOW())
ON DUPLICATE KEY UPDATE 
    username = VALUES(username),
    email = VALUES(email),
    updated_at = NOW();

-- Option B: Skip insertion silently if the primary key exists
INSERT IGNORE INTO users (id, username, email) 
VALUES (42, 'johndoe', 'john@example.com');

-- Option C: Replace row completely (deletes old row and inserts new)
REPLACE INTO users (id, username, email) 
VALUES (42, 'johndoe', 'john@example.com');

Step 3: Safely Import Large Dumps and Migrate Data

When restoring database backups or migrating between environments, ensure the dump uses safe collision guards:

# Generate dumps with DROP TABLE to eliminate preexisting collisions
mysqldump -u root -p --add-drop-table --single-transaction mydb > backup.sql

# Alternatively, import large datasets while disabling unique checks temporarily
mysql -u root -p mydb <<EOF
SET foreign_key_checks = 0;
SET unique_checks = 0;
SOURCE /path/to/backup.sql;
SET unique_checks = 1;
SET foreign_key_checks = 1;
EOF

Verification & Testing Steps

Test that the table accepts new records without specifying explicit primary keys and that uniqueness constraints hold:

-- Verify clean auto-increment insertion
INSERT INTO users (username, email) VALUES ('testuser', 'test@example.com');

-- Inspect the generated ID
SELECT LAST_INSERT_ID();

-- Confirm the auto_increment pointer has advanced
SHOW TABLE STATUS LIKE 'users'\G

Troubleshooting Summary Comparison

Scenario	Root Cause	Recommended Fix
Standard Insert Fails	Stale AUTO_INCREMENT ≤ MAX(id)	ALTER TABLE ... AUTO_INCREMENT = MAX+1
Webhooks / Sync Jobs	Re-delivered events with existing IDs	Use INSERT ... ON DUPLICATE KEY UPDATE
Bulk Seed / Batch Script	Overlapping dataset records	Use INSERT IGNORE or pre-truncate table
Multi-Master Replication	Primary keys colliding across nodes	Configure auto_increment_increment / offset