# Problem with mysql command in crontab



## memesiz (Nov 25, 2013)

Hey all, I've made a script including a mysql command to make a query. It works fine from the command line but not from crontab. Generally the script works (there is a part that is sending e-mail to me, it works) but the mysql command doesn't (not returning 0 after the mysql command).

In the crontab file I've added  /usr/local/bin to PATH and in the script I'm running mysql as /usr/local/bin/mysql but it still fails.

Any help will be appreciated.


----------



## SirDice (Nov 26, 2013)

Add some echo commands to your script and redirect the output to a log file. Use the log file to find out where your script fails.


----------



## ShelLuser (Nov 26, 2013)

You might also want to show us the script, or at least the line where you try to use MySQL.


----------



## memesiz (Nov 26, 2013)

Hey, this is the MySQL part of the script:

```
#!/usr/local/bin/bash
aydi=$(cat sonid.txt | awk '{print $1}')
#echo $aydi

/usr/local/bin/mysql -u root -h xxx -p'xxx' "ISPDB" -e "SELECT * FROM xx where id > $aydi;" > steler1.txt

if [ $? -ne 0 ];

then

echo "MYSQL Query Failed" | mail -s "......." mail@mail.com.tr

else

.....
```

And PATH in /etc/crontab:

```
SHELL=/bin/sh
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin
```

I get MySQL query failed e-mail.


----------



## SirDice (Nov 27, 2013)

A few things, don't use bash for such a simple script, stick to /bin/sh. Second, don't use the MySQL root account for scripts like this. Keep in mind that your script contains the password. If somebody is able to read the script they'll have full access to your databases. 

As for the error, look in the steler1.txt that's created, I'm quite sure the exact error is in there.


----------



## memesiz (Nov 27, 2013)

SirDice, thank you for advice, it is just part of the script including the mysql command. I'm going to redirect errors to steler1.txt and tell you what there is.

*I*t's empty.


----------



## SirDice (Nov 27, 2013)

Don't redirect anything inside the script. That'll make debugging it by hand a little easier. Do the redirection on the crontab line like so:

```
0  1  *  *  *  /path/to/my/script.sh > /var/log/myscript.log 2>&1
```
Once it works you can change the redirection to 

```
> /dev/null 2>&1
```

This way you'll also capture any script errors. Be sure to add some echo lines printing information where the script is, what variables etc. Enough to give you an idea what it's doing and where it may fail.


----------



## SirDice (Nov 27, 2013)

Oh, I just thought of something. It's possible the asterisk (*) is being interpreted by the shell before it's sent to mysql. So instead of an asterisk mysql will get a bunch of filenames from whatever directory when the script runs. That's because the string is in double quotes ("), which you want because of $aydi.


----------



## memesiz (Nov 27, 2013)

*T*hanks @SirDice, the problem is not MySQL according to the log output. It is just little mistake.


```
cat: sonid.txt: No such file or directory
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
```

I've just added the full path to the sonid.txt file, it is working fine. Thank you for your help.


----------

