Mysql select query not generating result on cron but is ok via terminal

Question

We have a C program (source is below). We compile it with:

gcc -Wall -Wextra -g $(mysql_config --cflags) get1ReceiveSource.c $(mysql_config --libs) -lrt -o get1Receive

The select give results perfectly when run on the terminal using ./get1Receive. The moment we move to cron job every minute using the cron entry

/1 * * * * /usr/local/bin/get1Receive >> /var/log/myalert1.log 2>&1

I just can't see that it gets any results. I have debuged using gdb and it works perfectly fine where I have set breakpoint and step through and I can see it goes well into the loop too. I have also attached the env value and I suspected that mysql is having issue there? Any idea please?

C Code:

#include <stdlib.h>
#include <unistd.h>
#include <pthread.h>
#include <stdio.h>
#include <time.h>
#include <sys/time.h>
#include <signal.h>
#include <mysql.h>
#include <string.h>
#include <syslog.h>

#define SMALLSIZE 20;

int main () {
    openlog("get1recieve",LOG_PID|LOG_PERROR,LOG_LOCAL0); 
    time_t now = 0; 
    time(&now); 
    syslog(LOG_INFO, "started at %s", ctime(&now));
    syslog (LOG_INFO, "Program started by User %d", getuid ()); 
    MYSQL *localConn;
    MYSQL_RES *localRes1;
    MYSQL_ROW localRow1;
    char *server = "localhost";
    char *user = "user1";
      char *password = "****"; 
      char *database = "test1";
      localConn = mysql_init(NULL);
      if (!mysql_real_connect(localConn, server,
             user, password, database, 0, NULL, 0)) {
         // fprintf(stderr, "%s\n", mysql_error(localConn));
          syslog(LOG_ERR, "failed to connect to MySQL : %s", mysql_error(localConn));
          exit(EXIT_FAILURE); 
          //exit(1);
      }

        struct timeval tv;
    char queryBuf1[512];
    char buff1[20] = {0};
    char buff2[20] = {0};
    gettimeofday (&tv, NULL);
    //fprintf (stderr, "[%d.%06d] Flag set to 1 on ", tv.tv_sec, tv.tv_usec);
    //tv.tv_sec -= 5;
    strftime(buff1, 20, "%Y-%m-%d %H:%M:00", localtime(&tv.tv_sec));
    strftime(buff2, 20, "%Y-%m-%d %H:%M:59", localtime(&tv.tv_sec));
    printf("\nTime from %s", buff1);
    printf("\nTime to %s", buff2);


    fflush(NULL);    
     if (snprintf(queryBuf1, sizeof queryBuf1,
        "SELECT ipDest, macDest, portDest, sum(totalBits)"
        " FROM dataReceive"
        " WHERE timeStampID between '%s' And '%s' "
        " GROUP BY ipDest, macDest, portDest ",
        buff1, buff2) >= (int) (sizeof queryBuf1))
     abort();

            syslog (LOG_INFO, "querybuf %s at line %d", queryBuf1, __LINE__);


            if(mysql_query(localConn, queryBuf1))
            {
                printf("Error in first query of select %s\n",mysql_error(localConn));
                exit(1);
            }
            localRes1 = mysql_use_result(localConn);
            //localRes1 = mysql_store_result(localConn);
            int num_fields = mysql_num_fields(localRes1);
            if (!localRes1)
            syslog(LOG_PERROR, "loop# mysql useresult failure %s",  mysql_error(localConn));
            printf("\nNumf of fields : %d",num_fields);
            printf("\nNof of row : %lld",mysql_num_rows(localRes1));

            while((localRow1 = mysql_fetch_row(localRes1)) !=NULL)
            {
              printf("TEST");

            } 
            fflush(NULL); 
              mysql_free_result(localRes1); 
        mysql_close(localConn);

       closelog ();        
      return 0;      

}

We have run this command file get1Receive and resulting to

file get1Receive
get1Receive: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

We have also run this command * * * * * set > /tmp/myvars and below is the results.

GROUPS=()
HOME=/root
HOSTNAME=capture
HOSTTYPE=x86_64
IFS='
'
LOGNAME=root
MACHTYPE=x86_64-redhat-linux-gnu
OPTERR=1
OPTIND=1
OSTYPE=linux-gnu
PATH=/usr/bin:/bin
POSIXLY_CORRECT=y
PPID=11086
PS4='+ '
PWD=/root
SHELL=/bin/sh
SHELLOPTS=braceexpand:hashall:interactive-comments:posix
SHLVL=1
TERM=dumb
UID=0
USER=root
_=/bin/sh

I have also seen something similar here ...not-run-in-the ?

Yes it shows up all the prints except for the Test in the while loop but on terminal it runs perfectly fine. — biz14, Mar 18 '13 at 13:25
So you get the number of fields and rows? If you add a row to print something if localRow1 == NULL? — NickW, Mar 18 '13 at 13:43
The issue is the number of fields is shown as 4 but this always is zero printf("\nNof of row : %lld",mysql_num_rows(localRes1)); and I have tried many things even tried to run it as a shell program also the same. I just dont understand what is causing the cron not to generate the query results. Nothing in the syslog could help either. — biz14, Mar 18 '13 at 13:48
Freaky, the query you're running prints out fine as well? Are the %s values for the between part of your query resolving properly? — NickW, Mar 18 '13 at 13:53
@NickW yes I have confirmed the between values are working in cron no problem of that just that no query results what could be the funny thing happening here? — biz14, Mar 18 '13 at 13:59

score 1 · Accepted Answer · answered Mar 18 '13 at 14:36

1

Could you try:

/1 * * * * /bin/bash -c '/usr/local/bin/get1Receive >> /var/log/myalert1.log 2>&1'

See if it works?

answered Mar 18 '13 at 14:36

ctrlaltreboot

26
1

@crtlaltreboot it is still the same no changes. – biz14 Mar 18 '13 at 14:57

Mysql select query not generating result on cron but is ok via terminal

1 Answers1