php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #39858 Lost connection to MySQL server during query by a repeated call stored proced
Submitted: 2006-12-17 14:30 UTC Modified: 2019-08-05 07:40 UTC
Votes:93
Avg. Score:4.6 ± 0.8
Reproduced:76 of 78 (97.4%)
Same Version:48 (63.2%)
Same OS:60 (78.9%)
From: develar at gmail dot com Assigned: cmb (profile)
Status: Duplicate Package: PDO MySQL
PHP Version: 5.2.0 OS: *
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: develar at gmail dot com
New email:
PHP Version: OS:

 

 [2006-12-17 14:30 UTC] develar at gmail dot com
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
(
)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-12-18 08:34 UTC] tony2001@php.net
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


 [2006-12-18 08:43 UTC] develar at gmail dot com
It is not fixed.
 [2006-12-18 09:20 UTC] tony2001@php.net
Works just fine on Linux.
Make sure you're really running the snapshot.
 [2006-12-18 09:28 UTC] develar at gmail dot com
&#1055;&#1086;&#1095;&#1080;&#1090;&#1072;&#1081;&#1090;&#1077; 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.
 [2006-12-18 12:18 UTC] mike at we11er dot co dot uk
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!
 [2006-12-18 12:26 UTC] mike at we11er dot co dot uk
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.
 [2007-01-29 11:30 UTC] denis dot podgurskiy at cofelab dot ru
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;
	}
 [2007-02-06 17:11 UTC] denis dot podgurskiy at cofelab dot ru
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.
 [2007-02-06 17:13 UTC] denis dot podgurskiy at cofelab dot ru
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.
 [2007-02-07 09:25 UTC] denis dot podgurskiy at cofelab dot ru
It works under Nix as well. So, good luck.
 [2007-02-15 13:56 UTC] mike at we11er dot co dot uk
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.
 [2007-02-19 18:45 UTC] denis dot podgurskiy at cofelab dot ru
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
 [2007-02-20 14:24 UTC] barney dot hanlon at markettiers4dc dot com
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.
 [2007-02-22 11:37 UTC] james dot cordon at btinternet dot com
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.
 [2007-02-22 12:39 UTC] james dot cordon at btinternet dot com
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;
			}
		}
	}
 [2007-02-23 11:29 UTC] martin dot schmitz at uni-bielefeld dot de
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
 [2007-02-23 12:09 UTC] james dot cordon at btinternet dot com
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.
 [2007-02-27 16:29 UTC] james dot cordon at btinternet dot com
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.
 [2007-03-09 13:24 UTC] mike at we11er dot co dot uk
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.
 [2007-03-26 19:57 UTC] timo at hhesse dot de
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! ;-)
 [2007-04-09 19:13 UTC] jaylehvee at srgtampa dot com
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?
 [2007-05-18 21:12 UTC] paulsidekick at gmail dot com
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.
 [2007-06-21 12:58 UTC] timo at hhesse dot de
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...?"
 [2007-07-10 13:38 UTC] matt dot keeble at gmail dot com
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...
 [2007-07-14 20:12 UTC] nitel_defect at yahoo dot com
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.
 [2007-09-12 15:20 UTC] timo at hhesse dot de
Well, months pass by and nothing happens... ;-(
 [2007-12-17 08:33 UTC] poon dot fung at gmail dot com
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.
 [2008-02-07 12:57 UTC] kraus at phoenix-medien dot de
This PDO bug makes it impossible to install Magento on Windows platforms. See http://www.magentocommerce.com/wiki/general/installing_on_windows_with_xampp_and_wamp for further information.
 [2008-04-05 16:56 UTC] php at pofik dot com
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!
 [2008-04-05 20:53 UTC] mgrdinic at sledxchange dot com
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?
 [2008-04-05 21:04 UTC] mgrdinic at sledxchange dot com
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.
 [2008-04-17 14:44 UTC] james dot lewis at americanmobileventures dot com
Also having this error. PHP 5.2.5 on XP SP2, with Apache2 (XAMPP 1.6.6)
 [2008-05-02 16:24 UTC] bepoteat at yahoo dot com
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!)
 [2008-05-02 16:48 UTC] bepoteat at yahoo dot com
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).
 [2008-07-14 15:31 UTC] ile at suomi dot finland
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.
 [2008-07-21 15:06 UTC] jpmasseria at hotmail dot com
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
 [2008-08-28 09:34 UTC] sakha5413 at gmail dot com
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
 [2008-09-12 23:02 UTC] johannes@php.net
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)
 [2013-08-11 03:39 UTC] stas@php.net
-Status: Closed +Status: Re-Opened -Operating System: Windows XP SP2 +Operating System: *
 [2013-08-11 03:39 UTC] stas@php.net
Still seeing this problem in test bug_39858. See:

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

and also on my local Linux, 64-bit, produces this on master:

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

Warning: PDOStatement::fetchAll(): SQLSTATE[HY000]: General error in 
/home/smalyshev/php-src/ext/pdo_mysql/tests/bug_39858.php on line 28
 [2013-12-13 07:48 UTC] wez@php.net
-Assigned To: wez +Assigned To:
 [2014-01-01 12:53 UTC] felipe@php.net
-Package: PDO related +Package: PDO MySQL
 [2014-01-14 22:11 UTC] gvinaj at hush dot ai
Still seeing this issue with Yii framework migrations.
Win2008R2 x64, IIS7.5, PHP5.4.9 (tried also 5.5.3), MySQL 5.7.3-m13
Seeing the following error while running Yii migrations that include multiple SQL statements against MySQL:
"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..."

For some reason even the suggested setup with 'PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=true' attribute is not working for me.
Any suggested workaround or idea why is not working?
 [2014-02-25 14:18 UTC] uw@php.net
Core test code works just fine for me using mysqlnd from 

nixnutz@linux-dstv:~/src/php-src> sapi/cli/php -v
PHP 5.4.26-dev (cli) (built: Feb 11 2014 09:32:10) (DEBUG)


Getting this from emulation:

array(1) {
  [0]=>
  array(1) {
    ["2 * 2"]=>
    string(1) "4"
  }
}
array(0) {
}
array(1) {
  [0]=>
  array(1) {
    ["2 * 2"]=>
    string(1) "4"
  }
}
array(0) {
}


... and the very same from native PS. Code is the one from the test:

  $db->exec("DROP PROCEDURE IF EXISTS p");
        $db->exec("
                CREATE PROCEDURE p()
                        NOT DETERMINISTIC
                        CONTAINS SQL
                        SQL SECURITY DEFINER
                        COMMENT ''
                BEGIN
                        SELECT 2 * 2;
                END;");

        $stmt = $db->prepare("CALL p()");
        $stmt->execute();
        do {
                var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
        } while ($stmt->nextRowset());

        $stmt = $db->prepare("CALL p()");
        $stmt->execute();
        do {
                var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
        } while ($stmt->nextRowset());


So if there is an issue, someone should write a comprehensive test different from the bug39858 test and provide it. Otherwise it is: can't repeat with recent versions of PHP and MySQL.
 [2019-08-05 07:40 UTC] cmb@php.net
-Status: Re-Opened +Status: Duplicate -Assigned To: +Assigned To: cmb
 [2019-08-05 07:40 UTC] cmb@php.net
I'm closing this as duplicate of bug #41997.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Oct 04 15:01:28 2024 UTC