php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #76742 PHP PDO mysqlnd ignores InnoDB deadlock errors (1213)
Submitted: 2018-08-14 21:29 UTC Modified: -
Votes:2
Avg. Score:4.0 ± 1.0
Reproduced:2 of 2 (100.0%)
Same Version:1 (50.0%)
Same OS:0 (0.0%)
From: jan at venekamp dot net Assigned:
Status: Open Package: PDO MySQL
PHP Version: 7.2.8 OS: Centos 7
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2018-08-14 21:29 UTC] 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)


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-12-13 16:09 UTC] dominik dot fiser at w3w dot cz
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.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Wed Jan 29 08:01:25 2020 UTC