php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #44081 Cannot execute queries while other unbuffered queries are active
Submitted: 2008-02-09 11:46 UTC Modified: 2009-09-09 20:54 UTC
Votes:51
Avg. Score:4.7 ± 0.7
Reproduced:46 of 46 (100.0%)
Same Version:16 (34.8%)
Same OS:8 (17.4%)
From: abouzekry at gmail dot com Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.2.9 OS: Windows XP SP2
Private report: No CVE-ID: None
 [2008-02-09 11:46 UTC] abouzekry at gmail dot com
Description:
------------
the same code runs on php5.2.0 without any problems, while it's problematic with php5.2.5 i don't know why!, it insists on giving
me the following message...

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

however i closed the cursor after each result fetch!!!!

Reproduce code:
---------------
if(isset($_POST['user_name'], $_POST['pass']))
{

	/* check wether the user exists or not */
	$result  = $db->query("CALL proc_check_user('{$_POST['user_name']}', '{$_POST['pass']}')")
					or die('0-1-Database-Query-Error-login.php:'.print_r($db->errorInfo()));
	$row     = $result->fetchAll(PDO::FETCH_ASSOC)
			or die('0-2-Database-Fetch-Error-login.php:'.print_r($db->errorInfo()));

	/* close the DB cursor to be able to perform the other querie(s)*/
	$result->closeCursor();

        /* if the user doesn't exist exit prematurely! */
	if($row['userExists'] == 0)
	{
		exit('0-2-Authentication-Error-login.php: User Doesnt Exist!');
	}

	/* get the user_id and full_name */
	$result  = $db->query("CALL proc_get_user_data('{$_POST['user_name']}', '{$_POST['pass']}')")
					or die('0-3-Database-Query-Error-login.php:'.print_r($db->errorInfo()));
	$row     = $result->fetch(PDO::FETCH_ASSOC);

}

Expected result:
----------------
the machine with php5.2.0 installed when running the same code produces
a correct authentication for valid users (POSTed) and error for invalid users.


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-02-25 14:18 UTC] stewart dot duncan at rocketmail dot com
Really annoying this one. Reproduced on 5.2.8.
 [2009-04-25 14:54 UTC] jani@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/


 [2009-05-03 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".
 [2009-05-08 20:11 UTC] xfurious at gmail dot com
Unless I'm losing my mind, this is happening on PHP 5.2.9-2/windows as 
well.
 [2009-05-16 20:12 UTC] abouzekry at gmail dot com
the bug wasn't resolved in the latest code snapshot, it's more than a year now!!!
 [2009-06-10 21:49 UTC] stewart dot duncan at rocketmail dot com
Confirmed, still broken in latest snapshot.
 [2009-07-23 08:30 UTC] sebastien dot barbieri at gmail dot com
Same issue with PHP 5.2.10 (Linux/Mac/Windows) with a similar code:

$this->dbh = new PDO($this->options['dsn'], $this->options['username'], $this->options['password'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.$this->options['charset']));
$this->dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$sth = $this->dbh->prepare($_stored_proc_1);
$sth->bindValue('I_bind', $value, PDO::PARAM_STR);
$sth->execute();
$object = $sth->fetchAll(PDO::FETCH_ASSOC);
$sth->closeCursor();
$sth = $this->dbh->prepare($_stored_proc_2);
$sth->bindValue('I_bind', $value, PDO::PARAM_STR);
$sth->execute();
$object = $sth->fetchAll(PDO::FETCH_ASSOC);
$sth->closeCursor(); 

However no problem at all if I install php 5.2.10 Zend Community Edition ... :-)
 [2009-09-06 14:46 UTC] webmaster at rdwonline dot com
I am using XAMPP (PHP 5.3.0 on Windows XP) and I am able to execute succeeding prepared statements with no problem as long as I call $PDOStatement->closeCursor() after I'm done with the previous one.

The problem arises with MySQL stored procedures.  PDO will execute and successfully retrieve records from the first stored procedure but all other statements afterwards WILL NOT EXECUTE, and I too get the error below:

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

I tried adding in the aforemention attribute to no avail.

Here is a snippet from a script I used to test this:

$PDOStatement = $PDO->prepare('SELECT * FROM MagicCards WHERE cardID = ?');
$PDOStatement->bindValue(1, 1600);
$PDOStatement->execute();
$PDOStatement->setFetchMode(\PDO::FETCH_ASSOC);
$rows = $PDOStatement->fetchAll();
$PDOStatement->closeCursor();
$PDOStatement = NULL;
print_r($rows); // works as expected

$PDOStatement = $PDO->prepare('CALL uspGetMagicCardByKey(?)');
$PDOStatement->bindValue(1, 10);
$PDOStatement->execute();
$PDOStatement->setFetchMode(\PDO::FETCH_ASSOC);
$rows = $PDOStatement->fetchAll();
$PDOStatement->closeCursor();
$PDOStatement = NULL;
print_r($rows); //works as expected


$PDOStatement = $PDO->prepare('SELECT * FROM MagicCards WHERE cardID = ?');
$PDOStatement->bindValue(1, 1600);
$PDOStatement->execute(); //errors out right here with the HY000 error
$PDOStatement->setFetchMode(\PDO::FETCH_ASSOC);
$rows = $PDOStatement->fetchAll();
$PDOStatement->closeCursor();
$PDOStatement = NULL;
print_r($rows); //doesn't get this far

The workaround would be to drop in your SQL from your stored procedure directly as an argument to $PDO->prepare().  But if you are doing any other modifications before or after that main SELECT statement, this is not a feasible solution.

This is a critical bug that needs to be fixed ASAP.
 [2009-09-09 11:42 UTC] webmaster at rdwonline dot com
I withdraw my bug report.  I did not realize I was still using libmysql.  Apparently I need the new MySQL native driver to get the most out of PDO_MYSQL.
 [2009-09-09 20:54 UTC] sjoerd@php.net
Closing as requested.
 [2010-09-16 10:52 UTC] andreas at schipplock dot de
running php 5.2.0-8+etch11 with debian and had the same problem.

What "fixed" it for me was simply: $stmt = null; <-- after every fetchAll. 

I set up a test machine with vanilla php 5.3.3 and there setting it to null wasn't required; worked without modification.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Mon Dec 09 20:01:28 2024 UTC