php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #41997 pdo_mysql: stored procedure call returning single rowset blocks future queries
Submitted: 2007-07-15 06:53 UTC Modified: 2014-01-01 12:53 UTC
Votes:8
Avg. Score:4.9 ± 0.3
Reproduced:7 of 7 (100.0%)
Same Version:3 (42.9%)
Same OS:5 (71.4%)
From: nitel_defect at yahoo dot com Assigned: johannes
Status: Re-Opened Package: PDO MySQL
PHP Version: 5.2.3 OS: *
Private report: No CVE-ID:
Have you experienced this issue?
Rate the importance of this bug to you:

 [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

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: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
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Mon Apr 21 14:02:18 2014 UTC