#39858 [Asn->Csd]: Lost connection to MySQL server during query by a repeated call stored proced

ID: 39858 Updated by: johannes@php.net Reported By: develar at gmail dot com -Status: Assigned +Status: Closed Bug Type: PDO related Operating System: Windows XP SP2 PHP Version: 5.2.0 Assigned To: wez New Comment: 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) Previous Comments: ------------------------------------------------------------------------ [2008-08-28 09:34:01] 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-07-21 15:06:16] 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-07-14 15:31:00] 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-05-02 16:48:43] 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-05-02 16:24:57] 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!) ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/39858 -- Edit this bug report at http://bugs.php.net/?id=39858&edit=1