MySQL Forums
Forum List  »  Newbie

Re: help making event for auto delete of partitions
Posted by: Darren White
Date: June 14, 2012 01:28AM

> The customer has specified they do not require more than 1 month of data
Then, shouldn't you partition by day, not by month???

perhaps but I have little experience of mysql partitions and I was given a nice example of monthly paritions. The way I planned to do it was let the data grow to two months then delete the first month. That way I always have one month of data.

If you can suggest a better/easier method I'd be happy :)

here was the event I thought I would try to call the drop partition routine:

DO
BEGIN
DECLARE iMonth INTEGER;
SET iMonthSelect = Month(CURRENT_TIMESTAMP)-2;
call logg_recreate_partition( iMonthSelect );
END$$

here was the routine:

DELIMITER $$

create procedure logg_recreate_partition( partno int )
begin
set @stmt1 = concat( 'alter table loggg drop partition p', partno );
prepare stmt1 FROM @stmt1;
execute stmt1;
deallocate prepare stmt1;

set @stmt1 = concat ('alter table loggg add partition ( partition p', partno,
' values in ( ', partno, ' ))' );
prepare stmt1 FROM @stmt1;
execute stmt1;
deallocate prepare stmt1;
end$$



Edited 1 time(s). Last edit at 06/14/2012 01:38AM by Darren White.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.