PHP PDO does not throw exception on duplicate key insert

Question

I have a strange problem with PDO not throwing an exception when a duplicate value is inserted. In this case I did expect an error.

The relevant code:

try
{
  $db_conn = new PDO("mysql:host=".$config["database"]["hostname"].";charset=utf8", $config["database"]["username"], $config["database"]["password"], []);
  $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $db_conn->exec(file_get_contents("some_file_with_data.sql");
}
catch(Exception $e)
{
  // PDOException extends RuntimeException extends Exception so exceptions should be catched here
  // however for the duplicate key entry it will not throw an exception
}

The file with SQL data contains multiple inserts like this:

INSERT INTO `a` (`b`, `c`) VALUES
  (1, 1),
  (2, 2),
  (3, 2);

INSERT INTO `a` (`b`, `c`) VALUES
  (1, 1);

The field b in table a is set to being the primary key. When I insert the exact same data in the exact same structure using phpMyAdmin I get this error: #1062 - Duplicate entry '65533' for key 'PRIMARY'

Why does PDO not throw an error in this case? Even when I set the error mode to exception?

Edit: This is the table structure used for this specific table

CREATE TABLE IF NOT EXISTS `a` (
  `b` smallint(5) unsigned NOT NULL,
  `c` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I already have it in a try/catch block, let me update my code to clarify this. — Matthijs
– Matthijs, Commented Sep 16, 2015 at 9:54

dognose · Accepted Answer · 2020-08-10 23:29:43Z

9

Update 2018: DEVs do not consider this a bug, but intended behaviour. So, PHP-Users have to live with that, Report is closed for any future questions...

This has often been reported as bug with PDO: https://bugs.php.net/bug.php?id=61613

It will only throw an exception if the FIRST Statement is invalid. If the first statement runs smooth, you won't get any error - And your first statement is valid:

INSERT INTO `a` (`b`, `c`) VALUES
  (1, 1),
  (2, 2),
  (3, 2);

as a workaround - or according to user deleted the right way of doing it - you need to process the rowsets one-by-one (taken from the bug reports comments):

$pdo->beginTransaction();
try {
    $statement = $pdo->prepare($sql);
    $statement->execute();
    while ($statement->nextRowset()) {/* https://bugs.php.net/bug.php?id=61613 */};
    $pdo->commit();
} catch (\PDOException $e) {
    $pdo->rollBack();
    throw $e;
}

edited Aug 10, 2020 at 23:29

answered Sep 16, 2015 at 10:12

dognose

21k9 gold badges67 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthijs Over a year ago

Thank you very much! I wasn't completely sure my code was 100% correct but I did never find this bug report (who searches for "No PDO error if first SQL statement of a group is valid" when you do not get an exception on a duplicate entry :P )

Collectives™ on Stack Overflow

PHP PDO does not throw exception on duplicate key insert

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related