Bug #76742 [Com]: PHP PDO mysqlnd ignores InnoDB deadlock errors (1213)

Edit report at https://bugs.php.net/bug.php?id=76742&edit=1 ID: 76742 Comment by: dominik dot fiser at w3w dot cz Reported by: jan at venekamp dot net Summary: PHP PDO mysqlnd ignores InnoDB deadlock errors (1213) Status: Open Type: Bug Package: PDO MySQL Operating System: Centos 7 PHP Version: 7.2.8 Block user comment: N Private report: N New Comment: Probably similar to https://bugs.php.net/bug.php?id=76525 The core of the problem is that Galera Cluster can return deadlock error on commit and MySQL PDO ignores this error and returns always only false instead of PDO error/exception. Previous Comments: ------------------------------------------------------------------------ [2018-08-14 21:29:34] jan at venekamp dot net Description: ------------ PHP PDO mysqlnd ignores InnoDB deadlock errors (1213) when ATTR_EMULATE_PREPARES = false When a deadlock is detected the current transaction is discarded without any indication that something went wrong. Making it possible to have hard-to-find bugs where one sometimes seems to "mysteriously" lose data when inserting into or updating the database. Tested on: Centos 7, 5.5.56-MariaDB MariaDB Server PHP 7.3.0alpha4 mysqlnd 5.0.12-dev (remi-safe) PHP 7.2.8 mysqlnd 5.0.12-dev (remi-safe) PHP 7.1.8 mysqlnd 5.0.12-dev (centos-sclo-rh) PHP 5.4.16 mysqlnd 5.0.10 (centos-sclo-rh) Test script: --------------- CREATE DATABASE test CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'; USE test; CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, data MEDIUMTEXT NULL ) ENGINE = InnoDB; <?php $password = 'password'; $dbh = new \PDO("mysql:host=localhost;dbname=test", 'root', $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_EMULATE_PREPARES => false]); $dbh->query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'); $dbh->beginTransaction(); $dbh->prepare("INSERT INTO test (data) VALUES (:data);") ->execute(['data' => 'a']); sleep(5); $sth = $dbh->prepare("SELECT id, data FROM test WHERE 1"); var_dump($sth->execute(), $sth->fetchAll(), $dbh->commit()); Expected result: ---------------- Shell 1: [jan@localhost ~]$ php test.php bool(true) array(1) { [0] => array(4) { 'id' => int(1) [0] => int(1) 'data' => string(1) "a" [1] => string(1) "a" } } bool(true) Shell 2: (started 2 seconds after 1st) [jan@localhost ~]$ php test.php PHP Fatal error: Uncaught PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction in /home/jan/test.php:16 Stack trace: #0 /home/jan/test.php(16): PDOStatement->execute() #1 {main} thrown in /home/jan/test.php on line 16 Actual result: -------------- Shell 1: [jan@localhost ~]$ php test.php bool(true) array(1) { [0] => array(4) { 'id' => int(1) [0] => int(1) 'data' => string(1) "a" [1] => string(1) "a" } } bool(true) Shell 2: (started 2 seconds after 1st) [jan@localhost ~]$ php test.php bool(true) array(0) { } bool(true) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=76742&edit=1