MariaDB/MySQL: “Commands out of sync” error (2014)

Versione italiana

If you execute mysqli_multi() and later you try to execute mysqli_query() or mysqli_result(), you may get the infamous error 2014: Commands out of sync; you can't run this command now (SQLSTATE: HY000). This happens because you didn’t free all resultsets before executing another query. But probably, you didn’t even know that any resultset exists, because you executed statements like INSERT, DELETE, or DDL.

Well, here is a function which frees all resultsets, and an usage example:

<?php
    
/**
 *	Free all resultsets from $dbCon.
 *	@param		mysqli		$dbCon	mysqli object.
 *	@return		void
 */
function free_all_results(mysqli $dbCon)
{
    do {
        if ($res = $dbCon->store_result()) {
            $res->fetch_all(MYSQLI_ASSOC);
            $res->free();
        }
    } while ($dbCon->more_results() && $dbCon->next_result());
}
    
$db = new mysqli('...', '...', '...', '...');
    
$sql = <<<SQL
    DROP TABLE IF EXISTS `test`.`tab1`;
    CREATE TABLE `test`.`tab1` (`col` INT);
    INSERT INTO `test`.`tab1` VALUE (1);
SQL;
$db->multi_query($sql);
    
free_all_results($db);
    
$sql = 'SELECT * FROM `test`.`tab1`;';
$res = $db->query($sql);
    
if ($db->sqlstate !== '00000') {
    echo 'SQLSTATE: ' . $db->sqlstate . '; Error: ' . $db->errno . ' - ' . $db->error;
} else {
    echo $res->num_rows;
}
    
?>

If you comment the free_all_results($db); line, you will see the error.

Enjoy!

About these ads

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s