MySQL Forums :: Newbie :: help making event for auto delete of partitions


Advanced Search

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


Subject Written By Posted
help making event for auto delete of partitions Darren White 06/05/2012 03:33AM
Re: help making event for auto delete of partitions Darren White 06/05/2012 05:05AM
Re: help making event for auto delete of partitions Darren White 06/05/2012 05:51AM
Re: help making event for auto delete of partitions Rick James 06/13/2012 11:42AM
Re: help making event for auto delete of partitions Darren White 06/14/2012 01:28AM
Re: help making event for auto delete of partitions Darren White 06/14/2012 01:46AM
Re: help making event for auto delete of partitions Rick James 06/15/2012 06:03PM
Re: help making event for auto delete of partitions Darren White 06/18/2012 06:09AM


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.