Automating Monthly Partition Creation in MySQL 8.0

This guide walks through automating monthly RANGE partition creation in MySQL 8.0 — part of the automated partition creation workflows pattern within partitioning implementation patterns & routing.

Prerequisites

• MySQL 8.0.17 or later (earlier 8.0 releases have INFORMATION_SCHEMA.PARTITIONS inconsistencies)
• EVENT privilege on the target schema (GRANT EVENT ON mydb.* TO 'app_user'@'%')
• ALTER privilege on the target table
• innodb_file_per_table = ON in my.cnf (isolates partition files at the filesystem level)
• Access to INFORMATION_SCHEMA for validation queries
• A staging replica to test DDL before promoting to production
• The event_scheduler variable confirmed ON (SHOW VARIABLES LIKE 'event_scheduler')

Step 1 — Create the partitioned table

Before deploying automation, verify that the schema supports efficient range partitioning strategies. MySQL requires the partition expression to reference only columns present in every unique and primary key. Using UNIX_TIMESTAMP() on a TIMESTAMP column converts it to an integer, enabling RANGE partitioning with straightforward integer comparisons and reliable partition pruning during time-bounded queries.

-- Verify and enable the event scheduler
SET GLOBAL event_scheduler = ON;
SHOW VARIABLES LIKE 'event_scheduler';

CREATE TABLE metrics (
  id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_ts TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  payload  JSON,
  PRIMARY KEY (id, event_ts)
) ENGINE=InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(event_ts)) (
  PARTITION p202312 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01 00:00:00')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

Operational note: Always include a MAXVALUE catch-all partition at creation. Without it, any row whose timestamp falls beyond the highest defined boundary triggers ERROR 1526 (HY000): Table has no partition for value X and rejects the write — a silent data-loss risk under high ingestion rates.

DBA tip: MySQL’s partitioning constraint requires every column referenced in the partition expression to appear in every PRIMARY KEY and UNIQUE KEY. Because event_ts drives the partition expression, it must be part of the composite primary key — hence PRIMARY KEY (id, event_ts) rather than a single-column key on id.

Step 2 — Create the dynamic partition procedure

Manual DDL execution introduces human error, lock contention, and service interruptions during maintenance windows. A reusable stored procedure calculates the next month’s boundary dynamically and issues ALTER TABLE ... ADD PARTITION without blocking concurrent InnoDB reads or writes. The logic relies on LAST_DAY() and INTERVAL arithmetic for accurate boundary calculation across months of varying length, paired with PREPARE/EXECUTE for safe dynamic DDL generation.

DELIMITER //

CREATE PROCEDURE add_monthly_partition()
BEGIN
  DECLARE next_boundary DATE;
  DECLARE partition_name VARCHAR(20);
  DECLARE sql_stmt TEXT;

  -- Calculate the first day of next month (handles leap years automatically)
  SET next_boundary = DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY);
  SET partition_name = CONCAT('p', DATE_FORMAT(next_boundary, '%Y%m'));

  -- Guard against duplicate partition creation (prevents ERROR 1481)
  IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA   = DATABASE()
      AND TABLE_NAME     = 'metrics'
      AND PARTITION_NAME = partition_name
  ) THEN
    SET sql_stmt = CONCAT(
      'ALTER TABLE metrics REORGANIZE PARTITION p_future INTO (',
      'PARTITION ', partition_name,
      ' VALUES LESS THAN (UNIX_TIMESTAMP(''', next_boundary, ''')),',
      'PARTITION p_future VALUES LESS THAN MAXVALUE)'
    );
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END //

DELIMITER ;

Operational note: The procedure uses REORGANIZE PARTITION p_future INTO (...) rather than a plain ADD PARTITION. This splits the MAXVALUE catch-all into a named monthly partition plus a new p_future — preserving the boundary and avoiding ERROR 1481 (HY000): Duplicate partition name on subsequent runs. Any rows already absorbed by p_future are redistributed automatically.

SRE tip: Wrap the procedure body with DECLARE EXIT HANDLER FOR SQLEXCEPTION to capture metadata lock timeouts and write failures to an audit table. This produces an auditable record without relying solely on the MySQL error log, which is critical for SLA reporting in regulated environments.

Step 3 — Schedule the EVENT

The native EVENT scheduler replaces external cron dependencies, keeping partition rotation tightly coupled to the database instance and ensuring it survives host restarts without external orchestration. Configure the event to fire on the first of each month at a low-traffic hour, aligned with your application’s timezone:

CREATE EVENT IF NOT EXISTS ev_monthly_partition
  ON SCHEDULE
    EVERY 1 MONTH
    STARTS DATE_FORMAT(
      DATE_ADD(NOW(), INTERVAL 1 MONTH),
      '%Y-%m-01 02:00:00'
    )
  ON COMPLETION PRESERVE
  ENABLE
  DO CALL add_monthly_partition();

Operational note: ON COMPLETION PRESERVE retains the event definition after execution. Without it, MySQL silently drops one-shot events on completion — a footgun if you accidentally omit EVERY during maintenance.

DBA tip: Set event_scheduler = ON permanently in my.cnf or my.ini. MySQL’s default is OFF, so any server restart without this config line silently disables partition automation until someone notices the growing p_future partition:

# my.cnf / my.ini
[mysqld]
event_scheduler = ON

MySQL does not replicate CREATE EVENT statements to replicas via standard binary log replication. Create matching events on each replica independently, or drive automation from an external scheduler (cron + MySQL client) pointed at each instance.

Step 4 — Verify correctness

Query INFORMATION_SCHEMA.PARTITIONS immediately after the first event execution to confirm monthly boundaries exist and that PARTITION_DESCRIPTION values are chronologically contiguous with no gaps:

SELECT
  PARTITION_NAME,
  PARTITION_DESCRIPTION,
  TABLE_ROWS,
  DATA_LENGTH / 1024 / 1024 AS data_mb
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'metrics'
ORDER BY PARTITION_ORDINAL_POSITION;

Expected output — each month appears as a distinct row, p_future is always last with PARTITION_DESCRIPTION = MAXVALUE, and TABLE_ROWS for p_future is near zero (confirming no live data is accumulating outside defined boundaries):

+------------+------------------------+------------+---------+
| PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS | data_mb |
+----------------+----------------------+------------+---------+
| p202312        | 1704067200           |    8432100 |  612.40 |
| p202401        | 1706745600           |    9100200 |  660.10 |
| p202402        | 1709251200           |    8750400 |  635.70 |
| p_future       | MAXVALUE             |          0 |    0.00 |
+----------------+----------------------+------------+---------+

Also confirm the scheduler is running and the event is visible:

-- Check scheduler status
SHOW VARIABLES LIKE 'event_scheduler';

-- Inspect event definition and last-run time
SELECT EVENT_NAME, STATUS, LAST_EXECUTED, STARTS, EVENT_DEFINITION
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA = DATABASE()
  AND EVENT_NAME   = 'ev_monthly_partition';

Set up a monitoring alert that fires when any of these conditions are true: p_future holds more than 5% of total TABLE_ROWS; LAST_EXECUTED is more than 32 days in the past; or the expected partition name for the current month is absent from INFORMATION_SCHEMA.PARTITIONS.

Failure mode table

FAQ

Related

• Automated Partition Creation Workflows — parent page covering scheduling strategies, pre-creation buffering, and metadata synchronisation across MySQL, PostgreSQL, and Kubernetes
• Range Partitioning Strategies — boundary selection, pruning optimisation, and lifecycle management for RANGE-partitioned tables
• Hash Routing Algorithms — complement RANGE-based time partitioning with consistent-hash routing at the application or proxy layer