Mit CPC Mit CPC - 2 months ago 18
Bash Question

Error delete data mysql from shell script

Below shell script : error choose 3 1,2 it's OK but mysql delete success. shell script can't delete

#!/bin/bash

selection=
until [ "$selection" = "0" ]; do
echo "=============================="
echo "=========PROGRAM MENU========="
echo "1 - Show Date and Time"
echo "2 - Show Data Database"
echo "3 - Delete data 90 day ago"
echo ""
echo "0 - Back to command"
echo "=============================="
echo -n "Enter selection : "
read selection
echo ""

case $selection in
2 ) mysql -u [user] -p[password] -e 'USE eventime;SELECT date1,time1 FROM dt;' ;;
1 ) dti=$(date '+%d/%m/%Y %H:%M:%S');
echo "This is Date and Time : " $dti ;;
3 ) mysql -u [user] -p[password] -e 'USE eventime;
DELETE FROM dt WHERE date1 <(SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(now(),\'%Y-%m-%d\'),\'-1\'),\'%Y-%m-%d\')- INTERVAL 90 DAY);' ;;

0 ) Ctrl+Z ;;
* ) echo "Please enter 1, 2, 3 or 0"
esac
done


**Output : selection 3 error

test.bash: line 24: syntax error near unexpected token `)'
bash: test.bash: line 24: ` DELETE FROM dt WHERE date1 <(SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(now(),\'%Y-%m-%d\'),\'-1\'),\'%Y-%m-%d\')- INTERVAL 90 DAY);' ;;'

Answer

You have to escape the nested single quotes:

   3 )  mysql -u [user] -p[password] -e 'USE eventime;
    DELETE FROM dt WHERE date1 <(SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(now(),\'%Y-%m-%d\'),\'-1\'),\'%Y-%m-%d\')- INTERVAL 90 DAY);' ;;