mysql and cron

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
< 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.

Any help would be appreciated. Thank you so ever much for any help.

Natalie

Re: mysql and cron

>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.

What is the value of $PATH when you run the script from the command line?
Where is the mysql command? /usr/local/bin/mysql ?

>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".

What is the value of $PATH when you run the script under cron?
/bin:/usr/bin ?

>
>Error Log:
>
>scriptname.sh: line #: mysql: command not found

Try setting PATH (and export it) or using an absolute pathname for
the command.

Gordon L. Burditt

Re: mysql and cron

wrote in message
news:1136843985.883561.165980@g43g2000cwa.googlegroups.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
> < > 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 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

Re: mysql and cron

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 ?

================
Thomas,

The crontab and script are run/owned by the same user (not root). And
the account does have a plain text cron job that's pushed onto the
crontab. I never use root unless necessary and only su into it since
that's the only way you can get to root (disabled both remote login and
console login for root)... :)

But that is very good advise. And in genereal anyone not doing this
should. Thank you.
================
What is the value of $PATH when you run the script from the command
line?
Where is the mysql command? /usr/local/bin/mysql ?
================
Gordon,

It was the PATH. I fixed it and now everything works. Thank you.
================