Incomplete dumps when `max_execution_time` is set and PHP < 7.4.13

[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: https://github.com/php/php-src/commit/0044a81fbbe0807158a626c4c5a4d8f4d379247f. 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.

[mpdude]

We could...

• require PHP ^7.4.13
• use the query hint to set max_execution_time to a very high value (#86)
• print a warning when the number of rows is not what we might expect (#85)

[mpdude]

I hope that #85 and #86 are sufficient to address this.