Unjustified error: "Cannot execute queries while other unbuffered queries are active"

Question

The following simplified code produces the error named in the title:

#!/usr/bin/env php
<?php


$ini['OVERALL']['Days to keep'] = 16 ;

if ( ! mysqlIsRunning()) {
    die("It seems that mysql is not running on this system.  Cannot continue.\n") ;
    }
    
if ( (! $ini) || (! is_array($ini)) || (! isset($ini['OVERALL']))) {
    die("INI file not correctly parsed - must at least include OVERALL section.\n") ;
    }


$daysToKeep = (isset($ini['OVERALL']['Days to keep']) ? $ini['OVERALL']['Days to keep'] : 15 ) ;

$purgeDate = Date('Y-m-d', strtotime('Today - ' . $daysToKeep . ' days')) ;
$sql = <<<EOS
            PURGE BINARY LOGS
            BEFORE $purgeDate
            EOS ;

$server = (($host = getenv('MYSQL_HOST')) ? $host : 'LOCALHOST') ;
$user   = (($u    = getenv('MYSQL_USER')) ? $u : $getenv('LOGNAME')) ;
$pwd    = getenv('MYSQL_PASSWORD') ;
$dbname = (($db   = getenv('MYSQL_DBNAME')) ? $db : $user) ;
$timeout = (($to  = getenv('MYSQL_TIMEOUT')) ? $to : 30) ;
$dsn = "mysql:host=" . $server. ";dbname=" . $dbname ;

$options = [ PDO::ATTR_EMULATE_PREPARES   => false // no emulation mode for "real" prepared stmts
          ,  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION // Errors cause exception
          ,  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // Default fetch mode
          ,  PDO::MYSQL_ATTR_FOUND_ROWS   => true
          ,  PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
          ,  PDO::ATTR_TIMEOUT            =>$timeout // Timeout in n seconds (may/not be supported by MySQL ??)
              ] ;
try {
    $pdo = new PDO($dsn, $user, $pwd, $options) ;
    }
catch (Exception $e) {
    die("Errors occurred when trying to connect to mysql service '$user@$server:$dbname'\n") ;
    }
$stmt = $pdo->prepare($sql) ;
$ok = $stmt->execute() ;
if ($ok)
    printf("Binary logs purged before %s\n", $purgeDate) ;
else
    die(sprintf("Failed to purge binary logs before %s", $purgeDate)) ;
exit(0) ;


function mysqlIsRunning() : bool {
    if (is_executable('/etc/init.d/mysql')) {
        $output = [] ;
        exec('/etc/init.d/mysql status 2>&1', $output) ;
        foreach ($output as $line) {
            if (preg_match('%\s*Active: active [(]running[)]%', $line))
                return true ;
            }
        }
    return false ;
    }

To test on your own system(requires a standard mysql/mariadb installation on Linux. (To run on Windows, the call to mysqlIsRunning() could be replaced with something like if (true) .

> export MYSQL_HOST=localhost
> export MYSQL_USER=<username>
> export MYSQL_PASSWORD=<password>
> export MYSQL_DATABASE=<database>
> run the script

The title error occurs on the statement >>> $ok = $stmt->execute() ;

There are at least two reasons this error seems out of place. First, because there have been no queries executed at all by this code. Second, because the option MYSQL_ATTR_USE_BUFFERED_QUERY is set, in accordance with the resolution indicated in the error message.

NOTE: As is often the case for code submitted here, this is a simplification of a much more complex script. I've done my best to reduce it to only what matters for the problem. Superfluous code you may see, probably has good reason to be there in the original.

php -v PHP 7.4.3 (cli) (built: Nov 2 2022 09:53:44) ( NTS ) Copyright (c) The PHP Group Zend Engine v3.4.0, Copyright (c) Zend Technologies with Zend OPcache v7.4.3, Copyright (c), by Zend Technologies with Xdebug v3.1.3, Copyright (c) 2002-2022, by Derick Rethans $ uname -a Linux velmicro 5.15.0-58-generic #64~20.04.1-Ubuntu SMP Fri Jan 6 16:42:31 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

Answer 1

You are most likely running into a bug that has been fixed in PHP 7.4.14. You should try with the most recent 7.4 patch version. However, since PHP 7 hasn't been supported for some time, it would be best if you upgraded to 8.2.