Mysql C running select every second give Commands out of sync; you can't ru

[newbie14]

We are running a C program where for every one second there is a function callback. Below is the snippet of the code listed. It is ran over every second without missing so we notice sometimes when is busy doing the insert part and the next select is up and running and cause this error. How to overcome this problem we have tried to copy the main localRes1 into another localRes2 and then try to free it but also got into errors.

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

[smantscheff]

Your post is not clear. Which error occurs when? To get a synchronous insert and update behaviour, use transactions to bracket all statements.

[newbie14]

Dear smantscheff I get error most often is here.

if(mysql_query(remoteConn, queryBuf1))
{     printf("Error in first query of select %s\n",mysql_error(remoteConn));
    exit(1);
}

or here too

if (mysql_query(localConn, queryBuf1)) 
               {
                 printf("Error in first query of insert %s\n",mysql_error(localConn));
                  exit(1);
               }

The error message I get is Commands out of sync; you can't run this command now? So where do I put the transaction and can C library for mysql support transaction? Any example for that?

[Ancient Dragon]

maybe once a second is too frequent -- slow it down to once every 15 seconds and see if it still gives errors.

[newbie14]

@ancientdragon I wish to slow it down but the problem then it defeat the application's objective to capture data at every second and process it accordingly. So what is the best mechanism I have tried using transaction also the same too.