Skip to content

Incomplete dumps when max_execution_time is set and PHP < 7.4.13 #84

@mpdude

Description

@mpdude

We're using pdo_mysql and unbuffered queries to allow for streaming large MySQL result sets with a small PHP memory footprint.

max_execution_time is a MySQL setting providing a timeout for SELECT queries. When this timeout is reached, the query is aborted. (Don't confuse that with the PHP setting with the same name.)

A problem exists on PHP < 7.4.13 when this timeout is reached and the PDO query is unbuffered, since PDO will fail to detect, report or make available the fact that the result was only partially fetched.

Consider the following script:

<?php
$conn = new PDO('mysql:host=127.0.0.1', 'test');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$statement = $conn->prepare('select /*+ MAX_EXECUTION_TIME(1500) */ *, sleep(1) FROM (select 1 union select 2 union select 3 union select 4 union select 5) a JOIN (select REPEAT("x", 1024*1024)) b');
$statement->execute();

while ($row = $statement->fetch()) {
   print $row[1] . PHP_EOL;
}

print_r($row);
print_r($conn->errorInfo());
print_r($statement->errorInfo());

The /*+ MAX_EXECUTION_TIME(1500) */ query hint is used to set the max_execution_time to 1.5s for this query. Alternatively, the MySQL server wide or session setting will be used. The REPEAT(...) is necessary to make the result large enough that unbuffered query mode shows an effect.

The script will produce the following output:

$ php unbuffered-pdo.php
1
2
Array
(
    [0] => 00000
    [1] =>
    [2] =>
)
Array
(
    [0] => 00000
    [1] =>
    [2] =>
)

As you can see, the timeout expired after the second row had been fetched. $statement->fetch() returns false, and no information is available in the statement or connection ..::errorInfo() methods.

The fix in PHP is here: php/php-src@0044a81. I can confirm that as of PHP 7.4.13, an exception SQLSTATE[HY000]: General error: 3024 Query execution was interrupted, maximum statement execution time exceeded will be thrown once the timeout kicks in.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions