Way to automate deletion of expired records in MySQL?

Question

I've googled around and searched the MYSQL docs ad nauseam and couldn't find a succinct way of automating deletion of records that exceeded a given timeframe. I've been able to get a query in 5.1 to cast a value of TIMESTAMP to DATETIME within a DIFF function with the current time to see if it meets the criteria of expiration. I've read that 5.1 now has the capability of running scheduled tasks but not much in the way of configuring it. I'm not using triggers for this.

In the MySQL docs for 5.1, it refers to creating an event:

'CREATE 
[DEFINER = { user | CURRENT_USER }]
EVENT 
[IF NOT EXISTS]
event_name    
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO sql_statement;

schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval 
[STARTS timestamp [+ INTERVAL interval] ...] 
[ENDS timestamp [+ INTERVAL interval] ...]

interval:

I currently use Toad (which has been a Godsend). My query affectively removes any records that are more than 30 minutes old. I just need to find how this event gets invoked...

Thanks!

Answer 1

Write a query and have it ran on a job every so often. Say, check for the expired rows every 30 minutes or so.

Answer 2

If it doesn't have to be exact, and you're just housekeeping, you can tie the process to another one. If you can afford the time.

If you have an old invoice file, purge it when month-end is run (possibly a lot of records, but it's a batch process anyway). Purge old inventory items when you add new ones (less frequent, but fewer records possibly). Keeping an access log table? Purge it when the most recent record in it falls on a different day than today. (for low traffic logfiles) And so on.

Answer 3

You are talking about using the MySQL Scheduler. Once you create that event, MySQL will call it automatically at whatever interval you configure it with. If you are having trouble getting it set up, post the query and error your are getting.