1

In PHP, when I call a MySQL stored procedure using PDO, and then another PDO query, just like this:

$dbh = new PDO('mysql:host=localhost;dbname=db1','user1','password1');

$query = "CALL get_token()";
$stmt = $dbh->query($query);
$array = $stmt->fetchAll();

$query = "SELECT * FROM `table1`";
$stmt = $dbh->query($query);
$array = $stmt->fetchAll();

The MySQL stored procedure is about like this:

CREATE PROCEDURE `get_token`()
BEGIN
    DECLARE token CHAR(64);
    DECLARE expire SMALLINT;

    SELECT `token`, `expire` INTO token, expire FROM `token`;

    SELECT token, expire;
END$$

And I got the following error message (using try...catch to catch it):

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.

Even if I followed the instructions described in the above error message (that means using fetchAll() and setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute), I still got the same error message.

If I change the first query to a normal SELECT SQL query, instead of a stored procedure, I won't get this error. So it seems that the problem arises from the stored procedure.

But how can I fix this?

Benson
  • 115
  • 1
  • 7
  • 1
    Did you try doing what's suggested in error message? – Mark Aug 01 '17 at 07:59
  • I followed the two suggestions in the error message, but they both didn't work. :-( @Mark – Benson Aug 01 '17 at 08:10
  • I've tried two methods mentioned in the post you recommended to me, that is using `$stmt->closeCursor()` or `unset($stmt)`. But unfortunately, they both didn't work for me. :-( @jiboulex – Benson Aug 01 '17 at 08:20

2 Answers2

4

That's because you are not freeing the cursor of the first query. It still waits for another fetchAll. From http://php.net/manual/en/pdo.query.php

If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().

So $stmt->closeCursor(); after first $array = $stmt->fetchAll(); should be sufficient.

Mark
  • 1,357
  • 16
  • 30
  • Thank you so much! I've tried this, but still got the same error message. `$dbh->closeCursor();` seems should be `$stmt->closeCursor();`. I've tried the latter one, but the problem still exists. :-( @Mark – Benson Aug 01 '17 at 08:23
  • 1
    Technically `fetchAll()` ends the buffer but doesn't close the cursor, so in theory you wouldn't need a `closeCursor()` to prevent buffer errors – DarkMukke Aug 01 '17 at 08:23
  • Then why did I got this error, since I've used fetchAll(), and even used `$stmt->closeCursor;` as well? @DarkMukke – Benson Aug 01 '17 at 08:27
  • 1
    See my answer below – DarkMukke Aug 01 '17 at 08:28
  • 1
    Yes, thank you! `$stmt->closeCursor();` does work. The problem is solved now. – Benson Aug 01 '17 at 08:53
1

You should use either PDO::MYSQL_ATTR_USE_BUFFERED_QUERY OR fetchAll() as they conflict

According to the documentation :

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (integer) If this attribute is set to TRUE on a PDOStatement, the MySQL driver will use the buffered versions of the MySQL API. If you're writing portable code, you should use PDOStatement::fetchAll() instead.

In other words, fetchAll() tries to close the buffer but PDO::MYSQL_ATTR_USE_BUFFERED_QUERY keeps it open

DarkMukke
  • 2,469
  • 1
  • 23
  • 31
  • I've tried the both. And even more used `$stmt->closeCursor();`, and used `unset($stmt);` as well. But all of them didn't solve my problem. It's still the same. – Benson Aug 01 '17 at 08:30
  • 1
    `unset($stmt);` will never work because the buffer is in the mysql api, the only way that would work is if you close the session too (eg in php open a new connection) – DarkMukke Aug 01 '17 at 08:33
  • Yes, thank you very much! `$stmt->closeCursor();` does work. The problem is solved now. – Benson Aug 01 '17 at 08:54