Mysql Commands out of Sync; you can’t run this command now

Question

I have a class Import (PHP) which is used to read XML files and insert into the BD.

First I got the class builder Import that creates a new connection mysqli:

...
$this->mysqli = new mysqli( HOST, USER_NAME, PASSWORD, DATA_BASE );
mysqli_set_charset( $this->mysqli, CHARSET ) or die ( "ERROR: Connection fail!" );
...

In my reading function (class Import) I have this code:

...
$elems = $this->dom->getElementsByTagName( TAG_NAME );
foreach ( $elems as $elem )
{
   $elem1 = $elem->getElementsByTagName( TAG_ELEM1 );
   $elem2 = $elem->getElementsByTagName( TAG_ELEM2 );
   $elem3 = $elem->getElementsByTagName( TAG_ELEM3 );

   $sql2 = "SELECT `elem4` FROM `other` WHERE `elem3`=?";
   $stmt2 = $this->mysqli->prepare( $sql2 ) ; //ERRO AQUI
   $stmt2->bind_param( "i", $elem3 );
   $stmt2->execute( );
   $stmt2->bind_result($elem4);
   $stmt2->store_result( );

   $sql = "INSERT INTO `table`( `elem1`, `elem2`,  `elem4` ) VALUES ( ?, ?, ?)";
   $stmt = $this->mysqli->prepare( $sql ) ;
   $stmt->bind_param( "iss", $elem1, $elem2, $elem4 );
   $stmt->execute( );
   $stmt->store_result( );
}
...

When executing I have this error (flagged in the code):

(2014) Commands out of Sync; you can’t run this command now

I’ve read some things in English and supposedly the $stmt->store_result( ); would solve that mistake, but that does not happen.

Note: Tables are being created before reading: $this->mysqli->multi_query( $sql_create_all )

Any idea what it will be?

Answer 1

From what I saw about the mistake in documentation and in the SOEN, the lib mysqli cannot maintain two queries results (result set) open at the same time. Each time a query is executed, it is as if a cursor exists in the database pointing to the next line that will be read from result set.

As mentioned in the question, some people claim that it is enough to call the store_result(). However, reading the documentation, it seems that this method needs to be called before the bind_result(), especially if there are fields BLOB or LONGTEXT involved.

Moreover, second a comment of the documentation, the solution is, in addition to closing the result set with close(), execute a next_result(). Example:

$result = $db->query("call getUsers()");
if($result){
     // Cycle through results
    while ($row = $result->fetch_object()){
        $user_arr[] = $row;
    }
    // Free result set
    $result->close();
    $db->next_result();
}

---

Updating

As mentioned in the comments, since the problem occurs already in the first iteration and there are queries being executed before the loop, then the error arose because these queries were not correctly finalized. This is because even the creation of tables seems to return some kind of result, although do not understand Mysql depth to explain the reason.

That is why it is necessary to free the resources by reading all the returns of the queries executed using $result->free() as explaining in quoted example of the documentation and also in some comments of the documentation, as the example cited by the OP:

while ($this->mysqli->next_result()) {
    if ($result = $this->mysqli->store_result()) {
        $result->free();
    }
}

Answer 2

This error is due to pending commit commands in the database. It is generally good to use the connection and close it right after.