Stack Exchange
C running query shows command out of sync?
We are running a C program where for every one second there is a function callback. Below is the snippet of the code listed:
char timeBuf[10],secondBuf1[100],queryBuf1[500],queryBuf2[500];
char buff[20] = {0};
struct timeval tv;
gettimeofday (&tv, NULL);
tv.tv_sec -= 5;
strftime(buff, 20, "%Y-%m-%d %H:%M:%S", localtime(&tv.tv_sec));
printf("\nTime is %s", buff);

sprintf(secondBuf1,"INSERT INTO  secondsLog2 (secondLogID , timeStampID ) VALUES (NULL,'%s')",buff);
//printf("Query 1 before executing %s\n",queryBuf1);
if (mysql_query(localConn, secondBuf1)) 
{
    printf("Error in insert of seconds log %s\n",mysql_error(localConn));
    exit(1);
}

sprintf(queryBuf1,"SELECT ipDest, portDest, SUM(packetLen), COUNT(ipDest) FROM source1 WHERE timeStamp = '%s' GROUP BY portDest",buff);
printf("\nQuery buf %s",queryBuf1);
if(mysql_query(remoteConn, queryBuf1))
{
    printf("Error in first query of select %s\n",mysql_error(remoteConn));
    exit(1);
}
localRes1 = mysql_use_result(remoteConn);
while((localRow1 = mysql_fetch_row(localRes1)) !=NULL)
            {
              sprintf(queryBuf1,"INSERT INTO  export1 (iBTID ,timeStampID ,ipDest ,portDest,totalBits, packetCount) VALUES (NULL,'%s','%s','%s',%s,%s)",buff, localRow1[0],localRow1[1],localRow1[2],localRow1[3],localRow1[4]);
              printf("Query 1 before executing %s\n",queryBuf1);
              if (mysql_query(localConn, queryBuf1)) 
              {
                printf("Error in first query of insert %s\n",mysql_error(localConn));
                 exit(1);
              }

            }
                        mysql_free_result(localRes1);

The moment I run this script the second SELECT will give me this error: Commands out of sync; you can't run this command now:
Time is 2012-07-17 00:59:14
Query buf SELECT ipDest, portDest, SUM(packetLen), COUNT(ipDest) FROM source1 WHERE timeStamp = '2012-07-17 00:59:14' GROUP BY portDest
Time is 2012-07-17 00:59:15
Query buf SELECT ipDest, portDest, SUM(packetLen), COUNT(ipDest) FROM source1 WHERE timeStamp = '2012-07-17 00:59:15' GROUP BY portDestError in first query of select Commands out of sync; you can't run this command now


You need to clear out your result set on any queries that will return results. If you're not using the results then just call:
MYSQL_RES *results;
results = mysql_store_result(localConn);
mysql_free_result(results);

To use your results just call mysql_store_result (or mysql_use_result) after queries that return results and be sure to use mysql_free_result on them at some point later. This should clear up any problems with CR_COMMANDS_OUT_OF_SYNC errors.
From the documentation for mysql_store_result (emphasis added):

After invoking mysql_query() or mysql_real_query(), you must call mysql_store_result() or mysql_use_result() for every statement that successfully produces a result set (SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, and so forth). You must also call mysql_free_result() after you are done with the result set. 


I have updated my question with the insert query which is using the results from the previous select query and infact I do have this mysql_free_result(localRes1); but after the insert query. So now I should free it before the insert query then how am I going to use the results for the insert query?

I have tried this localRes2 = localRes1; before freeing the localRes1 but nothing is showing up in the insert statement?

so what is best work around for my insert to get the results also to make sure my select does not stuck again. I do have mysql_store_result(remoteConn); ? So what is the mistake then?

does your pasted output still match the updated code? Why is the printf for e.g. Query 1 before executing missing?

I have updated my question ready what I did was just replace this localRes1 = mysql_store_result(remoteConn); with localRes1 = mysql_use_result(remoteConn);. Finally only I free mysql_free_result(localRes1); after the insert statement but now it run for a while then get into this error "Error in insert of seconds log Lost connection to MySQL server during query". Is it due to the too many queries on seconds basis?

Again I tend to get "Commands out of sync; you can't run this command now" after running it some time?

I think I need to restructure how I do the my select and insert is it? The queries are coming non stop. What is your idea here?
