MySQL Forums
Forum List  »  Newbie

help making event for auto delete of partitions
Posted by: Darren White
Date: June 05, 2012 03:33AM

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?

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.