php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79914 PHP PDO mysqlnd ignores InnoDB deadlock errors
Submitted: 2020-07-30 06:58 UTC Modified: 2020-10-28 10:40 UTC
From: houxiaoxian1111 at 163 dot com Assigned: sjon (profile)
Status: Closed Package: PDO MySQL
PHP Version: 7.2.32 OS: centos 7.8.2003
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:
22 - 15 = ?
Subscribe to this entry?

 
 [2020-07-30 06:58 UTC] houxiaoxian1111 at 163 dot com
Description:
------------
PHP PDO mysqlnd ignores InnoDB deadlock errors (1213) when ATTR_EMULATE_PREPARES = false

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 03:32 UTC by houxiaoxian1111 at 163 dot com)
patch.76742 (last revision 2020-07-30 06:59 UTC by houxiaoxian1111 at 163 dot com)

Add a Patch

Pull Requests

Pull requests:

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-07-30 06:59 UTC] houxiaoxian1111 at 163 dot com
The following patch has been added/updated:

Patch Name: patch.76742
Revision:   1596092366
URL:        https://bugs.php.net/patch-display.php?bug=79914&patch=patch.76742&revision=1596092366
 [2020-07-30 07:19 UTC] sjon@php.net
could this be related to #77163 ?
 [2020-08-05 02:20 UTC] houxiaoxian1111 at 163 dot com
The following patch has been added/updated:

Patch Name: patch.79914
Revision:   1596594009
URL:        https://bugs.php.net/patch-display.php?bug=79914&patch=patch.79914&revision=1596594009
 [2020-08-05 05:12 UTC] sjon@php.net
-Status: Open +Status: Assigned -Assigned To: +Assigned To: sjon
 [2020-08-05 05:12 UTC] sjon@php.net
thanks for the updated patch, I can confirm this also fixes bug#77163

I have created a pull-request here https://github.com/php/php-src/pull/5937
 [2020-08-08 03:32 UTC] houxiaoxian1111 at 163 dot com
The following patch has been added/updated:

Patch Name: patch.79914
Revision:   1596857567
URL:        https://bugs.php.net/patch-display.php?bug=79914&patch=patch.79914&revision=1596857567
 [2020-08-08 06:26 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:40 UTC] nikic@php.net
-Status: Assigned +Status: Closed
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Apr 25 08:01:28 2024 UTC