PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.

[DarioVanin]

Hi Guys,

I am stuck with an issue that prevents me from executing two queries in a row on my mysql database using PDO. The database works perfectly when I use mysqli, so the problem must be on my php.ini/code configuration.

This is the code:

function connect_pdo_db()
{
    global $pdodb;
    if (isset($pdodb) ) {
        return;
    }
    $host = 'myhost';
    $db   = 'mydbname';
    $user = 'myuser';
    $pass = 'mypassword';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
        PDO::ATTR_PERSISTENT         => true,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
    ];
    try {
         $pdodb = new PDO($dsn, $user, $pass, $options);
    } catch (\PDOException $e) {
         throw new \PDOException($e->getMessage(), (int)$e->getCode());
    }
}
global $pdodb;
connect_pdo_db();

$query = "CALL my_stored_procedure1(:user_login);";
$sth = $pdodb->prepare($query);
$sth->bindParam(':user_login', $user_login);
$sth->execute();
$output1 = $sth->fetchAll();

$query = "CALL my_stored_procedure2(:user_login);";
$sth = $pdodb->prepare($query);
$sth->bindParam(':user_login', $user_login);
$sth->execute();
$output2 = $sth->fetchAll();

If I run a single query without the last 5 lines of code, it works fine.

I tried wih the paramenter MYSQL_ATTR_USE_BUFFERED_QUERY both set to "true" and "false" with the same results. 

2022/12/03 09:21:40 [error] 32#32: *2 FastCGI sent in stderr: "PHP message: PHP Fatal error:  Uncaught PDOException: 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.

The extension on my php.ini is active

extension=pdo_mysql

Has any of you had the same problem? Can you please help me find a solution?

Thanks,

Dario Vanin

[DarioVanin]

problem solved. I had to upgrade from php7 to php8 and add 

$sth->closeCursor();

after

$sth->fetchAll();

I also vhanged the optinos as below

      PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES   => false,