php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #81567 Connection error status not immediately set for killed async query
Submitted: 2021-10-29 21:47 UTC Modified: 2021-11-06 19:49 UTC
Votes:1
Avg. Score:1.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: steven at movista dot com Assigned: dharman (profile)
Status: Not a bug Package: MySQLi related
PHP Version: 7.4.25 OS: Ubuntu 20.04.3 LTS
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: steven at movista dot com
New email:
PHP Version: OS:

 

 [2021-10-29 21:47 UTC] steven at movista dot com
Description:
------------
I'm running the Ubuntu php 7.4.25 distribution from http://ppa.launchpad.net/ondrej/php/ubuntu:  PHP 7.4.25 (cli) (built: Oct 22 2021 12:34:33) ( NTS )

I'm working with MariaDB (10.4.21-MariaDB-1:10.4.21+maria~focal-log) and Apache2 (2.4.41-4ubuntu3.8) with mod_php (7.4.25-1+ubuntu20.04.1+deb.sury.org+1).  This also occurs via CLI so I don't think Apache has anything to do with it.

I don't have any special configs for mysqlnd.

I'm running an async query that could be killed via MariaDB's max_statement_time and MYSQLI_USE_RESULT.

When a query gets killed, then I call mysqli::poll(), then reap_async_query(), the connection handle error status is not updated; errno is 0 and error is "".

For other queries with bad syntax or that reference non-existent columns/tables, the connection handle error status is updated when reap_async_query() is called, but not when the query gets killed.

The connection handle's error status is not updated until after I attempt a fetch on the mysqli_stmt object I get back from reap_async_query().  I would expect that the connection handle error status for a killed query would be set after reap_async_query(), but since I don't get it until after attempting a fetch, this complicates my error handling.

For what it's worth, if I use MYSQLI_STORE_RESULT instead of MYSQLI_USE_RESULT, the connection handle error status is set after reap_async_query(), but I prefer to use MYSQLI_USE_RESULT so I can stream a result set directly to redis, a file, or whatever else so I don't have to keep the entire result set in memory.

Test script:
---------------
<?php
$c = new mysqli(HOST,USER,PASSWORD,DATABASE);

$c->query('SET STATEMENT max_statement_time=1 FOR SELECT SLEEP(10);',MYSQLI_ASYNC|MYSQLI_USE_RESULT);

$reads = $errors = $rejects = [$c];
 
mysqli::poll($reads,$errors,$rejects,2);

$c = $reads[0];

if($c->error) die("Error after poll! {$c->error}\n");

$rs = $c->reap_async_query();

if($c->error) die("Error after reaping query! {$c->error}\n");

$rs->fetch_row();

if($c->error) die("Error after fetching result set! {$c->error}\n");


Expected result:
----------------
What I expect to happen with my test script is that when it executes, there should be a short delay and it should print "Error after reaping query! Query execution was interrupted (max_statement_time exceeded)".

I expect that the connection handle error status should be set after reap_async_query(), so I can check if the async query was successful or encountered some kind of error.

Actual result:
--------------
The script prints "Error after fetching result set! Query execution was interrupted (max_statement_time exceeded)"

This indicates that the mysqli connection handle error status is not updated until after I've called reap_async_query() and attempted any kind of fetch*().

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-11-04 14:51 UTC] nikic@php.net
-Status: Open +Status: Verified
 [2021-11-04 14:51 UTC] nikic@php.net
Can reproduce this. I had to adjust the test case to use

$c->query('SET max_execution_time=1');
$c->query('SELECT SLEEP(10) UNION ALL SELECT 1', MYSQLI_ASYNC|MYSQLI_USE_RESULT);

because my MySQL version doesn't support max_statement_time, and seems to immediately return from SLEEP without the UNION ALL.
 [2021-11-04 15:07 UTC] nikic@php.net
-Assigned To: +Assigned To: dharman
 [2021-11-04 15:07 UTC] nikic@php.net
After looking a bit closer, I don't think this is a bug. With STORE_RESULT, the response is processed at that point, so any errors will be encountered there. With USE_RESULT, it will happen when actually reading from the result sets, so you need to be prepared for errors to occur at that point.

This doesn't have anything to do with async, the behavior is the same if you do:

$c->query('SET max_execution_time=1');
$result = $c->query('SELECT SLEEP(10) UNION ALL SELECT 1', MYSQLI_USE_RESULT); 
var_dump($result->fetch_row());

It will throw on the fetch_row(), not the query().

> For other queries with bad syntax or that reference non-existent columns/tables, the connection handle error status is updated when reap_async_query() is called, but not when the query gets killed.

This is also expected. Syntax errors are detected when the query is initially submitted, while the timeout occurs only after the query has successfully started execution.

I'd like a second opinion from @dharman to confirm that the current behavior is correct.
 [2021-11-06 19:49 UTC] dharman@php.net
-Status: Verified +Status: Not a bug
 [2021-11-06 19:49 UTC] dharman@php.net
I can't reproduce the behaviour with MariaDB, but I can reproduce it with MySQL 8 and the code provided by Nikita. 

To summarize the issue: You expect reap_async_query to throw an error when the execution time of the SELECT query exceeds the limit. The actual behaviour is that the error is thrown by the fetch_row statement. 

This is not a bug, this is a feature. Time out errors in contrast to syntax errors are not triggered by the SQL being incorrect. They are triggered by MySQL when the result set preparation goes wrong. There's a small number of things that can produce errors when preparing the result set, but nonetheless, they happen and you need to be prepared that reading the result set can fail at any point. 

The difference between MYSQLI_USE_RESULT and MYSQLI_STORE_RESULT is that MYSQLI_STORE_RESULT will read all result set rows as soon as the query is executed (it internally fetches the whole result row by row). When using MYSQLI_USE_RESULT you have to ask PHP to read every single row from the MySQL server row by row yourself; the result isn't read in advance. In the example you shared, the query execution does not produce errors, but result set preparation does. This means that whenever PHP attempts to read the first row, it will get an error instead of the actual row. 

The behaviour can be better understood when you consider the timeout on the second row instead. 

$c->query('SET max_execution_time=200');
$c->query('VALUES ROW(1,-2), ROW(5,SLEEP(10)) UNION ALL SELECT 3,100', MYSQLI_ASYNC | MYSQLI_USE_RESULT);

This will produce an error only after the second fetch_row() call. Preparation of the first row will not take more than 200ms, but the second row takes over 10000ms so it will time out and throw an error. If you use STORE mode, PHP would try to read all rows at once, but fail and return an error. If you use USE mode, you can at least read the first row and get the error when trying to read the second row. 

This is a feature because it allows you to read partial result sets when the execution times out.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC