CODEWITHSUNDEEP
mysqlbashcron
Divya
Divya

Reputation: 3

Delete with subquery not working from cron job for MySQL

I have the below script for deleting duplicate entries from particular table. I have scheduled this job through cron job daily once. But delete is not happening from cron job. If I run the same query from the MySQL console, it works fine. Can anyone help me what is wrong with crontab?

#!/bin/bash
export PATH=/bin:/usr/bin:/usr/local/bin
TODAY=`date +"%d%b%Y"`
MYSQL_USER='**********'
MYSQL_PASS='*********'
MYSQL_DB='*********'
MYSQL_HOST='********'
START_TIME=`date`

echo "Deletion of duplicate records started for the table CPM_QUESTIONS at ${START_TIME}"

SQL="delete from CPM_QUESTIONS WHERE ID IN (select * from duplicate_questions_id);"

echo $SQL | /usr/bin/mysql --user=$MYSQL_USER --host=$MYSQL_HOST --password=$MYSQL_PASS $MYSQL_DB

END_TIME=`date`

echo "Deletion of duplicate records completed for the table CPM_QUESTIONS at at ${END_TIME}"

Subquery table ddl as below:

mysql> desc duplicate_questions_id;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   |     | 0       |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

Upvotes: 0

Views: 72

Answers (1)

Holger Just
Holger Just

Reputation: 55888

You probably want to quote your $SQL variable when passing it to mysql as well as any other variable you use. If you do not do this, bash will glob the * character in your SQL query and pass the list of files in the current directory to mysql, rather than a literal asterisk.

Your full script can thus look like this:

#!/bin/bash
export PATH=/bin:/usr/bin:/usr/local/bin
TODAY="`date +"%d%b%Y"`"
MYSQL_USER='**********'
MYSQL_PASS='*********'
MYSQL_DB='*********'
MYSQL_HOST='********'
START_TIME="`date`"

echo "Deletion of duplicate records started for the table CPM_QUESTIONS at ${START_TIME}"

SQL="delete from CPM_QUESTIONS WHERE ID IN (select * from duplicate_questions_id);"

echo "$SQL" | /usr/bin/mysql --user="$MYSQL_USER" --host="$MYSQL_HOST" --password="$MYSQL_PASS" "$MYSQL_DB"

END_TIME="`date`"

echo "Deletion of duplicate records completed for the table CPM_QUESTIONS at at ${END_TIME}"

Upvotes: 1

Related Questions