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;}