php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #41997 SP call yields additional empty result set
Submitted: 2007-07-15 06:53 UTC Modified: 2020-12-09 15:45 UTC
Votes:10
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
View Add Comment Developer Edit
 [2007-07-15 06:53 UTC] nitel_defect at yahoo dot com 
Description:
------------
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.
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is on by default.
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: http://source.hackthissite.org/svn/trunk/PDO/php_pdo_mysql.dll
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:
---------------
<?php
$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());
$st->closeCursor();
var_dump($db->errorInfo());
echo '<br />';
$st = $db->query("SELECT * FROM user");
var_dump($db->errorInfo());

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." }

Patches

Add a Patch

Pull Requests

Pull requests:

Add a Pull Request

History

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 ? http://source.hackthissite.org/svn/trunk/PDO/php_pdo_mysql.dll is actually DOWN. Thx.
 [2007-07-16 18:06 UTC] nitel_defect at yahoo dot com 
http://www.kpsol.ro/php_pdo_mysql.dll
 [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] felipe@php.net 
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] uw@php.net 
The next PDO_MYSQLND release should fix this
 [2008-07-22 14:38 UTC] uw@php.net 
Please try using this CVS snapshot:

  http://snaps.php.net/php5.3-latest.tar.gz
 
For Windows (zip):
 
  http://snaps.php.net/win32/php5.3-win32-latest.zip

For Windows (installer):

  http://snaps.php.net/win32/php5.3-win32-installer-latest.msi
 [2008-07-22 14:40 UTC] uw@php.net 
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] stas@php.net
-Status: No Feedback +Status: Re-Opened -Operating System: Windows XP SP2 +Operating System: *
 [2013-08-11 03:36 UTC] stas@php.net 
I still see test bug_41997 failing for me on master. Same happens on tracis CI: 

https://travis-ci.org/php/php-src/builds/10062028

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:

pdo_mysql
Client API version => mysqlnd 5.0.11-dev - 20120503 - $Id: 
40933630edef551dfaca71298a83fad8d03d62d4 $
 [2014-01-01 12:53 UTC] felipe@php.net
-Package: PDO related +Package: PDO MySQL
 [2015-07-10 12:41 UTC] spam2 at rhsoft dot net 
Related To: Bug #70039
 [2015-07-10 12:41 UTC] spam2 at rhsoft dot net 
Related To: Bug #70039
 [2017-10-24 05:22 UTC] kalle@php.net
-Status: Re-Opened +Status: Assigned
 [2017-10-24 06:54 UTC] kalle@php.net
-Status: Assigned +Status: Open -Assigned To: johannes +Assigned To:
 [2019-08-05 07:40 UTC] cmb@php.net 
Related To: Bug #39858
 [2019-08-05 07:58 UTC] cmb@php.net
-Status: Open +Status: Verified -Assigned To: +Assigned To: cmb
 [2019-08-05 07:58 UTC] cmb@php.net 
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] <https://dev.mysql.com/doc/refman/8.0/en/c-api-multiple-queries.html>
 [2019-08-06 12:57 UTC] cmb@php.net 
> not sure about libmysqlclient yet

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

Patch Name: Fix #41997: SP call yields additional empty result set
On GitHub:  https://github.com/php/php-src/pull/4496
Patch:      https://github.com/php/php-src/pull/4496.patch
 [2019-08-08 14:11 UTC] cmb@php.net
-Assigned To: cmb +Assigned To:
 [2019-09-03 11:33 UTC] cmb@php.net
-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] cmb@php.net 
Automatic comment on behalf of cmbecker69@gmx.de
Revision: http://git.php.net/?p=php-src.git;a=commit;h=41a4379cb45419a376043ca5f8c5a2bca82cea7c
Log: Fix #41997: SP call yields additional empty result set
 [2019-09-03 11:34 UTC] cmb@php.net
-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:
https://bugs.php.net/bug.php?id=61613

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] cmb@php.net 
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.

https://github.com/amcsi/php7.3.10-pdo-mysql-regression
 [2019-10-03 11:03 UTC] cmb@php.net 
Related To: Bug #78623
 [2019-10-07 07:21 UTC] cmb@php.net
-Status: Closed +Status: Re-Opened -Assigned To: cmb +Assigned To:
 [2019-10-07 07:21 UTC] cmb@php.net 
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
ticket.
 [2020-12-09 15:45 UTC] nikic@php.net
-Status: Re-Opened +Status: Closed -Assigned To: +Assigned To: nikic
 [2020-12-09 15:45 UTC] nikic@php.net 
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: Tue Apr 23 14:01:31 2024 UTC