php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #81286 PDO return phantom rowset and no error when MYSQL_ATTR_USE_BUFFERED_QUERY=FALSE
Submitted: 2021-07-24 12:30 UTC Modified: 2021-07-24 20:13 UTC
Votes:4
Avg. Score:4.8 ± 0.4
Reproduced:4 of 4 (100.0%)
Same Version:4 (100.0%)
Same OS:4 (100.0%)
From: tropicano at ukr dot net Assigned:
Status: Verified Package: PDO MySQL
PHP Version: 7.4.21 OS: All
Private report: No CVE-ID: None
 [2021-07-24 12:30 UTC] tropicano at ukr dot net
Description:
------------
Unexpected phantom rowset and no error when PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = FALSE.
When PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = TRUE then result expected.

Test script:
---------------
$db = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => FALSE]);

$qr = $db->query('SELECT IF(1, (SELECT 1 UNION ALL SELECT 2), 0)');

print_r($db->errorInfo()); // here is no error

if ($qr) {
  print_r($qr->errorInfo()); // here is no error too
  $qr->nextRowset(); // return false;
  print_r($qr->errorInfo()); // here is no error again
}

Expected result:
----------------
Array
(
    [0] => 21000
    [1] => 1242
    [2] => Subquery returns more than 1 row
)

Actual result:
--------------
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)

Patches

Pull Requests

Pull requests:

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-07-24 20:13 UTC] dharman@php.net
Thanks for reporting this issue. This has already been fixed in PHP 8.0.1 but the fix was not backported to PHP 7. It might be possible to backport it...

FYI, your expected result doesn't match what really should happen. The proper output with SILENT mode enabled should be:

Array
(
    [0] => 00000
    [1] =>
    [2] =>
)
Array
(
    [0] => 00000
    [1] =>
    [2] =>
)
Array
(
    [0] => 21000
    [1] => 1242
    [2] => Subquery returns more than 1 row
)
 [2021-07-24 20:13 UTC] dharman@php.net
-Status: Open +Status: Verified
 [2021-07-24 20:31 UTC] tropicano at ukr dot net
I think, if first SQL statement do error then PDO::query() may return FALSE, but you said it will return PDOStatement with phantom result?
 [2021-07-24 20:45 UTC] dharman@php.net
PDO::query() does not fetch a result in unbuffered mode. Therefore, you should not get an error when you call PDO::query(). You would get an error if you tried to fetch the results or close the statement. The problem is that it should close the previous result when fetching the next result in the same statement, which PDO_MySQL doesn't do, hence this bug report. So PDO::query() should not give you an error, but PDOStatement::nextRowset() should.
 [2021-07-24 21:04 UTC] tropicano at ukr dot net
Ok, then another query like this:

$qr = $db->query('SELECT SELECT');

why returns FALSE and error info ?

What different? Subject query and this second query both with errors.
But subject query return PDOStatement and second query return FALSE.

I think in this case both PDO::query() must return same result - FALSE or PDOStatement with errors.
 [2021-07-24 22:22 UTC] dharman@php.net
And that is an excellent question, one which I cannot answer. 

In unbuffered mode PDO::query does two things: prepare a statement and execute a statement. In emulated mode, preparing is done client-side and the baked query is sent to the MySQL server. MySQL server does its own things to prepare the statement, execute the query, fetch the data, etc. MySQL server can throw an error if any of its actions fail. For the query `SELECT SELECT` the prepare fails as the syntax is invalid. It can't even execute the SQL, it throws the error at the very first step. For the SQL, which you used in the bug report, MySQL does not report any errors in prepare, nor in execute. PHP can't report an error if there is no error thrown by MySQL. However, MySQL will throw an error if you try to fetch the results from that query. PHP will try to fetch the result internally whenever you use PDOStatement::fetch or PDOStatement::closeCursor. What is not working properly is that when you try to call PDOStatement::nextRowset, PHP doesn't check if MySQL is waiting for it to fetch a result. What needs to be added is that when you call that method, PDO should close the previous result, i.e. ask the MySQL server for it and immediately discard whatever it receives. 

I am not sure how you discovered this edge case, but it does look like a real bug, despite your wrong expectations. And hey, I can't blame you if you are confused, even I have absolutely no idea why MySQL reports the error so late.
 [2021-07-25 00:08 UTC] tropicano at ukr dot net
Thanks for the detailed answers - you explain very clearly.

In this case, with multi-queries ([PDO::MYSQL_ATTR_MULTI_STATEMENTS => TRUE]) will be impossible to determine exactly which query the given error:

$sql1 = 'SELECT 1';

$sql2 = 'SELECT IF(1, (SELECT 1 UNION ALL SELECT 2), 0)';

$sql3 = 'SELECT 3';

PDO::query( $sql1.';'.$sql2.';'.$sql3 );

Normally it may return one rowset and non-zero errorInfo() after nextRowset().

But as unexpected result it return two rowsets which implies an error in $sql3, although it is not, an error was in $sql2
 [2021-07-26 15:46 UTC] dharman@php.net
The following pull request has been associated:

Patch Name: Fix #81286: PDO return phantom rowset and no error when MYSQL_ATTR_USE_BUFFERED_QUERY=FALSE
On GitHub:  https://github.com/php/php-src/pull/7303
Patch:      https://github.com/php/php-src/pull/7303.patch
 [2022-11-25 05:52 UTC] melindaetinw81 at gmail dot com
i am trying to solve it give me just a minute (https://www.mycfavisit.vip/)github.com
 [2023-09-05 09:08 UTC] allenprise at gmail dot com
i am trying to solve it give me just a minute github.com
 [2024-05-03 10:07 UTC] amanda2369weaver at outlook dot com
Thank you for this information it is very helpful for me. (https://github.com)(https://www.my-sedgwick.com/)
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 14 12:01:27 2024 UTC