help making event for auto delete of partitions
I have some log tables growing at a rate of one row per second. The customer has specified they do not require more than 1 month of data. I was advised by some nice guy on the forums to try partitioning my tables by date and then dropping partitions using an event /routine. I'll simplify by making an example that functions as I wish:
Here is the create and parition (this works ok - after I found out you cannot use timestamp for partition, only datetime)
create table loggg(
logdate datetime not null,
message varchar(500),
KEY( logdate )
)
PARTITION BY LIST ( month( logdate ) )
(
PARTITION p0 VALUES IN ( 0 ),
PARTITION p1 VALUES IN ( 1 ),
PARTITION p2 VALUES IN ( 2 ),
PARTITION p3 VALUES IN ( 3 ),
PARTITION p4 VALUES IN ( 4 ),
PARTITION p5 VALUES IN ( 5 ),
PARTITION p6 VALUES IN ( 6 ),
PARTITION p7 VALUES IN ( 7 ),
PARTITION p8 VALUES IN ( 8 ),
PARTITION p9 VALUES IN ( 9 ),
PARTITION p10 VALUES IN ( 10),
PARTITION p11 VALUES IN ( 11 ),
PARTITION p12 VALUES IN ( 12 )
)
;
Then here is the stored routine to delete a month (partition):
delimiter $$
drop procedure if exists logg_recreate_partition $$
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;
$$
Now what I need is a clever way of calling this procedure from an event so that I only ever have the last months worth of data in the tables.
I thought something like (obviously with correct syntax):
ON SCHEDULE
EVERY 24 HOUR
IF month==march
call logg_recreate_partition( 1 );
IF month==april
call logg_recreate_partition( 2 );
IF month==may
call logg_recreate_partition( 3 );
IF month==june
call logg_recreate_partition( 4 );
etc etc etc.
but this looks messy and I am not quite sure how to implement it.
Any suggestions?