Bug #81567 [Ver->Nab]: Connection error status not immediately set for killed async query

Edit report at https://bugs.php.net/bug.php?id=81567&edit=1 ID: 81567 Updated by: dharman@php.net Reported by: steven at movista dot com Summary: Connection error status not immediately set for killed async query -Status: Verified +Status: Not a bug Type: Bug Package: MySQLi related Operating System: Ubuntu 20.04.3 LTS PHP Version: 7.4.25 Assigned To: dharman Block user comment: N Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2021-11-04 15:07:58] 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-04 14:51:34] 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-10-29 21:47:20] 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*(). ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=81567&edit=1