|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #41997 SP call yields additional empty result set
Submitted: 2007-07-15 06:53 UTC Modified: 2020-12-09 15:45 UTC
Avg. Score:4.7 ± 0.6
Reproduced:8 of 8 (100.0%)
Same Version:4 (50.0%)
Same OS:6 (75.0%)
From: nitel_defect at yahoo dot com Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 5.2.3 OS: *
Private report: No CVE-ID: None
 [2007-07-15 06:53 UTC] nitel_defect at yahoo dot com
The code below calls a stored procedure that fethches the whole tree in a father-son system roles hierarchy.
In the end, the stored procedure selects everything from a temp table, so that's our result set.
After running the call statement, all the result rows are returned correctly but any future query (not only other calls, all queries) fail with the message you see below.
For the time being I replicated the code I needed in PHP, but it's frustrating not to be able to run SPs...
I tested the library found here:
and this bug was gone. But there is no source code for this modified library and its size is much smaller so I don't quite trust it.
Uhm... help?

Reproduce code:
$db = new PDO('mysql:host=localhost;dbname=2m;port=3306', 'root', '', array());
$st = $db->query("call getParentRoles('admin')");
do {
$rows = $st->fetchAll(PDO::FETCH_ASSOC);
} while ($st->nextRowset());
echo '<br />';
$st = $db->query("SELECT * FROM user");

Expected result:
array(1) { [0]=>  string(5) "00000" }
array(1) { [0]=>  string(5) "00000" }

Actual result:
array(1) { [0]=>  string(5) "00000" }
array(3) { [0]=> string(5) "HY000" [1]=> int(2014) [2]=> string(269) "Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute." } 


Add a Patch

Pull Requests

Pull requests:

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2007-07-16 17:30 UTC] temposeb at free dot fr
I encounter exactly the same bug for many months.
Environment : Win2000 Pro SP4, Apache 2.2, PHP 5.2.4-dev (Build Date 	Jul 11 2007 20:03:23), MySQL 5.1.20-beta-community-nt-debug (same thing with MySQL 5.0.x).
nitel_defect, where can we find the hacked DLL ? is actually DOWN. Thx.
 [2007-07-16 18:06 UTC] nitel_defect at yahoo dot com
 [2007-08-21 00:08 UTC] marpiotr at gmail dot com
i seem to have the same problem on 5.1.6 on Fedora Core 6
 [2008-01-22 13:28 UTC]
Reclassified: mysql -> pdo
 [2008-01-24 11:46 UTC] ben at stickyeyes dot com
Just to add to this bug, I too experience this problem when trying to execute two stored procedures, one after the other. 

Unfortunately, the only work around I could get working, was to create a new PDO object for each query (attempting to clone an existing PDO object caused Apache to crash, but thats another bug for another day...)

Sucks, but it works.

Apache/2.2.4 (Win32) PHP/5.2.3
PDO Driver for MySQL, client library version	5.0.37
MySQL 5.1.22
 [2008-05-22 17:32 UTC]
The next PDO_MYSQLND release should fix this
 [2008-07-22 14:40 UTC]
Please make sure that you try this with PHP 5.3+ and mysqlnd (--with-pdo-mysql=mysqlnd , default). It might not work when using the mysqlclient library (libmysql, --with-pdo-mysql=/path/to/mysql_config)
 [2008-07-30 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2013-08-11 03:36 UTC]
-Status: No Feedback +Status: Re-Opened -Operating System: Windows XP SP2 +Operating System: *
 [2013-08-11 03:36 UTC]
I still see test bug_41997 failing for me on master. Same happens on tracis CI:

Error message is:

Warning: PDOStatement::fetchAll(): SQLSTATE[HY000]: General error in 
/home/smalyshev/php-src/ext/pdo_mysql/tests/bug_41997.php on line 10

Client library version is:

Client API version => mysqlnd 5.0.11-dev - 20120503 - $Id: 
40933630edef551dfaca71298a83fad8d03d62d4 $
 [2014-01-01 12:53 UTC]
-Package: PDO related +Package: PDO MySQL
 [2017-10-24 05:22 UTC]
-Status: Re-Opened +Status: Assigned
 [2017-10-24 06:54 UTC]
-Status: Assigned +Status: Open -Assigned To: johannes +Assigned To:
 [2019-08-05 07:58 UTC]
-Status: Open +Status: Verified -Assigned To: +Assigned To: cmb
 [2019-08-05 07:58 UTC]
The originally reported issue has been basically resolved, but the
fix for bug #40935 broke the test case as reported by stas@ above.
The relevant behavior of calling stored procedures is described in
the documentation[1]:

| The final result from the procedure is a status result that
| includes no result set. The status indicates whether the procedure
| succeeded or an error occurred.

This is not properly handled by mysqlnd, though (not sure about
libmysqlclient yet), which simply proceeds to this status result
when ::nextRowset() is called, but then errors because the actual
result set is missing.

[1] <>
 [2019-08-06 12:57 UTC]
> not sure about libmysqlclient yet

Behaves the same.
 [2019-08-06 12:58 UTC]
The following pull request has been associated:

Patch Name: Fix #41997: SP call yields additional empty result set
On GitHub:
 [2019-08-08 14:11 UTC]
-Assigned To: cmb +Assigned To:
 [2019-09-03 11:33 UTC]
-Summary: pdo_mysql: stored procedure call returning single rowset blocks future queries +Summary: SP call yields additional empty result set -Assigned To: +Assigned To: cmb
 [2019-09-03 11:34 UTC]
Automatic comment on behalf of
Log: Fix #41997: SP call yields additional empty result set
 [2019-09-03 11:34 UTC]
-Status: Verified +Status: Closed
 [2019-10-01 13:31 UTC] attila at szeremi dot org
Could the fix to this issue have caused breakage with empty rowset iteration loops?

I had been using the workaround mentioned in the below ticket to be able to trigger exceptions in multi-statement PDO MySQL queries:

But I can pretty much confirm that my test code that does `while ($statement->nextRowset());` after a multi-statement query that in Bitbucket pipelines using the PHP 7.3.10 image the query doesn't seem to run at all (or maybe just the first one?), but in PHP 7.3.9 the multi-line statement is run correctly.

I would test in 3v4l, but I don't know how to test DB stuff there.
 [2019-10-01 13:51 UTC]
Can you please provide a minimal self-contained reproduce script?
 [2019-10-01 17:50 UTC] attila at szeremi dot org
Here it is. It is definitely related to this issue.
 [2019-10-07 07:21 UTC]
-Status: Closed +Status: Re-Opened -Assigned To: cmb +Assigned To:
 [2019-10-07 07:21 UTC]
Since the regression reported as bug #78623 couldn't be resolved
for now, I've reverted the fix for this bug, and re-open the
 [2020-12-09 15:45 UTC]
-Status: Re-Opened +Status: Closed -Assigned To: +Assigned To: nikic
 [2020-12-09 15:45 UTC]
We have decided to follow MySQL behavior here. As of PHP 8.0.1, SP calls will always include a trailing empty result set regardless of whether emulation is used or not and whether mysqlnd is used or not.

This is unfortunate, but MySQL doesn't seem to really leave us a choice in this matter, as we can't distinguish the trailing SP result from a legitimate multi-query result.
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed May 29 15:01:34 2024 UTC