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:

#!/bin/sh
SHELL=/bin/bash
PATH=usr/bin
MyHOST="localhost"
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
exit 0


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.

EG:

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

Carlo

There's no '*' in DELETE, as the msg is hinting

DELETE FROM table ....