#39858 [Com]: Lost connection to MySQL server during query by a repeated call stored proced

From: Date: Thu, 28 Aug 2008 09:34:02 +0000
Subject: #39858 [Com]: Lost connection to MySQL server during query by a repeated call stored proced
References: 1  Groups: php.bugs 
Request: Send a blank email to php-bugs+get-128496@lists.php.net to get a copy of this message 
 ID:               39858
 Comment by:       sakha5413 at gmail dot com
 Reported By:      develar at gmail dot com
 Status:           Assigned
 Bug Type:         PDO related
 Operating System: Windows XP SP2
 PHP Version:      5.2.0
 Assigned To:      wez
 New Comment:

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


Previous Comments:
------------------------------------------------------------------------

[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!)

------------------------------------------------------------------------

[2008-04-17 14:44:34] james dot lewis at americanmobileventures dot com

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

------------------------------------------------------------------------

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

Thread (44 messages)

« previous php.bugs (#128496) next »