let table grow for two months then delete first month

[Darren White]

Hi - I would appreciate any tips getting an EVENT SCHEDULE going. Not sure it should be an event though so will put it in Newbie.

I have log tables growing at a rate of one row per second (an example of CREATE for one such table below).

I would ideally like to run an EVENT that would check the timestamps on this column periodically and when it reaches 2 months of data (5.4 million rows), then delete the first months worth of data.

Any tips to get me started?

I imagined something like this:

CREATE EVENT tidy_logs
ON SCHEDULE
EVERY 1 MONTH
STARTS (NOW() + INTERVAL 1 MINUTE)
DO
BEGIN

SELECT @firstDate := MIN(last_update) FROM log_streamer_01;
SELECT @lastDate := MAX(last_update) FROM log_streamer_01;
IF @lastDate - @firstDate >= 2 MONTHS THEN -- unsure of syntax
DELETE --the first month. mot sure how to do this best on a table with 6million rows

-- repeat for other tables

END$$

--

CREATE TABLE `log_streamer_01` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`streamer_tension` float NOT NULL DEFAULT '0',
`cable_speed` float NOT NULL DEFAULT '0',
`reel_speed_rpm` float NOT NULL DEFAULT '0',
`meter_out` float NOT NULL DEFAULT '0',
`synchro_delta_deviation` float NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54337 DEFAULT CHARSET=latin1$$