StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Excellent solution from Justin there. I took his code as the starting point for my current project and would like to mention a few things that came up while I was implementing it.</p> <ol> <li><p>The existing partition structure in the table you run this on should not include a MAXVALUE type partition - all partitions must be delimited by literal dates. This is because SELECT MAX(PARTITION_DESCRIPTION) will return 'MAXVALUE' which fails to be converted to a date in the next step. If you get odd message when calling the procedure saying something like: illegal mix of collations for '<', this could be the problem.</p></li> <li><p>It's a good idea to add: "AND TABLE_SCHEMA = 'dbname'" when selecting partition names from the INFORMATION_SCHEMA table, because while more than one partition can exist with the same name for the same table (in different databases), they are all listed in the INFORMATION_SCHEMA table together. Without the TABLE_SCHEMA specification your select eg. MAX(PARTITION_DESCRIPTION) will give you the max partition name among every existing partition for tables of that name in every database.</p></li> <li><p>Somewhere along the way I had problems with the ALTER TABLE xxx ADD PARTITION as it is in Justin's solution, I think it was that the same format for the partition name (yyyymmdd) was being used as the partition delimiter which expected yyyy-mm-dd (v5.6.2).</p></li> <li><p>The default behaviour is to only add partitions in the future as necessary. If you want to create partitions for the past, you will need to first set up a partition for a date older than the oldest partition you want. Eg. if you are keeping data for the past 30 days, first add a partition for say, 35 days ago and then run the procedure. Granted, it may only be feasible to do this on an empty table, but I thought it worth mentioning.</p></li> <li><p>In order to create the desired span of past/future partitions as in 4. you will initially need to run the procedure twice. For the example in 4. above, the first run will create partitions for -35 days to present, and the necessary future partitions. The second run will then trim the partitions between -35 and -30 away.</p></li> </ol> <p>Here is what I am using at the moment. I added some parameters to make it a bit more flexible from the caller's point of view. You can specify the database, table, current date, and how many partitions to keep for both past and future. </p> <p>I also altered the naming of partitions so that the partition named p20110527 represents the day <em>starting</em> from 2011-5-27 00:00 instead of the day <em>ending</em> at that time. </p> <p>There is still no error checking or prevention of simultaneous execution :-)</p> <pre><code>DELIMITER $$ DROP PROCEDURE IF EXISTS UpdatePartitions $$ -- Procedure to delete old partitions and create new ones based on a given date. -- partitions older than (today_date - days_past) will be dropped -- enough new partitions will be made to cover until (today_date + days_future) CREATE PROCEDURE UpdatePartitions (dbname TEXT, tblname TEXT, today_date DATE, days_past INT, days_future INT) BEGIN DECLARE maxpart_date date; DECLARE partition_count int; DECLARE minpart date; DECLARE droppart_sql date; DECLARE newpart_date date; DECLARE newpart_sql varchar(500); SELECT COUNT(*) INTO partition_count FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname; -- SELECT partition_count; -- first, deal with pruning old partitions WHILE (partition_count > days_past + days_future) DO -- optionally, do something here to deal with the parition you're dropping, e.g. -- copy the data into an archive table SELECT STR_TO_DATE(MIN(PARTITION_DESCRIPTION), '''%Y-%m-%d''') INTO minpart FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname; -- SELECT minpart; SET @sql := CONCAT('ALTER TABLE ' , tblname , ' DROP PARTITION p' , CAST(((minpart - INTERVAL 1 DAY)+0) as char(8)) , ';'); -- SELECT @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT COUNT(*) INTO partition_count FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname; -- SELECT partition_count; END WHILE; SELECT STR_TO_DATE(MAX(PARTITION_DESCRIPTION), '''%Y-%m-%d''') INTO maxpart_date FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname; -- select maxpart_date; -- create enough partitions for at least the next days_future days WHILE (maxpart_date < today_date + INTERVAL days_future DAY) DO -- select 'here1'; SET newpart_date := maxpart_date + INTERVAL 1 DAY; SET @sql := CONCAT('ALTER TABLE ' , tblname , ' ADD PARTITION (PARTITION p' , CAST(((newpart_date - INTERVAL 1 DAY)+0) as char(8)) , ' VALUES LESS THAN (''' , newpart_date , '''));'); -- SELECT @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT STR_TO_DATE(MAX(PARTITION_DESCRIPTION), '''%Y-%m-%d''') INTO maxpart_date FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname; SET maxpart_date := newpart_date; END WHILE; END $$ DELIMITER ; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload