MySQL CR_COMMANDS_OUT_OF_SYNC

mysql_error: commands out of sync; you can't run this command now.
   why:
   after you used 'mysql_use_result', but didn't finish it with 
      'mysql_fetch_row' until NULL.
   after you got multiple result set, but didn't finish them with
      'mysql_next_result' until >0 or -1.
   after 'mysql_stmt_execute' which generated result set, but you didn't 
      finish it with 'mysql_stmt_fetch'.
   after 'mysql_stmt_execute' which generated multiple result sets, but you
      didn't finish them with 'mysql_stmt_next_result'.
   so:
      when you execute multiple statements with 'select' or 'call' always check
      for more possible result sets with 'mysql_more_result' or
      'mysql_next_result'.
      if you use 'mysql_use_result' after query, call 'mysql_fetch_row' until it

      returns NULL, then 'mysql_free_result'.

/*execute a multi-query to check its return types.
 *
 * result: as said by manual: after each mysql_next_result, the state is just like after mysql_query.
 *       you can call mysql_store_result, mysql_affected_rows, mysql_warnings_count.
 *       so mysql_next_result is as its name suggets get the next result(result stauts or result set).*/
#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<mysql/mysql.h>
#include"error_handler.h"

int main(){
   MYSQL mysql,*mptr;
   mptr=mysql_init(&mysql);
   const char* sqlstr=
      "create table tbl_test(id int);"
      "insert into tbl_test values(1),(2),(3),(4);"
      "update tbl_test set id=id*2;"
      "select id from tbl_test;"
      "drop table tbl_test";     //no ending ';'
   int ires;
   if(mptr==NULL){
      fputs("error: mysql_init\n",stderr);
      exit(1);
   }
   mptr=mysql_real_connect(mptr,"localhost","zdave","****","test",
         0,NULL,CLIENT_MULTI_STATEMENTS);
   if(mptr==NULL) SQLERR_HANDLER(&mysql,"mysql_real_connect:");

   ires=mysql_real_query(mptr,sqlstr,strlen(sqlstr));
   if(ires)SQLERR_HANDLER(mptr,"mysql_real_query:");
   /*
    * I think mysql return one result(not necessarily resultset) for each statement.
    * some are just status. some are result set.*/
   do{
      MYSQL_RES *resptr=mysql_store_result(mptr);
      //is a result set returned?
      if(resptr==NULL){
         //no resultset.error?
         ires=mysql_field_count(mptr);
         if(ires){
            //indeed error occured
            SQLERR_HANDLER(mptr,"mysql_next_result:");
         }else{
            //no error. there is just no resultset. so get the result status.
            printf("affected arrows:%d\n",mysql_affected_rows(mptr));
         }
      }else{
         //process the resultset.
         MYSQL_ROW row;
         unsigned int fieldnum=mysql_num_fields(resptr);
         MYSQL_FIELD *fieldptr=mysql_fetch_fields(resptr);
         for(int i=0;i<fieldnum;i++){
            printf("%s\t",fieldptr[i].name);
         }
         putchar('\n');
         while(row=mysql_fetch_row(resptr)){
            for(int i=0;i<fieldnum;i++){
               printf("%s\t",row[i]);
            }
            putchar('\n');
         }
         mysql_free_result(resptr);
      }
   }while(!(ires=mysql_next_result(mptr)));
   if(ires>0) SQLERR_HANDLER(mptr,"mysql_next_result:");
   mysql_close(mptr);
   return 0;
}