Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

Handling a MySQL duplicate entry error (1062) in PHP without a crash

ErrorDuplicate entry 'jane@example.com' for key 'users.email'

Short answer

MySQL error 1062 (SQLSTATE 23000) means an INSERT or UPDATE broke a unique or primary key. Since PHP 8.1 mysqli throws mysqli_sql_exception and PDO throws PDOException, so an uncaught one ends the script. Catch the exception, compare the error number with 1062, and show a friendly message or run an upsert.

  • Applies to PHP 8.3, PHP 8.4, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find which unique key was hit. The message names the value and the key, and the error number is 1062 with SQLSTATE 23000. Print both from the exception.

    <?php
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    try {
        $mysqli->query("INSERT INTO users (email) VALUES ('jane@example.com')");
    } catch (mysqli_sql_exception $e) {
        echo $e->getCode(), ' ', $e->getSqlState(), ' ', $e->getMessage();
    }
  2. 2Match what you see, then jump to its fix

    1. 1Fatal error: Uncaught mysqli_sql_exception: Duplicate entry ...mysqli throws and nothing catches itFix
    2. 2Fatal error: Uncaught PDOException on an INSERT that repeats a unique valuePDO throws a PDOException that is not caughtFix
    3. 3The same key is inserted on every import or page load, and the duplicate error is the expected caseThe row existing already is normal and should updateFix

What does the error look like?

  • Duplicate entry 'jane@example.com' for key 'users.email'
  • Fatal error: Uncaught PDOException (duplicate entry, driver code 1062)
  • Fatal error: Uncaught mysqli_sql_exception: Duplicate entry

Cause 1 of 3

mysqli throws and nothing catches it

1Check

From PHP 8.1 the default mysqli mode is MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, which throws mysqli_sql_exception instead of returning false. If your code only tests the return value of query(), the exception skips that test.

php -r 'mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); echo "strict";'

2Fix

Wrap the write in try/catch. For mysqli, getCode() holds the MySQL error number, so test it against 1062 and rethrow anything else.

<?php
try {
    $stmt = $mysqli->prepare("INSERT INTO users (email) VALUES (?)");
    $stmt->bind_param("s", $email);
    $stmt->execute();
} catch (mysqli_sql_exception $e) {
    if ($e->getCode() === 1062) {
        echo "That email is already registered.";
    } else {
        throw $e;
    }
}

3Verify

Submit the same email twice. The second attempt prints the friendly message and the script keeps running.

php register.php jane@example.com && php register.php jane@example.com

Confirmed by php.net

Did this fix it?

Cause 2 of 3

PDO throws a PDOException that is not caught

1Check

PDO uses exception mode by default from PHP 8.0. The exception carries an errorInfo array that matches PDO::errorInfo(): element 0 is the SQLSTATE and element 1 is the driver error code, which is 1062 for MySQL duplicates.

grep -n "ATTR_ERRMODE\|new PDO" db.php

2Fix

Catch PDOException and read errorInfo[1]. Send other errors up unchanged.

<?php
try {
    $stmt = $pdo->prepare("INSERT INTO users (email) VALUES (:email)");
    $stmt->execute([':email' => $email]);
} catch (PDOException $e) {
    if (($e->errorInfo[1] ?? null) === 1062) {
        echo "That email is already registered.";
    } else {
        throw $e;
    }
}

3Verify

Run the insert twice with the same value. Only the second run takes the 1062 branch.

php register.php

Confirmed by php.net

Did this fix it?

Cause 3 of 3

The row existing already is normal and should update

1Check

If a repeat insert should refresh the stored row, an error is the wrong tool. MySQL can update the existing row in the same statement when a unique index or primary key would be duplicated.

mysql -e "SHOW CREATE TABLE users\G" mydb

2Fix

Use INSERT ... ON DUPLICATE KEY UPDATE with a row alias. The older VALUES(col) form still works but the manual marks it deprecated. With several unique keys the matched row can be ambiguous, so use it on a table with one.

INSERT INTO users (email, name) VALUES ('jane@example.com', 'Jane') AS new
  ON DUPLICATE KEY UPDATE name = new.name;

3Verify

Run it twice. The second run changes the existing row and reports 2 affected rows when a value changed, or 0 when nothing changed.

mysql -e "SELECT email, name FROM users WHERE email = 'jane@example.com'" mydb

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Put the unique index in the database; do not rely on a SELECT before the INSERT, because two requests can pass that check together.
  • Catch only the error number you expect and rethrow the rest, so other failures are not swallowed.
  • Never echo the raw exception message to visitors: it contains the stored value.
  • Use an upsert for data that is meant to be re-imported.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.