MySQL Partitioning automation

partitioning.sql

--
-- I tested at /home/ted/sandboxes/MySQL-HOWTOs
-- Start MySQL (/home/ted/sandboxes/MySQL-HOWTOs/scripts/start.sh) and bring up client: mysql -uroot -proot -h127.0.0.1 -P3306
-- Run script: source /home/ted/labb/partitioning/partitioning.sql
-- Also tested in OCI, host 130.61.35.76
-- 
-- Remember to set group_concat_max_len to a high enough value.
-- I recommend setting it to at least 50000.
-- 

CREATE DATABASE IF NOT EXISTS ted;
use ted;

DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
    id INT AUTO_INCREMENT NOT NULL,
    message VARCHAR(500) NOT NULL,
    code TINYINT UNSIGNED NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id,created)
);

ALTER TABLE logs PARTITION BY RANGE COLUMNS(created) (
    PARTITION p20200217 VALUES LESS THAN ('2020-02-17'),
    PARTITION p20200218 VALUES LESS THAN ('2020-02-18'),
    PARTITION p20200219 VALUES LESS THAN ('2020-02-19'),
    PARTITION p20200220 VALUES LESS THAN ('2020-02-20'),
    PARTITION p20200221 VALUES LESS THAN ('2020-02-21')
);

DROP TABLE IF EXISTS blogs;
CREATE TABLE blogs (
    id INT AUTO_INCREMENT NOT NULL,
    message VARCHAR(500) NOT NULL,
    code TINYINT UNSIGNED NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id,created)
);

ALTER TABLE blogs PARTITION BY RANGE COLUMNS(created) (
    PARTITION p20200217 VALUES LESS THAN ('2020-02-17'),
    PARTITION p20200218 VALUES LESS THAN ('2020-02-18'),
    PARTITION p20200219 VALUES LESS THAN ('2020-02-19'),
    PARTITION p20200220 VALUES LESS THAN ('2020-02-20'),
    PARTITION p20200221 VALUES LESS THAN ('2020-02-21')
);

-- SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='logs' AND TABLE_SCHEMA='ted'\G

-- 
-- Internal stuff to handle partitioning.
-- Table MyPartTables contains one row per table to manage.
-- Insert table name, schema name, number of future partitions to create
-- and max number of partitions to keep before starting to remove old partitions
--
-- Two events will run once per day, first event will call a SP to add new partitions,
-- the second event will call a SP to remove any old partitions. This is done for
-- all tables that are recorded on MyPartTables.
-- 

DROP TABLE IF EXISTS MyPartTables;
CREATE TABLE IF NOT EXISTS `MyPartTables` (
  `schema_name` VARCHAR(100) NOT NULL,
  `table_name` VARCHAR(100) NOT NULL,
  `no_of_future_partitions` INT NOT NULL,
  `max_partitions` INT NOT NULL,
  PRIMARY KEY (`schema_name`,`table_name`)
);

INSERT INTO MyPartTables VALUES ('ted','logs',2,20);
-- Handle partition for table ted.logs, create 2 partitions in the future and
-- keep only 20 partitions before starting to remove old partitions.
INSERT INTO MyPartTables VALUES ('ted','blogs',3,5);
-- Handle partition for table ted.blogs, create 3 partitions in the future and
-- keep only 5 partitions before starting to remove old partitions.

DELIMITER ;;

-- Run SP AddPartition() once per day
DROP EVENT IF EXISTS `AddPartitions`;;
CREATE EVENT AddPartitions ON SCHEDULE EVERY 1 DAY
DO
  BEGIN
  CALL AddPartitions();
END ;;

--
-- SP that creates new partitions
-- Only works for RANGE partitions using DAY resolution
--
DROP PROCEDURE IF EXISTS `AddPartitions`;;
CREATE PROCEDURE `AddPartitions`()
BEGIN
  DECLARE done   BOOL DEFAULT FALSE;
  DECLARE maxp   INT DEFAULT 0;
  DECLARE pdate  INT DEFAULT 0;
  DECLARE pname  VARCHAR(20);    
  DECLARE v_table_name  VARCHAR(100);
  DECLARE v_schema_name  VARCHAR(100);
  DECLARE v_no_future_part INT;

  DECLARE list CURSOR FOR SELECT `table_name`,`schema_name`,`no_of_future_partitions` FROM MyPartTables;

  DECLARE CONTINUE HANDLER FOR
  SQLSTATE '02000'
  SET done = TRUE;

  OPEN list;
  tloop: LOOP
    IF done THEN
      CLOSE list;
      LEAVE tloop;
    END IF;

    FETCH list INTO v_table_name,v_schema_name,v_no_future_part;
    -- DEBUG SELECT v_table_name,v_schema_name,v_no_future_part;

    SET pdate=TO_DAYS(DATE(NOW() + INTERVAL (v_no_future_part) DAY));
    SELECT TO_DAYS(TRIM(BOTH "'" FROM MAX(PARTITION_DESCRIPTION))) INTO maxp
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME=v_table_name
           AND TABLE_SCHEMA=v_schema_name
           AND PARTITION_DESCRIPTION!="MAXVALUE";
    -- DEBUG' SELECT pdate, maxp;

    WHILE pdate > maxp DO
       SET maxp = maxp + 1;
       SET pname = CONCAT('p', DATE_FORMAT(FROM_DAYS(maxp), '%Y%m%d'));
       SET @qry = CONCAT('ALTER TABLE ',v_schema_name,'.',v_table_name, ' ADD PARTITION (PARTITION ',pname,' VALUES LESS THAN ("',DATE_FOR
MAT(FROM_DAYS(maxp),'%Y-%m-%d'),'"))');
       -- DEBUG SELECT @qry;
       PREPARE stmt FROM @qry;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
    END WHILE;
  END LOOP;
END ;;

-- Run SP RemovePartition() once per day
DROP EVENT IF EXISTS RemovePartitions;
CREATE EVENT RemovePartitions ON SCHEDULE EVERY 1 DAY
DO
  BEGIN
  CALL RemovePartitions();
END ;;

--
-- SP that removes old partitions once you have more than max_partitions
-- Only works for RANGE partitions using DAY resolution
--
DROP PROCEDURE IF EXISTS RemovePartitions;;
CREATE PROCEDURE RemovePartitions ()
BEGIN
  DECLARE done BOOL DEFAULT FALSE;
  DECLARE v_table_name  VARCHAR(100);
  DECLARE v_schema_name  VARCHAR(100);
  DECLARE v_max_partitions INT;
  DECLARE v_no_of_partitions_to_remove INT;

  DECLARE cur CURSOR FOR SELECT `table_name`,`schema_name`,`max_partitions` FROM MyPartTables;

  DECLARE CONTINUE HANDLER FOR
  SQLSTATE '02000'
  SET done = TRUE;

  OPEN cur;

  tloop: LOOP
    FETCH cur INTO v_table_name,v_schema_name,v_max_partitions;
    IF done THEN
      CLOSE cur;
      LEAVE tloop;
    END IF;
    -- DEBUG SELECT v_table_name,v_schema_name,v_max_partitions;
    SET @qry = CONCAT('SELECT COUNT(DISTINCT(PARTITION_DESCRIPTION)) INTO @v_no_of_partitions ',
                     'FROM INFORMATION_SCHEMA.PARTITIONS ',
                     'WHERE TABLE_NAME="',v_table_name, '" ',
                     'AND TABLE_SCHEMA = "',v_schema_name, '" ');
    -- DEBUG SELECT @qry;
    PREPARE stmt FROM @qry;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    -- DEBUG SELECT @v_no_of_partitions, v_max_partitions;

    IF @v_no_of_partitions > v_max_partitions THEN
      SET v_no_of_partitions_to_remove = @v_no_of_partitions - v_max_partitions;
      SET @qry = CONCAT('SELECT GROUP_CONCAT(DISTINCT(PARTITION_NAME)) INTO @v_list_of_partitions_to_remove FROM ( ',
                            'SELECT PARTITION_NAME ',
                            'FROM INFORMATION_SCHEMA.PARTITIONS ',
                            'WHERE TABLE_NAME = "',v_table_name, '" ',
                            'AND TABLE_SCHEMA = "',v_schema_name, '" ',
                            'AND PARTITION_DESCRIPTION!="MAXVALUE" ',
                            'ORDER BY PARTITION_DESCRIPTION LIMIT ',v_no_of_partitions_to_remove,
                            ' ) as slabb');
      -- DEBUG SELECT @qry;
      -- DEBUG SELECT @v_partitions_to_remove;
      PREPARE stmt FROM @qry;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

      SET @qry = CONCAT('ALTER TABLE ',v_table_name, ' DROP PARTITION ', @v_list_of_partitions_to_remove);
      -- DEBUG SELECT @qry;
      PREPARE stmt FROM @qry;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
    END IF;
  END LOOP;
END ;;

DELIMITER ;

-- Run some tests:
SELECT "========== Tables before AddPartitions ==========" as "";
SHOW CREATE TABLE logs\G
SHOW CREATE TABLE blogs\G
CALL AddPartitions();
SELECT "========== Tables after  AddPartitions ==========" as "";
SHOW CREATE TABLE logs\G
SHOW CREATE TABLE blogs\G
CALL RemovePartitions();
SELECT "========== Tables after RemovePartitions ===========" as "";
SHOW CREATE TABLE logs\G
SHOW CREATE TABLE blogs\G
SELECT "========== Contect of MyPartTables ==========" as "";
SELECT * FROM MyPartTables;
SHOW EVENTS;