Today I was programming MySQL via PHP and I received an error “Error in SQL: Commands out of sync; you can’t run this command now”. It turned out the problem was that a previous mulit_query had mysqli_results that hadn’t been freed. This article helped me solve the problem, and now my batch mode SQL processor looks like this:

  public function execute_batch( $sql ) {

    $this->write_count++;

    if ( ! $this->link->multi_query( $sql ) ) {

      $this->throw_error( $sql );

    }

    if ( $this->link->more_results() ) {

      do {
        $result = $this->link->use_result();
        if ( $result instanceof mysqli_result ) {
          $result->free();
        }
      }
      while ( $this->link->more_results() && $this->link->next_result() );

    }
  }

This code executes the SQL batch and then frees any mysqli_results that result from the query batch.