Hi there. I'm trying to make a bash script to automatically purge old records from a MySQL database, to be run every day from the CRON directory cron.daily.
The script I have is as follows:
mysql -h$MyHOST -e "use pstsize; SELECT * FROM sizehist WHERE msgtime < CURRENT_DATE - INTERVAL 30 DAY && modtime < CURRENT_DATE - INTERVAL 30 DAY;" >> /var/log/sizehist_cleanup.log
It seems to work okay for the SELECT * command. Viewing the log, exactly what I want is there. However since I'm a newbie to MySQL I figured I'd have it search and display the information prior to doing something relatively destructive.
The need is to have it find entries that where 2 date fields (msgtime and modtime) are greater than 30 days. If either of the date fields are less than 30 days it leaves the entries alone.
if msgtime & modtime > 30 days - delete entries
if msgtime < 30 days but modtime > 30 days leave alone (dont delete)
if msgtime > 30 days but modtime < 30 days leave alone (dont delete)
if msgtime < 30 days and modtime < 30 days leave alone (dont delete)
I tried changeing the SELECT * to DELETE * but got the following error:
ERROR 1064 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 '* FROM sizehist WHERE msgtime < CURRENT_DATE - INTERVAL 30 DAY
Is it possible that it's because I didnt' include a username/password? As far as I know a username/password isn't required for this specific database.
Anyhelp would be appreciated
Thanks in advance