I'm not familiar with this type of script, but would you also want to optimise the table?
On Thursday, November 1, 2012 9:07:32 PM UTC, ash kumar wrote: > > Does the following procedure appear right to delete older than 30 days > data, or am I missing something? > > I am deleting from tables alert and data only based on alert ids. > > DELIMITER $$ > > CREATE DEFINER=`ossec`@`%` PROCEDURE `purge_old_data`() > BEGIN > > declare done int default 0; > declare cur1id tinytext; > declare cur1 cursor for select alertid from ossec.alert where > date(from_unixtime(timestamp)) < curdate() -30; > declare continue handler for sqlstate '02000' set done = 1; > > open cur1; > repeat > fetch cur1 into cur1id; > if not done then > delete from ossec.alert where alert.alertid = cur1id; > delete from ossec.data where data.alertid = cur1id; > end if; > until done end repeat; > close cur1; > > END > > Thanks and regards > > Ash Kumar >
