Thanks all for the help on getting my log_addpartition event running, dynamic SQL was just the ticket.
I'm now focusing on writing the sister event: 'log_removepartition', which will find and purge partitions older than some time interval. The information schema comes in quite handy here, as I can query the PARTITIONS table to see all partitions.
mysql> select GROUP_CONCAT( PARTITION_NAME )
from
information_schema.PARTITIONS
where
TABLE_SCHEMA='test' and
TABLE_NAME='log'
group by TABLE_NAME;
+---------------------------------------------------+
| GROUP_CONCAT( PARTITION_NAME ) |
+---------------------------------------------------+
| p20080206,p20080207,p20080208,p20080209,p20080212 |
+---------------------------------------------------+
+---------------------------------------------------+
| GROUP_CONCAT( PARTITION_NAME ) |
+---------------------------------------------------+
| p20080206,p20080207,p20080208,p20080209,p20080212 |
+---------------------------------------------------+
So, I can easily add the CREATE_TIME column to the WHERE clause to find all partitions older than a certain date. Using that query with the SELECT ... INTO syntax and dynamic SQL, I can 'ALTER TABLE DROP PARTITION' on all those partitions. The end result looks something like this:
DELIMITER |
CREATE EVENT log_removepartition ON SCHEDULE EVERY 1 DAY
DO
BEGIN
DECLARE old_partitions CHAR(64) DEFAULT '';
SELECT GROUP_CONCAT( PARTITION_NAME ) from information_schema.PARTITIONS
where
TABLE_SCHEMA='test' and TABLE_NAME='log' and
CREATE_TIME < DATE_SUB( CURDATE(), INTERVAL 1 WEEK )
group by TABLE_NAME
INTO old_partitions; SET @s = CONCAT( 'ALTER TABLE log DROP PARTITION ', old_partitions );
PREPARE stmt FROM @s;
EXECUTE stmt;
END |
DELIMITER ;
This seems work work correctly, it removed my older partitions when I shortened the INTERVAL down to match my earliest partitions. I'll let this run for a week and make sure it's operating the way I want it to. Any suggestions to simplify/shorten are certainly welcome.
Trackback URL for this post:
http://mysqlguy.net/trackback/9
Jay, I'm curious whether
Jay, I'm curious whether adding and removing partitions rebuilds existing partitions? That would be insane, of course, but... you know.... such things have happened.
Just checking. I haven't played with partitions much.
No, I believe ALTER TABLE
No, I believe ALTER TABLE DROP PARTITION simply removes the partition in question along with the data. Check out the the ALTER TABLE page in the manual and read the paragraph that starts with "
DROP PARTITIONcan be used to drop one or moreRANGEorLISTpartitions.".Hi!I'm glad the dynamic SQL
Hi!
I'm glad the dynamic SQL worked for you. Anyway, regarding the code to remove partitions:
- instead of doing TABLE_SCHEMA = 'someconstant' you can do TABLE_SCHEMA = schema() for the current schema - that is, the schema wherein your event resides, or when you decide to wrap all inside a stored proc, the schema wherein your proc resides. So if that happens to be the same schema as the schema with your table, you make the schema as a whole portable.
- Currently you calculate the list in the selct, and use a separate SET to build the statement. You could do:
SELECT CONCAT('ALTER TABLE log DROP PARTITION ', GROUP_CONCAT(partition_name))
INTO @stmt
FROM ....
This will make it somewhat more compact (not sure it's for the better though)
- You currently do not DEALLOCATE your stmt. You should! There is some automatic cleanup at the end of a session. Now it might be so that the event thread ends the session and cleans up the statement automagically, but personally I would not take any chance and make sure there is an explicit DEALLOCATE PREPARE stmt after the EXECUTE
- You use the pipe as delimiter. Many people do but I would still disrecommend that as the pipe is an operator in MySQL's SQL dialect. Other delimiters that are quite often used are $ and // and those do not have any special meaning and are thus completely safe to use.