|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[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
(
)
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Thu Nov 21 17:01:32 2024 UTC |
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; }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, Denisphp 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; } } }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.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.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.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!)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.