Member Avatar for 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);
  • 3 Contributors
  • 4 Replies
  • 250 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by newbie14

Dani AI

Generated

Brief diagnosis: the MySQL C API error "Commands out of sync; you can't run this command now" means you tried to run a new command on a connection that still has an open/unfetched result set or the same MYSQL* is being used concurrently. In this thread reports the SELECT/stream and INSERT happening close together — if the SELECT connection still has an unread server-side result (or the same connection pointer is used by both operations or by overlapping timer callbacks), the client/server protocol will refuse the next command.

Concrete fixes (pick one or combine):

  • Buffer the SELECT result on the client so the server-side result is closed before you issue other queries on that connection. Example pattern:

    MYSQL_RES *res = mysql_store_result(remoteConn);
if (!res) { /* handle error */ }
while ((row = mysql_fetch_row(res)) != NULL) { /* process row */ }
mysql_free_result(res);

  • Use two distinct connections: one dedicated to the streaming SELECT and another for INSERTs. Make sure remoteConn and localConn are separate MYSQL* handles (call mysql_init/mysql_real_connect twice). That lets you stream with a server-side cursor on one handle and insert on the other safely.

  • Prevent re‑entry: ensure the per‑second callback cannot start a new run while the previous is still processing. Use a worker thread/queue or a simple in-function flag/mutex so the timer won’t overlap runs.

Transactions and the C API: yes — transactions are supported, but they solve atomicity, not the out‑of‑sync protocol issue. For batching inserts you can turn off autocommit and commit once:

mysql_autocommit(localConn, 0);
/* perform multiple inserts */
mysql_commit(localConn);
mysql_autocommit(localConn, 1);

Troubleshooting checklist: verify pointer identities (print addresses of remoteConn/localConn), check mysql_errno()/mysql_error(), ensure you fetch/free every result, avoid sharing a MYSQL* across threads or protect it with a mutex, and consider prepared statements + batched inserts for performance. As noted, transactions help consistency; as observed, slowing the timer may mask the issue but doesn’t fix connection/result handling.

Recommended Answers

All 4 Replies

Member Avatar for smantscheff

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

Member Avatar for 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?

Member Avatar for Ancient Dragon

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

Member Avatar for newbie14

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.