<th*********@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Hi.
I am a newbie to mysql, cron, and shell scripting, so please bear with
me. I have a script that is used for updating an image directory based
on contents in a database. The script does the following: runs
several queries against different tables in a database; returns several
lists of pictures being used in the database; removes any obsolete
images from specific directories. The script is bash shell script.
The problem is that I can run this script from the command line without
any problems. After testing this script several times, I decided to
put into crontab so that it can execute daily. From crontab, it will
not execute the mysql commands in the file saying that "no commands
were found".
Error Log:
scriptname.sh: line #: mysql: command not found
My sql command in my script is as follows:
mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST dbname
<<COMMANDS
SELECT PHOTO INTO OUTFILE "filename" FROM table_name WHERE PHOTO != ""
AND PHOTONAME LIKE "\%0";
COMMANDS
Also, please note that I tried this command without 'PHOTONAME LIKE
"\%0"' to see if the % was causing any problems, and even with that
removed, it would not execute the mysql statements in this script.
If you can do it on the command line, then it will work in crontab.
My first guess would be that you are running crontab for a different user
than the one where you did your command line test - the one that worked so
well.
try
crontab -l
at the same command line that worked so well. Does it print out the cron
that you set up?
My guess is it won't because you entered it in someone else cron.
Did I guess correctly ?
Every user (including "root") has his/her own cron.
I don't know why, but most people seem to think they have to run cron as
"root". They test the command line as [natalie] (for example!) then enter
the same command line in the [root] cron and wonder why it doesn't work.
The reason is that [root] runs in a different environment than [natalie] and
the search $PATH probably doesn't contain the path to the mysql utility.
One solution is to give the full path to the mysql utility instead of just
"mysql".
Better would be to run it in [natalie]'s own personal cron rather than
root - or someone elses.
crontab -e
at the same command prompt you use to test will put you into edit mode for
that particular users cron.
Better yet - just edit your cron in a plain text file. Then:
crontab <MyCron.txt
to push it into your own [natalie] cron.
crontab -l
will show it back at you.
You might also consider that -
[mysql] is set up as it's own user on the system and has, of course, it's
own cron available.
It might be appropriate to log in as [mysql] and use *that* crontab to do
mysql maintenance.
Never do anything as [root] unless you absolutely have to !
Thomas Bartkus