We have a simple query and insert using C code linking to mysql db. The issue now on itself the script works fine. But when we put to run as a cron job the select query gives empty results and when we run back on the db there is results. It really puzzle as to what extra should be done for it to a cron job?

Hello,

It is hard to tell what is wrong without seeing the cron entry you ae using or the code. However the first things that come to mind are that cron jobs are not run with the same environment variables as a normal user. You have to call the program using the absolute path of the file you are trying to run (i.e. /home/me/myscript ) and if there are any variables set that are specific to you login they need to be set when you script runs. You could try checking the cron logs and mysql logs to see if they show any errors.

Right, I was thinking 'permissions'. But rch1231 is correct. It's more likely the environment variables.

If you could post the cron entry and any important errors in the logs, that would be a big help.

dear all, below is my c codes.

MYSQL *localConn;
    MYSQL_RES *localRes1;
    MYSQL_ROW localRow1;
    timer_t tid = 0;
    pthread_t thread;
    struct itimerspec it;
    char *server = "localhost";
    char *user = "user1";
      char *password = "*******"; /* set me first */
      char *database = "packets";
      localConn = mysql_init(NULL);
      if (!mysql_real_connect(localConn, server,
             user, password, database, 0, NULL, 0)) {
          fprintf(stderr, "%s\n", mysql_error(localConn));
          exit(1);
      }

        struct timeval tv;
            char timeBuf[10],secondBuf1[100],queryBuf1[500],queryBuf2[500];
            char buff1[20] = {0};
            char buff2[20] = {0};
            gettimeofday (&tv, NULL);
            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);





          sprintf(queryBuf1,"SELECT field1, field2,field3, sum(totalCount) FROM tblR1 WHERE timeStamp between '%s' And '%s' ",buff1,buff2);
                printf("\nQuery receive %s",queryBuf1);


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

                localRes1 = mysql_store_result(localConn);
                int num_fields = mysql_num_fields(localRes1);

                printf("\nNumf of fields : %d",num_fields);
                printf("\nNof of row : %lu",mysql_num_rows(localRes1));

                //output table name
                //printf("Mysql Tables in mysql database: \n");
                while((localRow1 = mysql_fetch_row(localRes1)) !=NULL)
                {
                  int totalBits = atoi(localRow1[3]);

                  printf("totalBits %d\n", totalBits);
                  printf("RECEIVE %s,%s\n", localRow1[0], localRow1[1]);
                  if(totalBits>5000)
                  {
                    sprintf(queryBuf1,"INSERT INTO tblTR1 (timeStamp,field1, field2, field3, field4)VALUES ('%s','%s','%s','%s',%s)",buff1, localRow1[0],localRow1[1],localRow1[2],localRow1[3]);
                    printf("Query 1 before executing %s\n",queryBuf1);
                    if (mysql_real_query(localConn,queryBuf1,strlen(queryBuf1))) {
                   printf("Error in first query of select %s\n",mysql_error(localConn));
                   fprintf(stderr, "%s\n", mysql_error(localConn));
                   exit(1);
                  }
                    //printf("Query 1 after executing %s\n",queryBuf1);*/


                  } 


                } 
mysql_free_result(localRes1); 
           mysql_close(localConn);

Cron settings and the receive1.c is the output file not the source file. It is already compiled.

/1 * * * * /home/test1/userland/examples/receive1.c  2>&1 >> /var/log/myjob.log

My env variables are as below.

Output from env.

HOSTNAME=test1
TERM=xterm
SHELL=/bin/bash
HISTSIZE=1000
SSH_CLIENT=60.50.38.121 4383 22
QTDIR=/usr/lib64/qt-3.3
OLDPWD=/
QTINC=/usr/lib64/qt-3.3/include
SSH_TTY=/dev/pts/0
USER=root
LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.tbz=01;31:*.tbz2=01;31:*.bz=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36:
MAIL=/var/spool/mail/root
PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
PWD=/tmp
LANG=en_US.UTF-8
MODULEPATH=/usr/share/Modules/modulefiles:/etc/modulefiles
LOADEDMODULES=
HISTCONTROL=ignoredups
SHLVL=1
HOME=/root
LOGNAME=root
QTLIB=/usr/lib64/qt-3.3/lib
CVS_RSH=ssh
SSH_CONNECTION=60.50.38.121 4383 10.207.160.52 22
MODULESHOME=/usr/share/Modules
LESSOPEN=|/usr/bin/lesspipe.sh %s
G_BROKEN_FILENAMES=1
module=() {  eval `/usr/bin/modulecmd bash $*`
}
_=/bin/env

Then I run a crontab * * * * * env > /tmp/env.out and below is the output.

SHELL=/bin/sh 
USER=root 
PATH=/usr/bin:/bin 
PWD=/root 
SHLVL=1 
HOME=/root 
LOGNAME=root _=/usr/bin/env a

So what is the possible problem because if you see the path are showing difference.

Hello,

I don't know enough about C programming to be able to tell you what to do there but I suggest that you try putting your variables into a shell script file with the c program called at the very end and call that program instead of the c file:

#!/bin/bash
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
export TERM=xterm
/home/test1/userland/examples/receive1.c

Dear rch1231, I tried it via shell script still the same issue the C script runs and when comes to the results part it show empty. Then when I just run ./firsrule.sh it works perfectly fine and able to generate results. I do know why when it runs via cron is where the problem.

Hello,

Ok so how does you program normally return the results? If it normally displays to the screen then you need to redirect the output to a file either from within your C code or by redirecting STDOUT in the shell script:

/home/test1/userland/examples/receive1.c > /home/test1/userland/examples/output

I code alot with perl and when I am trying to save the results from a query or making changes to a file, I open a file for output and then print the results to the file a line at a time. I know it is not C (sorry I don't know C) however here is a piece of perl code to read a multiline record and export it as a one line csv.

`my $delim = "',";

### converts a multi-line record into a single line CSV file.

my $file = "$ARGV[0]";
open OUTGOING, ">", "$file" . ".csv";
my $lineone = 0;
open(FILE,"<$file.txt") or die "ERROR $file: $!\n";
while(<FILE>) {
    chomp;
    my $stuff = $_;
    my $firstslash = index( $stuff , "=");
    my $fieldname = substr $stuff , 4 , $firstslash  ;
    my $fielddata = substr $stuff , $firstslash +2  , 1000 ;
if ($lineone == 1) {
    if ( /EBOM_Item/  ) {
        print OUTGOING "\n";
        }
    else { print OUTGOING "'$fielddata$delim" ;
        }
    }
    $lineone = 1;
}
`

Normally I just printf and it gets written to the log /var/log/myjob.log file when I run via cron. The funny part when the select is run via cron it returns empty results and when I run the shell script it works perfectly fine with the relevant results generated via the select statement.

This is not a C code problem. It is most likely a misconfigured cronjob problem. Show the crontab please, as well as user ID for interactive use.

Here is the crontab setting /1 * * * * /home/test1/userland/examples/receive1.c 2>&1 >> /var/log/myjob.log dont worry about the receive1.c name is a compiled version of the source codes. The user is root to avoid any permission problems. What could be wrong ya?

Hello,

Well the only other thing I can think of is that cron runs without a terminal type being set. If your script needs a terminal type set to format the output then add the line:

export TERM=xterm

To the shell script you call the C code from.

The export is already from the time I build the shell script as per your suggestion.

#!/bin/bash
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
export TERM=xterm
/home/test1/userland/examples/receive1.c
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.