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: 2020-10-28 10:41 UTC
Votes:5
Avg. Score:4.2 ± 1.0
Reproduced:5 of 5 (100.0%)
Same Version:3 (60.0%)
Same OS:2 (40.0%)
From: jan at venekamp dot net Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 7.2.8 OS: Centos 7
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
32 - 23 = ?
Subscribe to this entry?

 
 [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

patch.79914 (last revision 2020-08-08 00:53 UTC by houxiaoxian1111 at 163 dot com)
patch.76742 (last revision 2020-07-30 03:25 UTC by houxiaoxian1111 at 163 dot com)

Add a Patch

Pull Requests

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.
 [2020-07-30 03:25 UTC] houxiaoxian1111 at 163 dot com
The following patch has been added/updated:

Patch Name: patch.76742
Revision:   1596079503
URL:        https://bugs.php.net/patch-display.php?bug=76742&patch=patch.76742&revision=1596079503
 [2020-08-05 02:31 UTC] houxiaoxian1111 at 163 dot com
The following patch has been added/updated:

Patch Name: patch.79914
Revision:   1596594683
URL:        https://bugs.php.net/patch-display.php?bug=76742&patch=patch.79914&revision=1596594683
 [2020-08-08 00:53 UTC] houxiaoxian1111 at 163 dot com
The following patch has been added/updated:

Patch Name: patch.79914
Revision:   1596848027
URL:        https://bugs.php.net/patch-display.php?bug=76742&patch=patch.79914&revision=1596848027
 [2020-08-08 00:56 UTC] houxiaoxian1111 at 163 dot com
The following pull request has been associated:

Patch Name: with ATTR_EMULATE_PREPARES = false, don't ignore deadlock errors
On GitHub:  https://github.com/php/php-src/pull/5937
Patch:      https://github.com/php/php-src/pull/5937.patch
 [2020-10-28 10:41 UTC] nikic@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 11:01:27 2024 UTC