MySQL v8 script for partitioning the Zabbix database while also deleting old partitions

zabbix_partitioning.sql

DELIMITER $$
USE zabbix$$

DROP PROCEDURE IF EXISTS manage_partitions$$
CREATE PROCEDURE manage_partitions()
BEGIN
    -- Change these according to your settings
    DECLARE zabbix_db_name VARCHAR(64) DEFAULT 'zabbix';
    DECLARE trends_storage_period INT DEFAULT UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 5 YEAR));
    DECLARE history_storage_period INT DEFAULT UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 1 YEAR));

    -- Partition naming pattern and its regex version must match each other
    DECLARE naming_pattern VARCHAR(64) DEFAULT 'p%Y_%m_%d';
    DECLARE regex_naming_pattern VARCHAR(64) DEFAULT 'p[0-9]{4}_[0-9]{2}_[0-9]{2}';

    DECLARE created_at INT;
    DECLARE done INT DEFAULT 0;
    DECLARE table_name_tmp VARCHAR(64);
    DECLARE partition_name_tmp VARCHAR(64);

    -- Gets existing Zabbix partitions from information_schema
    DECLARE get_partitions CURSOR FOR SELECT table_name, partition_name, LTRIM(RTRIM(partition_description)) FROM information_schema.partitions
    WHERE table_schema = zabbix_db_name AND partition_name RLIKE regex_naming_pattern
    ORDER BY table_name, subpartition_ordinal_position;

    -- Change this query to add or remove specific tables to the partition scheduler
    DECLARE get_tables CURSOR FOR SELECT DISTINCT(table_name) FROM information_schema.partitions
    WHERE table_schema = zabbix_db_name AND (table_name LIKE 'history%' OR table_name LIKE 'trends%');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN get_partitions;

    loop_partitions: LOOP
        IF done THEN
            LEAVE loop_partitions;
        END IF;
        
        FETCH get_partitions INTO table_name_tmp, partition_name_tmp, created_at;

        -- If the partition was created before the history and/or trend storage period it is dropped
        IF (table_name_tmp LIKE 'history%' AND created_at < history_storage_period) OR (table_name_tmp LIKE 'trends%' AND created_at < trends_storage_period) THEN
            SET @sql = CONCAT('ALTER TABLE `', zabbix_db_name, '`.`', table_name_tmp, '` DROP PARTITION ', partition_name_tmp, ';');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        ITERATE loop_partitions;
    END LOOP loop_partitions;
    CLOSE get_partitions;

    -- Reset
    SET done = 0;
    OPEN get_tables;

    loop_tables: LOOP
        IF done THEN
            LEAVE loop_tables;
        END IF;

        FETCH get_tables INTO table_name_tmp;

        BEGIN
            DECLARE rows_count INT UNSIGNED;

            /* Partitions will always be created for the next day to avoid unnecessary resources
            consumption, therefore the first day's partitions must be manually created */
            DECLARE start_time TIMESTAMP DEFAULT DATE(NOW()) + INTERVAL 1 DAY;

            /* Creates partitions per day, if the schedule is to be changed to something
            other then "EVERY 1 DAY", the `end_time` variable must be changed accordingly */
            DECLARE end_time INT UNSIGNED DEFAULT UNIX_TIMESTAMP(start_time + INTERVAL 1 DAY);

            -- Good practice to name your partitions by the period they are storing data
            SET partition_name_tmp = DATE_FORMAT(start_time, naming_pattern);

            -- Checks if the partition was already created
            SELECT COUNT(*) INTO rows_count FROM information_schema.partitions
            WHERE table_schema = zabbix_db_name AND table_name = table_name_tmp AND partition_name = partition_name_tmp;
            
            IF rows_count = 0 THEN -- If the partition does not already exist, then it must be created
                SET @sql = CONCAT('ALTER TABLE `', zabbix_db_name, '`.`', table_name_tmp, '` ADD PARTITION (PARTITION ', partition_name_tmp, ' VALUES LESS THAN (', end_time, '));');
                PREPARE stmt FROM @sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END;
        
        ITERATE loop_tables;
    END LOOP loop_tables;
    CLOSE get_tables;
END$$


-- PARTITION MANAGER EVENT
DROP EVENT IF EXISTS partition_manager$$
CREATE EVENT partition_manager ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE + INTERVAL 1 DAY)
DO BEGIN
    -- Assuming default `zabbix` database name
    CALL zabbix.manage_partitions();
    SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Zabbix partitions have been managed';
END$$

DELIMITER ;