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!

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

  1. If you get this message when you try to work a Mariadb database via Mariadb ODBC driver you must change to MySql 5.1 ODBC driver.

    • You experienced this recently? I wrote this post in 2013, and I completely forgot the context. But freeing the resultsets before executing another query seems to me reasonable, in most cases.

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