1062 php data_error ai_generated true

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '[email protected]' for key 'users_email_unique'

ID: php/pdo-sqlstate-23000-duplicate-entry-unique-constraint

Also available as: JSON · Markdown · 中文

85%Fix Rate

90%Confidence

1Evidence

2024-02-10First Seen

Version Compatibility

Version	Status	Introduced	Deprecated	Notes
MySQL 8.0.34	active	—	—	—
MariaDB 10.11.0	active	—	—	—
PHP 8.2.0	active	—	—	—

Root Cause

An INSERT or UPDATE operation attempted to create a duplicate value in a column that has a UNIQUE constraint, usually due to race conditions, missing checks, or concurrent requests.

generic

中文

INSERT 或 UPDATE 操作尝试在具有 UNIQUE 约束的列中创建重复值，通常由于竞态条件、缺少检查或并发请求导致。

Official Documentation

https://dev.mysql.com/doc/refman/8.0/en/error-mysql-server.html#error_er_dup_entry

Workarounds

95% success Use INSERT IGNORE or ON DUPLICATE KEY UPDATE to handle duplicates gracefully: $stmt = $pdo->prepare('INSERT INTO users (email, name) VALUES (:email, :name) ON DUPLICATE KEY UPDATE name = VALUES(name)'); $stmt->execute([':email' => '[email protected]', ':name' => 'John']);
```
Use INSERT IGNORE or ON DUPLICATE KEY UPDATE to handle duplicates gracefully:
$stmt = $pdo->prepare('INSERT INTO users (email, name) VALUES (:email, :name) ON DUPLICATE KEY UPDATE name = VALUES(name)');
$stmt->execute([':email' => '[email protected]', ':name' => 'John']);
```
80% success Implement a retry mechanism with a short delay if the exception occurs: try { $stmt->execute(); } catch (PDOException $e) { if ($e->errorInfo[1] == 1062) { usleep(100000); $stmt->execute(); } else { throw $e; } }
```
Implement a retry mechanism with a short delay if the exception occurs:
try { $stmt->execute(); } catch (PDOException $e) { if ($e->errorInfo[1] == 1062) { usleep(100000); $stmt->execute(); } else { throw $e; } }
```

中文步骤

使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 优雅处理重复：
$stmt = $pdo->prepare('INSERT INTO users (email, name) VALUES (:email, :name) ON DUPLICATE KEY UPDATE name = VALUES(name)');
$stmt->execute([':email' => '[email protected]', ':name' => 'John']);

实现带有短暂延迟的重试机制：
try { $stmt->execute(); } catch (PDOException $e) { if ($e->errorInfo[1] == 1062) { usleep(100000); $stmt->execute(); } else { throw $e; } }

Dead Ends

Common approaches that don't work:

Removing the UNIQUE constraint on the column to allow duplicates 95% fail
This violates data integrity and can lead to application logic bugs; not a real fix but a workaround that breaks the schema.
Wrapping every INSERT in a SELECT to check existence first without using transactions 80% fail
Race conditions still occur between SELECT and INSERT in concurrent environments; does not solve the problem reliably.