PDO "Unhandled exception" PDOException ".. Unable to execute requests while other unbuffered requests are active. Consider using PDOStatement :: fetchAll ()".

Question

I know this question has been asked many times, but I have read the answers to many questions and still cannot understand why I am getting this error:

Fatal error: thrown a 'PDOException' exception with the message 'SQLSTATE [HY000]: general error: 2014 Unable to execute queries while other unbuffered queries are active. Consider using PDOStatement :: fetchAll (). Also, if your code is only ever to run with mysql, you can enable query buffering by setting the PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY attribute. ''

The first thing that is odd is that I am not getting an error on my localhost (wampserver), but I am getting it on my web server. The php version on my localhost is 5.3.10, and on my web server it is 5.3.13.

I read that the source of this error is the request when the data remained in the buffer from the previous request. This does not apply to me - I tracked all the data, and I know that every row returned in the request is retrieved.

Having said that, I found that changing one of my requests to fetchAll instead of fetch fixes the problem, but it just doesn’t, since I know that all rows are returned. When I used fetchAll for the query (it runs in a loop), I printed an array every loop, and only one element was in the array for each query in the loop.

One more information. This is not a request that I changed to fetchAll (which makes the problem go away) that causes a PDO error, there is another request in my php file that throws an error. My file is basically like this:

 ... code ... query 1 ... code ... loop query 2 end loop ... code ... query 3 

If I comment out request 3, there will be no error. If I comment or change to fetchAll , request 2, there will be no error. request 1 does not affect anything.

I would also like to add that I tried to add LIMIT 1 to all requests on the page (at the same time), and the error is still there. I think this proves that there are no unread data in the buffer?

I am really confused, so I will be grateful for your advice. Before anyone asks, I cannot post the full code for this, but here is a simplified version of my code:

 $stmt = $this->db->prepare('SELECT ... :par LIMIT 1'); makeQuery($stmt, array(':par' => $var)); $row = $stmt->fetch(PDO::FETCH_ASSOC); $stmt = $this->db->prepare('SELECT ... :par LIMIT 1'); for loop makeQuery($stmt, array(':par' => $var)); $row2 = $stmt->fetch(PDO::FETCH_ASSOC); ... [use row2] ... end for loop $stmt = $this->db->prepare('SELECT ... :par LIMIT 1'); makeQuery($stmt, array(':par' => $var)); $row3 = $stmt->fetch(PDO::FETCH_ASSOC); 

Here is makeQuery() .

 /************************************************************************************************************** * Function: makeQuery * * Desc: Makes a PDO query. * * Pre conditions: The statement/query and an array of named parameters (may be empty) must be passed. * * Post conditions: The PDO query is executed. Exceptions are caught, displayed, and page execution stopped. * **************************************************************************************************************/ function makeQuery($stmt, $array, $errMsg = '') { try { $stmt->execute($array); } catch (PDOException $e) { print $errMsg != ''?$errMsg:"Error!: " . $e->getMessage() . "<br/>"; die(); } } 

Thanks for your help!

EDIT: I also tried the following after query 2 (since this seems to be the source of the problem:

 $row2 = $stmt->fetch(PDO::FETCH_ASSOC); var_dump($row2); 

The output was:

 bool(false) 

Am I stumbled upon a PDO error?

Answer 1

you need to retrieve until the row fetch attempt completes. I know that in the result set there can be only one row, and I think that one selection is enough, but it is not.

you probably have other statements in which you didn’t fully “choose until the error has completed.” Yes, I see that you have chosen, while the withdrawal did not work for one of the operators.

also see closecursor ()

 $sql = "select * from test.a limit 1"; $stmt = $dbh->prepare($sql); $stmt->execute(array()); $row = $stmt->fetch(); $stmt->closeCursor(); 

or

 $sql = "select * from test.a limit 1"; $stmt = $dbh->prepare($sql); $stmt->execute(array()); list($row) = $stmt->fetchAll(); //tricky 

Answer 2

After a long struggle with this problem, I finally found that it worked for me:

 $db = new PDO ($cnstring, $user, $pwd); $db->setAttribute (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);