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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: houxiaoxian1111 at 163 dot com
New email:
PHP Version: OS:

 

 [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)

Pull Requests

Pull requests:

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: Fri Dec 27 09:01:29 2024 UTC