Script runs from command line but not via crontab

ericlee has asked for the wisdom of the Perl Monks concerning the following question:

I've written a Perl script (db2sql.pl) which reads records in a flat ASCII database and adds them to a MySQL database using the Perl DBI.

In my cron file, I have a line which runs this script every five minutes:

1,6,11,16,21,26,31,36,41,46,51,56 * * * * cgi-bin/dbman/db2sql.sh
[download]

(I've also tried renaming this db2sql.pl - it doesn't make a difference what I call it).

But it never produces any results.

The strange thing is that if I logon to my site via SSH and run the same script, it does what it's supposed to do. The records are added to the MySQL database.

The script permissions are 755, like all my Perl scripts.

As I say -- it runs fine from the command line, but not via cron. Any ideas?

I'm pasting the entire code below, with the MySQL details blacked out.

Thanks very much.

#!/usr/bin/perl

use DBI;

$state = " ";
open (FILE1, "default.db");
@records = <FILE1>;
close(FILE1);
$db = DBI->connect('dbi:mysql:dbxxxxxxxx:dbxxx.oneandone.co.uk','dboxx
+xxxxxx','xxxxxx');
my $sth = $db->prepare("insert into news(url, header, country, country
+2, date, priority, userid, source, formoreinfo, language, regreqd, ke
+ywords, image, actnowcampaigncode, state) values(?,?,?,?,?,?,?,?,?,?,
+?,?,?,?,?)");

open(COUNTER,"db2sql.count");
@counters = <COUNTER>;
close(COUNTER); 
foreach $count (@counters)    {$rc = $count;}

foreach $record (@records) {
    @fields = split(/\|/,$record);
    $rr++; # counts records read in default.db
    if ($rr > $rc) { # only reads new records
    if ($fields[5] > 0) {
         &convdate;
        &convlang;
        $sth->execute($fields[0], $fields[1], $fields[2], $fields[3], 
+$date, $fields[5], $fields[6], $fields[7], $fields[8], $language, $fi
+elds[10], $fields[11], $fields[12], $fields[13], $state);
        }
        }
    }
$db->disconnect();

open(COUNT, ">db2sql.count"); # Keeps a record of last item added to S
+QL DB
print COUNT "$rr";
close(COUNT);

sub convdate {
@dfields = split(/-/,$fields[4]); # Convert date to yyyymmdd
($dd) = $dfields[0];
($month) = $dfields[1];
($yyyy) = $dfields[2];
if ($month eq "Jan") {$mm = "01"}; # Get month number
if ($month eq "Feb") {$mm = "02"};
if ($month eq "Mar") {$mm = "03"};
if ($month eq "Apr") {$mm = "04"};
if ($month eq "May") {$mm = "05"};
if ($month eq "Jun") {$mm = "06"};
if ($month eq "Jul") {$mm = "07"};
if ($month eq "Aug") {$mm = "08"};
if ($month eq "Sep") {$mm = "09"};
if ($month eq "Oct") {$mm = "10"};
if ($month eq "Nov") {$mm = "11"};
if ($month eq "Dec") {$mm = "12"};
$dd = sprintf("%02d", $dd);
$date = "$yyyy$mm$dd";
}
sub convlang {
($languagename) = $fields[9];
$language = ""; # blanks out the field first
if ($languagename eq "English") {$language = "en";}
if ($languagename eq "Indonesian") {$language = "id";}
if ($languagename eq "Chinese") {$language = "zh";}
if ($languagename eq "Danish") {$language = "da";}
if ($languagename eq "German") {$language = "de";}
if ($languagename eq "Spanish") {$language = "es";}
if ($languagename eq "Esperanto") {$language = "eo";}
if ($languagename eq "French") {$language = "fr";}
if ($languagename eq "Italian") {$language = "it";}
if ($languagename eq "Creole") {$language = "cpf";}
if ($languagename eq "Dutch") {$language = "nl";}
if ($languagename eq "Norwegian") {$language = "no";}
if ($languagename eq "Polish") {$language = "pl";}
if ($languagename eq "Portuguese") {$language = "pt";}
if ($languagename eq "Russian") {$language = "ru";}
if ($languagename eq "Finnish") {$language = "fi";}
if ($languagename eq "Swedish") {$language = "sv";}
if ($languagename eq "Turkish") {$language = "tr";}
}
[download]

Comment on Script runs from command line but not via crontab Select or Download Code

Replies are listed 'Best First'.
Re: Script runs from command line but not via crontab by kirillm (Friar) on Jan 02, 2008 at 10:24 UTC
Hi, What's the full path to your script and what's the contents of the environment variable $PATH in your crontab? Specify full path to your script in the crontab and the problem should go away: `1-59/5 * * * * /full/path/to/cgi-bin/dbman/db2sql.sh` [download] P.S. 1-59/5 Is a shorter way to say that the command should be executed every 5 mins.	[reply] [d/l]
Re: Script runs from command line but not via crontab by ForgotPasswordAgain (Vicar) on Jan 02, 2008 at 10:25 UTC
Oh la la.... You have no error checking at all. Start with putting some '`or die "blah: $!"`' after the calls to `open`. When it's working, add `use strict; use warnings;` to the top of the code and declare variables with `my` as necessary. Get rid of the global variables like `@fields` within the subroutines. Replace `open FILE1` by `open my $file1`. Replace the blobs of `if`s with hashes (`%langname = (English => 'en', Indonesian => 'id', etc...);`).	[reply] [d/l] [select]
Re: Script runs from command line but not via crontab by quester (Vicar) on Jan 02, 2008 at 10:30 UTC
I would change the crontab entry (at least temporarily) to `1,6,11,16,21,26,31,36,41,46,51,56 * * * * cgi-bin/dbman/db2sql.sh>/tmp +/xxx.log 2>&1` [download] so that you can see the output of the script, including error messages. (It may or may not be mailed to you by default depending on your system.) The usual problem with things that refuse to run under cron is that cron doesn't run your .profile or .bash_profile or .bashrc... before your scripts run. Typically you will find an environment variable that needs to be set. If there actually is a shell script at db2sql.sh, you could try calling .profile or .bash_profile from it.	[reply] [d/l]
Re: Script runs from command line but not via crontab by apl (Monsignor) on Jan 02, 2008 at 10:49 UTC
In addition to what ForgotPasswordAgain said, your crontab entry should specify STDOUT and STDERR (e.g. `>/log/db2sql.out 2>/log/db2sql.err)` so you can see the results of the run. MySQL may already be telling you what the problem is...	[reply] [d/l]
Re: Script runs from command line but not via crontab by cdarke (Prior) on Jan 02, 2008 at 12:50 UTC
...alll of the above, plus: when you run a cron job it does not usually invoke a login shell. The effect of that is then dependant on which shell you are using, and what is in your startup files. If critical environment variables are set in a startup file then you may find they are not set - PATH is an example and is mentioned above. I have to say that your 'if' statements are laborious, try using preset hash. Update: `%languagenames = ("English" => "en", "Indonesian" => "id", "Chinese" => "zh", "Danish" => "da", "German" => "de", "Spanish" => "es", "Esperanto" => "eo", "French" => "fr", "Italian" => "it" "Creole" => "cpf", "Dutch" => "nl", "Norwegian" => "no", "Polish" => "pl", "Portuguese" => "pt" "Russian" => "ru", "Finnish" => "fi", "Swedish" => "sv", "Turkish" => "tr");` [download] The hash should be global, or a state variable if you have 5.10. Then in the subroutine: `%language = $languagenames{$languagename};` [download] You can use a simple array for the month numbers.	[reply] [d/l] [select]
Re: Script runs from command line but not via crontab by dwm042 (Priest) on Jan 02, 2008 at 15:06 UTC
I would have to agree with kirillm for the most part. When a script runs on the command line but fails in crontab, it's usually an environment variable issue. The cron environment is very limited and it does not inherit the settings from your login shell, unless you take a lot of care and trouble to make it happen. The posters above have given suggestions to get your script to give up error messages and place them into files to be read. What I'll add is that you can get your environment variables, if you're in bash/ksh, with the command: `set` [download] and that you probably should, while you're debugging your script, add something like: print `set`; [download] into your script, just to see what the cron environment is (assuming you've redirected your script output to a file as others have suggested). Finally, one style suggestion. It is much easier to do date manipulations with a module such as Date::Calc or Date::Manip than manually.	[reply] [d/l] [select]
Re: Script runs from command line but not via crontab by dsheroh (Monsignor) on Jan 02, 2008 at 17:08 UTC
As already stated, it's generally an environment issue when something works on the command line, but not from cron. In this specific case, I suspect issue is not an environment variable, though, but rather the working directory. You start off by opening your input file with the assumption that it's in the current directory, but you can't make assumptions like that under cron - unless you've tested, how do you know whether cron runs the script from your home directory, from /tmp, or from just about anywhere else on the system? Does your script work from the command line if you run it from a random, arbitrary directory? (Unless you have a copy of default.db in every directory on the system, I expect not.) For cron shortcuts, note that 1-59/5 will only run the job 11 times per hour (at :05, :10,... :55 - nothing on the hour). 0-59/5 will get in the 12th run at :00, but if that's what you want, you can do it even more simply with */5, to run whenever the value is a multiple of 5.	[reply]