Adding trigger to MySQL by PHP

Question

I use PhpMyAdmin to run the following SQL statement.

CREATE TRIGGER `insert_channel_test2` AFTER DELETE ON `channel_test2` FOR EACH ROW INSERT INTO `tv`.`LOG_test2` (`table_name`, `table_action`) VALUES('channel_test2', 'delete');

It works successful, but when I use the PHP webpage to query above code, it occurs Fatal error.

$query = "CREATE TRIGGER `insert_channel_test2` AFTER DELETE ON `channel_test2` FOR EACH ROW INSERT INTO `tv`.`LOG_test2` (`table_name`, `table_action`) VALUES('channel_test2', 'delete');"
$this->link = new mysqli( DB_HOST, DB_USER, DB_PASS, DB_NAME );
$full_query = $this->link->multi_query( $query );

And the error messages is following.

 Commands out of sync; you can't run this command now

Does everyone knows what the problem is? Thanks!

Permission error perhaps, try actually printing the error message.. — hank, Dec 17 '13 at 09:10
What is the actual error from mysqli that you can get with [`mysqli_error`](http://www.php.net/manual/en/mysqli.error.php)? What you posted is your custom error message, that shows the time and the query which is nine, but doesn't contain the real error message from MySQL. — peterm, Dec 17 '13 at 09:19
sorry, the following error message is produced by `mysqli_error`. **Commands out of sync; you can't run this command now** — Andy, Dec 17 '13 at 09:22

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

As per MySQL:

C.5.2.14. Commands out of sync If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

Refer to:

MySQL: Commands out of sync
One of my answers to a similar query on SO: getting error in calling stored procedure

score 1 · Answer 2 · answered Dec 17 '13 at 09:58

Thanks Everybody. I modified my code to following, and fix the problem.

public function multi_query( $query )
{
    $query = $this->link->multi_query( $query );
    $this->clear_next();
    if(  mysqli_error( $this->link ) )
    {
        $this->log_db_errors( mysqli_error( $this->link ), $query, 'Fatal' );
        return false;
    }
    else
    {
        return true;
    }
    mysqli_free_result( $query );
} 

public function clear_next()
{
    while($this->link->more_results())
    {
        $this->link->next_result();
        if($res = $this->link->store_result())
        {
            $res->free(); 
        }
    }
}

Adding trigger to MySQL by PHP

2 Answers2