Description:
------------
The second call stored procedures causes an error "SQLSTATE[HY000]:
General error: 2013 Lost connection to MySQL server during query" with
probability of 50%. I read #35333 #35637 #35203, but why the given code
fine works in Debian?

Reproduce code:
---------------
CREATE PROCEDURE `foo`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
     SELECT 2 * 2;
END;

<?php

$Db = new PDO('mysql:host=localhost;dbname=test', 'root', '',
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'",
PDO::ATTR_PERSISTENT => true));
$Db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$Db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

$Pdo = $Db->prepare('CALL foo()');
$Pdo->execute();
print_r($Pdo->fetchAll());

$Pdo = $Db->prepare('CALL foo()');
$Pdo->execute();
print_r($Pdo->fetchAll());
$Pdo->closeCursor();

?>

Expected result:
----------------
Array
(
    [0] => Array
        (
            [2 * 2] => 4
        )

)
Array
(
    [0] => Array
        (
            [2 * 2] => 4
        )

)

Actual result:
--------------
Array
(
    [0] => Array
        (
            [2 * 2] => 4
        )

)
<br />
<b>Warning</b>:  PDOStatement::execute() [<a
href='function.PDOStatement-execute'>function.PDOStatement-execute</a>]:
SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server
during query in <b>C:\home\test\www\pdo.php</b> on line <b>12</b><br />
Array
(
)

Please try using this CVS snapshot:

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

It is not fixed.

Works just fine on Linux.
Make sure you're really running the snapshot.

Почитайте
http://phpclub.ru/talk/showthread.php?s=&threadid=92764&rand=10

It always worked normally on linux. My first message: "I read #35333
#35637 #35203, but why the given code
fine works in Debian?" Only in windows.

I'm having this issue as well.

My bug report here: http://bugs.php.net/bug.php?id=39759 has some more
information. To recap:

I've tested this with php 5.2 release, as well as various recent
snapshots. I've tested with mysql 5.0.22 and 5.0.27. I've tested with
the libmysql.dll files that php ships with, as well as the libmysql.dll
that mysql ships with - they produce two different errors but the
problem is the same.

When using PHP's libmysql.dll the error is: SQLSTATE[HY000]:
General error: 2013 Lost connection to MySQL server during query

When using MySQL's libmysql.dll the error is: SQLSTATE[HY000]: General
error: 2014 Cannot execute queries while other
unbuffered queries are active.

When running through apache I get this error 100% of the time. When
running through the PHP command line interface, I get it about 50% of
the time, so perhaps there are performance issues here. I'm running on a
celeron 2.4GHz with 512 ram. Windows XP.

Please fix this!

Here are two more bug reports on pecl:

http://pecl.php.net/bugs/bug.php?id=7976
http://pecl.php.net/bugs/bug.php?id=5827

Again it seems intermittant with some people. I got a SQL error log
which showed this:

061213 11:27:36 [Warning] Aborted connection 1 to db: 'test' user:
'test' host: 'localhost' (Got an error reading communication packets)

Before anyone asks, I have been rd /s'ing the PHP directory when I try a
snapshot to make sure I'm running a clean version of everything.

Adding $this->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE);
does not stop the error.

nextRowset() isn't implemented so we can't fetch all the results
manually.

That is all.

Hi there
I have had the same problem but as far as I use PHP 5 why do not create
your own class to work with PDO. See example - this is a part of class.
I just stupidly catch the required error, make re-init of the Zend_Db
and run this quesry once again...Really stupidly, but this bug meets on
Windows only and for Nix this case wont' work so nothing serious:)
Good luck, Denis

public function ExecuteStoredProcedure($spName, $args)
	{
		$ch = ',:';
		$sp_str = "";
		if(sizeof($args) > 0) {
			$sp_str = 'CALL '.$spName.' (:'.implode($ch, array_keys($args)).')';
		}
		else{
			$sp_str = 'CALL '.$spName.'()';
		}
		$command = null;

		$command = $this -> Db -> prepare( $sp_str );
		$command -> setFetchMode ( Zend_Db::FETCH_ASSOC );
		foreach($args as $key => $par ){
			if(!is_object($par)) {
				trigger_error('Wrong parameters type in :'.$spName, E_USER_ERROR);
			}
			$command -> bindParam($par -> name, $par -> value, $par -> type, $par
-> size, array(
						PDO::ATTR_EMULATE_PREPARES => true) );	
		}
		try{
			$command -> execute();
		} 

		catch(PDOException $e) {
			if($e -> errorInfo[0] == !'HY000' && $e->errorInfo[0] !== '2013'){
				echo "www";
				throw($e);
			} 
			else {
				$command = $this -> Db -> prepare( $sp_str );
				$command -> setFetchMode ( Zend_Db::FETCH_ASSOC );
				try { 
					$command -> execute();
				} 
				catch(PDOException $e) {
					if($e -> errorInfo[0] == !'HY000' && $e->errorInfo[0] !== '2013'){
						throw($e);
					}	
					$command = null;
					$this -> Init();				
				}
			}
		}
		$rows = array();
		if($command != null){
			$rows = $command -> fetchAll();
			$command -> closeCursor();
			$this -> Init();
		}
		$command = null;
		return $rows;
	}

Well, some updates. The previous approach doesn't work with transactions
at all - see Init method. After long investigation I can say the next:
Windows XP SP/2
Apache 1.3/PHP 5.2/MySQL/5.**
MySql tables should be in InnoDB (possibly BDB) format - MYISAM doesn't
support transactions yet.

Class to work with DB:
<?php
/**
 * eXtended Management System
 *
 * LICENSE
 *
 * This source file is subject to the new BSD license that is bundled
 * with this package in the file LICENSE.txt.
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@zend.com so we can send you a copy immediately.
 *
 *
 * @category   Xms
 * @package    Xms_Core
 * @copyright  Copyright (c) 2006 CoFe Lab. (http://www.cofelab.ru)
 * @license    http://www.cofelab.ru/license
*/

class Xms_Do 
{
    /* Begin class */

	/**
    * @var Zend Db factory object.
    */
	public $Db;

	/**
    * @var array
    */	
	private $_params;

	/**
    * @var string. Type of the PDO object.
    */	
	private $_config;
	
	/**
    * Construct 
    * @param object. Zend config object.
    */
	public function __construct( $config )
	{
		
		Zend::loadClass('Zend_Db');
		$this -> _config = $config -> database;
		
  	    $this -> _params  = array ('host'=> $this -> _config -> host,
							'port' => $this -> _config -> port,
							'username' => $this -> _config -> username,
							'password' => $this -> _config -> password,
							'dbname'   => $this -> _config -> name,
							 PDO::ATTR_PERSISTENT => true); 
		$this -> Init();
	}

	/**
    * Init database connection
    */
	public function Init()
	{	
		if(strstr($_SERVER['SERVER_SOFTWARE'],'Win')){
			$this -> Db = new PDO($this -> _config -> odbc, $this -> _config ->
username, $this -> _config -> password);	
		} else {
			$this -> Db = Zend_Db::factory( $this -> _config -> type, $this ->
_params);
		}
		$this -> Db -> query("SET NAMES '".$this -> _config -> charset."'");
		$this -> Db -> query("SET CHARACTER SET '".$this -> _config ->
charset."'");
	}

	private function InitOdbc(){
		
	}

	/**
    * Execute stored procedure.
	* @param string. Stored procedure's name.
	* @param array. DbParameter objects.
    */	
	public function ExecuteStoredProcedure($spName, $args)
	{
		//echo $spName;
		$ch = ',?';
		$sp_str = "";
		if(sizeof($args) > 0) {
			$arr = array_fill ( 1, sizeof($args),null);
			$sp_str = 'CALL '.$spName.' (?'.implode($ch,
array_values($arr)).')';
		}
		else{
			$sp_str = 'CALL '.$spName.'()';
		}
		$command = null;

		$command = $this -> Db -> prepare( $sp_str );
		$count = 1;
		$var = '@SiteId';
		foreach($args as $key => $par ){
			if(!is_object($par)) {
				trigger_error('Wrong parameters type in :'.$spName, E_USER_ERROR);
			}
			if($par -> parType == 'OUTPUT'){
				
				$command -> bindParam($count, $var, $par -> type, $par -> size,
array(
						PDO::ATTR_EMULATE_PREPARES => true) );
			} else {
				$command -> bindParam($count, $par -> value, $par -> type, $par ->
size, array(
						PDO::ATTR_EMULATE_PREPARES => true) );

			}
	
			$count ++;
		}

		$command -> execute();

		$rows = array();
		if($command != null){
			$rows = $command -> fetchAll();
			$command -> closeCursor();
			$command -> nextRowset();
			//$this -> Init();
		}
		$command = null;
		return $rows;
	} 
	 /**
    * Desturctor.
    */		

	public function __destruct()
	{
		$this -> Db = null;

	}   

 /* End class */
}

Config file (uses Zend config to read it):
    <database>
      <type>Pdo_Mysql</type>
      <host>localhost</host>
      <port>3307</port>
      <username>root</username>
      <password></password>
      <name>xms</name>
      <charset>utf8</charset>
      <odbc>odbc:DSN=XMS_MySQL;</odbc>
    </database>

Wrapper for DB parameter
<?php
/**
 * XMS Framework
 *
 * LICENSE
 *
 * This source file is subject to the license that is bundled
 * with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://xms.cofelab.ru/license/
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@cofelab.ru so we can send you a copy immediately.
 *
 * @category   Xms
 * @package    Xms_Core
 * @copyright  Copyright (c) 2006 Cofelab Russia.
(http://www.cofelab.com)
 * @license    http://xms.cofelab.ru/license/ 
 */

class Xms_Db_Parameter
{
	/**
	* Presents name of the class.
	* @staticvar string
	*/
	public static $ClassName = "Xms_Db_Parameter";

	/**
	* Presents  name of the class.
	* @staticvar string
	*/
	public $name;
	public $value;
	public $type;
	public $size;
	public $parType;
	
	public function __construct($name, $value, $type, $size)
	{
		$this -> name  = $name;
		$this -> value = $value;
		$this -> type  = $type;
		$this -> size  = $size;
	}
	
	
	
}

An example:

		try{
			$this -> _modDO -> Db -> beginTransaction();

			$_siteId = null;
			$parSiteUrl = new Xms_Db_Parameter('Url', $args -> siteadddomain,
PDO::PARAM_STR, 100);
			$parSiteDesc = new Xms_Db_Parameter('Description', $args ->
siteadddescription, PDO::PARAM_STR, 255);

			$rows = $this -> _modDO -> ExecuteStoredProcedure( Xms_Constants ::
$prcSiteAddItem, array('Url'=>$parSiteUrl, 'Description' =>
$parSiteDesc));

			
			$node ="<siteadddomain><value>".$args ->
siteadddomain."</value><error sysmes='siteadddomainerrex'
/></siteadddomain>";
			
			$this -> _modDO -> Db -> commit();

		} catch(Exception $e) {
			echo $e -> getMessage();
			$this -> _modDO -> Db -> rollBack();
		}

PS. Not tested under Nix yet - I'll update the topic.

One more thing - you need to install MySQL ODBC driver to work with
MySQL in this example: see dsn string. I use named DSN - XMS_MySQL based
on MySQL ODBC 3.51.12 Win driver.

It works under Nix as well. So, good luck.

Thanks for the help Denis, although I can't personally implement this
workaround...

For the time being I have hacked in lines of code to create a new
database connection before calling certain stored procedures.

Now, PLEASE could a developer or someone RESPOND and acknowledge this
bug, and let us know what is going on!?

I've been stuck with this bug for months and months with no help
whatsoever from the PHP guys.

Hi once again.
Step by step instruction how to work with MySQL sp/transaction under Win
XP SP 2
1. Install MySQL ODBC driver.
2. Create database with tables in InnoDB format (to support transaction
- see my.cnf).
3. Create DSN with connection to your database (see Admin tools->ODBC).
4. Enable pdo_odbc within php.ini file.
5. Use this to create connection
		if(strstr($_SERVER['SERVER_SOFTWARE'],'Win')){
			$this -> Db = new PDO("odbc:DSN=MY_MySQL_ODBC;", user, password);	
		} else {
			$this -> Db = Zend_Db::factory( PDO_MYSQL);
6. When the sp has been executed do not forget to fetch the statement:
			$command -> execute();
			do {
			   $rows = $command->fetchAll();
			} while ($command -> nextRowset());
That's all. This code will work under win/nix without any diffireneces -
checked by me.
If you still have any problems - just email me and I'll contact you by
ICQ/Skype to help (I spent four weeks to solve it).
Regards, Denis

That would technically be a workaround rather then a fix.  The issue is
still there, and switching to ODBC is not necessarily a good or
acceptable solution.  Also as we have taken onboard the Zend Framework
prior to beginning work on Zend Platform, creating non-standard
solutions doesn't sound like a forward step.  

However, until the PHP team pull their fingers out and assess PDO to
work with Windows properly, I may have to implement it.  Thank goodness
that the partnership with Microsoft will force PHP to stop treating IIS
as a perochial platform and get proper support.

php 5.2.1
win xp pro
mysql 5x
apache 2x

I also built a project assuming stored procedures would work
as they are mentioned in the php docs, anyway.

to cut a long story short, I extended PDO with methods that included not
connectimg to DB until actually needed.

public function connect(){
try{
parent::__construct($this->connect_a['DSN'], $this->connect_a['U'],
$this->connect_a['P']);
} catch (Exception $e) {
throw($e);
}
### always use exception error handling
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->connected=1;
}//

When  calling a 2nd query (after a stored procedure) I get the
"SQLSTATE[HY000]:
General error: 2013 Lost connection to MySQL server during query" every
time.

I discovered calling "$pdoextended->connect();" after each use of a
stored procedure revived the connection (without instantiating another
PDOextended obj) with no errors.

This is ONLY a bodge-it but it is a very lightweight one.

AHHHHHH!!!!

My bodge-it above doesn't work correctly, it just opens but doesn't
close many connections.

This does work (tried several times on 200 consec' queries)
added closeCursor().

$i=100;
	
	while($i>0){
	echo 'LOOP NUM:'.$i.'<br />';
		try{
			$stmt=$pdodl_1->query("call testMany()");
			$stmt->setFetchMode(PDO::FETCH_ASSOC);
	
			echo '<br />PDODL OBJ: ';
			var_dump($pdodl_1);
			echo '<br />PDO::STATEMENT OBJ: <br />';
			var_dump($stmt);
			echo '<br /><br />';
	
			while ($row= $stmt->fetch()) {
				echo '<br />';
				var_dump($row);
				echo '<br />';
   		}
		$i--;
	
	
		}catch(PDOException $e){
			if($e->getCode()=='HY000'){
			$stmt->closeCursor();
			$pdodl_1->connect();
			$i--;
			} else {
			throw $e;
			}
		}
	}

I've got a dirty solution for your problem (works under linux, not
tested on windows)

if i do:

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute( PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

$datasets = $dbh->query( 'SELECT * FROM table_name_1 WHERE id=5);
$datasets->execute();
$result_1 = $datasets->fetch( PDO::FETCH_ASSOC);

$datasets = $dbh->query( 'SELECT * FROM table_name_2 WHERE id=7);
$datasets->execute();
$address = $datasets->fetch( PDO::FETCH_ASSOC);

it gets the same unbuffered error. But if I do a fetch twice on the
first result and store it to a dummy array, the second query works:

$datasets = $dbh->query( 'SELECT * FROM table_name_1 WHERE id=5);
$datasets->execute();
$result_1 = $datasets->fetch( PDO::FETCH_ASSOC);

// test, remove if bug is fixed
$dummy = $datasets->fetch( PDO::FETCH_ASSOC);

$datasets = $dbh->query( 'SELECT * FROM table_name_2 WHERE id=7);
$datasets->execute();
$address = $datasets->fetch( PDO::FETCH_ASSOC);

I think the cursor-pointer does not work correctly?? You can also do a
fetchAll(), but then your result looks like:

array[0] => ( array=>([prename] => 'Martin' [name] => 'Schmitz'))

instead of

array=>([prename] => 'Martin' [name] => 'Schmitz')

Hope it works for you

previous doesn't address stored procedure prob (from what i've read this
is a win prob only).

my revised approach:
Essentialy after you use a stored procedure call, burn another  query to
force an exception.

###
private $connect_a=array();
private $connected=0;

public function __construct($dsn=NULL, $user=NULL, $pass=NULL){
	if(is_array($dsn)){
	$this->connect_a['DSN']=$dsn[0];
	$this->connect_a['U']=$dsn[1];
	$this->connect_a['P']=$dsn[2];
	} else {
	$this->connect_a['DSN']=$dsn;
	$this->connect_a['U']=$user;
	$this->connect_a['P']=$pass;
	}
}//

public function query($q){
	if($this->connected==0){
	$this->connect();
	}
return parent::query($q);
}//

public function dropConnection($stmt){
$stmt->closeCursor();
$this->connected=0;
}##

public function callStoredProcedure($stmt){
$resultset;
try{
	#multi array
	while ($row= $stmt->fetch()) {
	$resultset[]=$row;
	echo '<br />';
	var_dump($row);
	echo '<br />';
   }
	#burn
	if(stripos($_SERVER['SERVER_SOFTWARE'], 'win') ){
	$stmt=$this->query("select 1+1");
	}
			
			
}catch(PDOException $e){
	if($e->getCode()=='HY000' AND strpos ($e->getMessage(), 'Lost
connection' )){
	print "<-!!!- Error!: Caught 'Lost connection error, dropConnection()
-!!!-><br />";
	$this->dropConnection($stmt);
	} else {
	throw $e;
	}
}
return $resultset;
}//

public function connect(){
#if($this->connected==1) return true;
try{
parent::__construct($this->connect_a['DSN'], $this->connect_a['U'],
$this->connect_a['P']);
} catch (Exception $e) {
throw($e);
}
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->connected=1;
}//

#EXAMPLE
#just to prove obj is same one
$pdodl_1->temp_id='AAAAAA';

$i=100;
	
	do{
	echo 'LOOP NUM:'.$i.'<br />';
	echo '<br />PDODL OBJ: ';
	var_dump($pdodl_1);
	echo '<br /><br />';
	$stmt=$pdodl_1->query("call testMany()");
	$stmt->setFetchMode(PDO::FETCH_ASSOC);
	$rset=$pdodl_1->callStoredProcedure($stmt);
			
	$i--;
	} while($i>0);

This is all from another project, so may need some tinkering.

What I have stated previously works, but only with the PHP vers. of
libmysql.dll {error lost connection}
mysql vers. {error unbuffered queries} just keeps making new connections
until it is maxed out, have found no solution to this.

I am using the very latest vers of everything.

Hi everyone. We have a fixed php_pdo_mysql.dll in our svn repos - but
the guy that compiled it all hasn't got the source together because it
involved a massive overhaul of the php and extension code to support the
latest mysql libs.

I know your first thoughts will be "There's no way I'm going to use a
.dll from a *hackthissite*!", that's fine... but the guy that compiled
it can be trusted, and I've been using it on my windows machine for a
week or two now.

http://source.hackthissite.org/wsvn/HTSv4/trunk/PDO/php_pdo_mysql.dll

I'll see if we can get some sort of source code version together. Until
then, use this at your own risk.

Hi mike, thanks for the fixed php_pdo_mysql.dll! I tested it right now
and it works perfectly!
It took me quite the whole day finding out that it was a php bug what
made my stored procedures producing those exceptions (on Windows IIS).

But now I'm asking myself if your fix will find its way into the next
PHP release so that I can be sure my code will work at my customers
Windows servers too or if I should use some script language that
supports stored procedures as they are meant...

A reliable answer in this issue would be great! ;-)

I am using this modified DLL, above, and it works great, in most
regards. The core issue still exists, however. Mulitple calls to stored
procedures are still causing db connection loss. I've had to go
lazy-load kuh-ray-zee on my objects to avoid connection failures. I
shouldn't have to use the wrong patterns in order to work around a
faulty db driver.

Mike, any chance you can post your hacked source somewhere?

I am still not able to issue two stored procedures in a row using PDO
and mysql 5.0.31, php 5.2.1 and windows server 2003 when using the php
pdo_mysql.dll extension.  Is there any fix for this planned?  It has
been a very long time since it was first noted. If there is a solution
other than using a hacked DLL with no source code, can somebody please
advice.  This is the simplest way to see it happen.

$stmt = $myPdoConnection->query("CALL myStoredProc ('x',  'y')");

//this returns results
print_r($stmt->fetchAll());

//this does not - says mysql lost connection
print_r($stmt->fetchAll());

If I do this with SELECT statements instead I get no problem.  I believe
that there is an additional row set send back with stored procedures
from mysql that needs to be interated over in order to issue the next
statement.  However, I cannot access them with 

$stmt->nextRowset();

as I get "Warning: PDOStatement::nextRowset()
[function.PDOStatement-nextRowset]: SQLSTATE[HYC00]: Optional feature
not implemented in "

Please help.

Well, this is very disappointing. A new PHP version is out and this
stupid bug has not been fixed although a solution seems to be found by
mike at we11er dot co dot uk.

I can't use PHP5 without this hacked dll on our servers. Seems like no
one feels responsible for developers on windows servers. 
"There's no bug on linux so what are you talking about...?"

As an update to this, I'm not sure that this is a bug in PHP at all - I
believe it's a bug with MySQL. Have stumbled across this bug report
today after receiving MySQL lost connections from the MySQL command
line, and from Navicat Terminal - in both cases there was no PHP
involved at all, just an SP that calls a function in a loop which
subsequently calls another function. The SP returns the first row, but
then dies with the error message and goes into an irretrievable loop (in
Navicat).

Don't know if this will help, but thought I would share...

What's the status of this bug?
I mean the part about any query ran after a fetchAll from a stored
procedure call, saying that there are still unfetched results. And that
happens even with a nextRowset call :)
The bug still exists in the last library from snaps, on Windows.

Well, months pass by and nothing happens... ;-(

Same problem presist on Windows XP with PHP v5.2.5. Again, here is the
error message. When a query is executed after calling a stored
procedure.

code: HY000, msg: 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.

This PDO bug makes it impossible to install Magento on Windows
platforms. See
http://www.magentocommerce.com/wiki/general/installing_on_windows_with_x
ampp_and_wamp for further information.

Hi there - so is there a proper fix for this by now? I just started
using stored procedures more extensively and I am now hit by this bug
all the time (Windows XP SP2, MySQL 5.0.51a, PHP 5.2.1)! Apparently this
bug is a known problem since at least October 2005 (Bug #5827), so I'd
think this should be long fixed?? However, I cannot locate any
information on a proper fix except for a bunch of makeshift workarounds
(using ODBC, abandoning the connection on HY000 etc.), and tons of
people complaining about it not being fixed. 

Considering how long this bug is known and open, it would help if
someone could post some update. This issue is really really annoying.

Thanks!

Same problem here-I'm on Vista Ultimate SP1 and IIS 7 MySQL 5.0.45

The trick is regular NON-Select queries work as expected. It's when your
Stored Procedures return result sets the problems show up. 

So for example, if the first query performs a one off select and closes,
the second query is hit with the "SQLSTATE[HY000]: General error: 2014
Cannot execute queries while other unbuffered queries are active error".
And yes, that's with using the fetchAll.

However, if I remove the select statement from the first procedure and
run the code again, both stored procedures work fine.  

I've tried the latest snap-shots, but unfortunately I couldn't get pdo
to even load. Any ideas? Updates?

One last thing:

It should be noted that you can "workaround" this issue by simply
instantiating a whole new PDO object after every call that returns a
result set.

// create a PDO instance up here somewhere and perform your query...

// get the return values you need...
$result = $sth->fetchAll ();

// and just create a new object...
try {
	$dbh = new PDO ( $dsn, $user, $pass );
	$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, 1);
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
} catch ( PDOException $e ) {
	die ( 'PDO Connection Failed: ' . $e->getMessage () );
}

// now you can query again without the error. 

It seem like the solution is simple to do. In fact, this guy seems to be
on the right track:
http://bugs.php.net/bug.php?id=42499

Why isn't this done!

Oh well, hopefully the above, if not totally wrong : ) will help someone
else.

Also having this error. PHP 5.2.5 on XP SP2, with Apache2 (XAMPP 1.6.6)

I believe paulsidekick is correct.  I am working on a page that
repeatedly calls a stored proc that returns a resultset.  After using
the expected results from the first call, I tried moving to the next
resultset and displaying the column values.  I got nothing.  Then I
tried using

	mysqli_free_result($rs);
	while (mysqli_more_results($conn)) {
		mysqli_next_result($conn);
		echo count($row);
		mysqli_free_result($rs);
	}

and got "0".  So I decided to just get rid of the empty resultset. 
Below is the basic structure of the code that worked for me (note the
while loop).

	$conn = mysqli_connect("server", "username", "password");

	for ($i=1; $i<$someNumber; $i++) {
		$rs = mysqli_query($conn, "CALL spMyProc(param1, param2)");
		
		if ($rs && $row = mysqli_fetch_asoc($rs)) {
			//some code to use results
		} else {
			//error handler
		}
		
		mysqli_free_result($rs);
		
		//Add this section to dispose of extra resultset.
		while (mysqli_more_results($conn)) {
			mysqli_next_result($conn);
			mysqli_free_result($rs);
		}
	}

	mysqli_close($conn);

I know this doesn't exactly fix the problem, but it is a workaround that
involves a minimal amount of code.

(By the way, why doesn't the CAPTCHA box show up in Firefox?  I had to
use Internet Exploiter to post this.  I thought PHP was all about being
open source!)

After looking at the PHP manual, I realized I could reduce the loop to a
single line:

	while (mysqli_next_result($conn));

I didn't even have to use mysqli_free_result (but it is good practice).

This bug occurs with Apache/2.0.63 (Win32) PHP/5.2.6 and MySQL 5.0.51b.

I don't use PDO. I use mysqli calls.

The work-around by bepoteat helps though.

The single line workaround worked for me!

	while (mysqli_next_result($conn));

Thank you bepoteat!

This is much better than my previous workaround which was to close and
reopen my database connection before my subsequent stored procedure
call.

John

Fatal error: SQLSTATE[HY000]: General error: 2014 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. in
C:\xampp\htdocs\tshirtshop\business\database_handler.php on line 102

This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

Fixed when using mysqlnd (Windows default, else,
--with-pdo-mysql=mysqlnd)