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:

 *	Free all resultsets from $dbCon.
 *	@param		mysqli		$dbCon	mysqli object.
 *	@return		void
function free_all_results(mysqli $dbCon)
    do {
        if ($res = $dbCon->store_result()) {
    } 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 = '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.


Leave a comment

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

You are commenting using your 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