php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #76048 PDO Mysql max_execution_time is not respected
Submitted: 2018-03-05 07:37 UTC Modified: 2020-12-10 11:01 UTC
Votes:19
Avg. Score:4.7 ± 0.7
Reproduced:17 of 17 (100.0%)
Same Version:7 (41.2%)
Same OS:8 (47.1%)
From: rishabh dot pugalia at gmail dot com Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 7.0.28 OS: Mac
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: rishabh dot pugalia at gmail dot com
New email:
PHP Version: OS:

 

 [2018-03-05 07:37 UTC] rishabh dot pugalia at gmail dot com
Description:
------------
PDO does not respect MySQL 5.7's new feature - max_execution_time()

The code below will explain further. Sadly the mysqli_* seg faults if the same native prepared statement is executed.

Test script:
---------------
<?php

$connection = new PDO("mysql:host=127.0.0.1;dbname=experiments", 'root', '');

// Setting this to true triggers a query directly but still the result is same (which is also weird)
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

// The query is supposed to take about 6s to execute
$stmt = $connection->prepare("select /*+ max_execution_time(1000) */ * from users where name = ?");

$stmt->execute(['b08588d6-0f12-11e8-9637-7e0088aab9a1']);

var_dump($stmt->fetchAll());

var_dump($connection->errorInfo());

Expected result:
----------------
It should throw an error around what Mysql natively does:

"ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded"

Actual result:
--------------
array(0) {
}
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-03-05 08:18 UTC] saurav1991 at gmail dot com
When client side prepared statement is used i.e PDO::ATTR_EMULATE_PREPARES => true, the expected error PDOException: SQLSTATE[HY000]: General error: 3024 Query execution was interrupted, maximum statement execution time exceeded is thrown

However on using server side prepared statements, this exception is not thrown.

Compared with a ruby script using the mysql2 client. The exception is thrown both when server side prepared statement is used and when prepared query is sent to mysql directly.

This might have to do with how the prepared statement APIs of libmysqlclient are being used internally.
 [2018-03-05 08:32 UTC] rishabh dot pugalia at gmail dot com
-Summary: max_execution_time is not respected +Summary: PDO Mysql max_execution_time is not respected
 [2018-03-05 08:32 UTC] rishabh dot pugalia at gmail dot com
Adding "PDO Mysql" to the title.
 [2018-06-20 15:56 UTC] anthony at catsone dot com
I'm also running into this issue. 100% reproducible as per the original reporter.
 [2019-07-01 06:50 UTC] Fractalizer at yandex dot ru
Is reproducable on PHP 7.2 also. Any chances to have this fixed?
 [2020-02-25 12:00 UTC] thijs at keeping dot nl
Reproduced using PHP 7.4 on both Ubuntu 18.04.4 and macOS 10.15.3.
 [2020-12-10 11:01 UTC] nikic@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 [2020-12-10 11:01 UTC] nikic@php.net
Can't reproduce this on current 7.4/8.0 any more, can reproduce on 7.3. I believe the fix for InnoDB lock errors also covered this case. (Please tell me if you still see this issue on current 7.4/8.0.)
 [2021-09-03 12:23 UTC] thanhtung4948 at gmail dot com
I still face this problem using PHP 7.4
Can you confirm?
 [2023-11-11 11:50 UTC] piotrekkr at o2 dot pl
I can reproduce with 8.2 and MySQL 8.0:

##########
PHP 8.2.12 (cli) (built: Oct 28 2023 01:45:57) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.2.12, Copyright (c) Zend Technologies
    with Zend OPcache v8.2.12, Copyright (c), by Zend Technologies
    with Xdebug v3.2.0, Copyright (c) 2002-2022, by Derick Rethans
##########

test script:
------------------------
<?php
try {
    $dbh = new PDO(
        'mysql:host=db;dbname=dbname',
        'root',
        'root',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );

    $dbh->exec('SET SESSION MAX_EXECUTION_TIME=1000');
    var_dump((new DateTime())->format('Y-m-d H:i:s.u'));
    $stmt = $dbh->query('SELECT SLEEP(10), version()');
    var_dump((new DateTime())->format('Y-m-d H:i:s.u'));
    var_dump($stmt->fetchAll());
    var_dump($stmt->errorInfo());
    var_dump($dbh->errorInfo());
} catch (\Throwable $e) {
    exit(var_dump($e->getMessage()));
}
------------------------

output is

------------------------
string(26) "2023-11-11 11:47:22.527547"
string(26) "2023-11-11 11:47:23.528134"
array(1) {
  [0]=>
  array(2) {
    ["SLEEP(10)"]=>
    int(1)
    ["version()"]=>
    string(6) "8.0.28"
  }
}
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}
array(3) {
  [0]=>
  string(5) "00000"
  [1]=>
  NULL
  [2]=>
  NULL
}
------------------------
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 09:01:30 2024 UTC