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?
rch1231 169 Posting Shark
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.
CimmerianX 197 Junior Poster
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.
newbie14 0 Posting Pro
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.
rch1231 169 Posting Shark
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
Edited by rch1231
newbie14 0 Posting Pro
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.
rch1231 169 Posting Shark
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;
}
`
newbie14 0 Posting Pro
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.
rubberman 1,355 Nearly a Posting Virtuoso Featured Poster
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.
newbie14 0 Posting Pro
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?
rch1231 169 Posting Shark
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.
newbie14 0 Posting Pro
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.