Adding a partition to already partitioned table

Whats the best way to add another partition to an already partitioned table?

The original CREATE TABLE statement looks like:

CREATE TABLE `command_log` (
  `id` bigint(20) NOT NULL,
  `insert_time` datetime NOT NULL,
  `start_time` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `command` varchar(255) NOT NULL,
  `parameters` varchar(255) DEFAULT NULL,
  `result` mediumblob,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`,`insert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (to_days(insert_time))
(PARTITION p001 VALUES LESS THAN (736237) ENGINE = InnoDB,
 PARTITION p002 VALUES LESS THAN (736268) ENGINE = InnoDB,
 PARTITION p003 VALUES LESS THAN (736298) ENGINE = InnoDB,
 ...
 PARTITION p064 VALUES LESS THAN (738156) ENGINE = InnoDB,
 PARTITION p065 VALUES LESS THAN (738187) ENGINE = InnoDB,
 PARTITION p066 VALUES LESS THAN (738215) ENGINE = InnoDB,
 PARTITION p067 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

Let's say I want to add just 1 additional partition, in this case p067. Does this require a whole ALTER TABLE statement, like:

ALTER TABLE command_log 
PARTITION by range (to_days(insert_time))
(
    partition p059 VALUES LESS THAN (to_days('2020-08-01'))
  , partition p060 VALUES LESS THAN (to_days('2020-09-01'))
  , partition p061 VALUES LESS THAN (to_days('2020-10-01'))
  , partition p062 VALUES LESS THAN (to_days('2020-11-01'))
  , partition p063 VALUES LESS THAN (to_days('2020-12-01'))
  , partition p064 VALUES LESS THAN (to_days('2021-01-01'))
  , partition p065 VALUES LESS THAN (to_days('2021-02-01'))
  , partition p066 VALUES LESS THAN (to_days('2021-03-01'))
  , partition p067 VALUES LESS THAN (to_days('2021-04-01'))
  , partition p068 VALUES LESS THAN (MAXVALUE)
);

If this is the case, what exactly happens?

Do my older partitions not included in this statement get dropped (for example p001 - p058)?

Does doing this wipe out any existing data in the table (for instance, data in p059)?

Thanks!

Solution

You don't have to redefine all the earlier partitions.

It's a common operation in range partitioned tables to split the last partition that handles values less than MAXVALUE into a few new partitions for specific ranges. You can do this with REORGANIZE PARTITION.

For example, to split the last partition into two new partitions for fixed date ranges, plus a new maxvalue partition at the end:

ALTER TABLE command_log REORGANIZE PARTITION p067 INTO (
  partition p067 VALUES LESS THAN (TO_DAYS('2021-04-01'))
, partition p068 VALUES LESS THAN (TO_DAYS('2021-05-01'))
, partition p069 VALUES LESS THAN (MAXVALUE)
);

All the earlier partitions will be untouched by this reorganize operation.

If you do this reorganize while the last partition is still empty (i.e. it contains zero rows), then no data copying is needed, and the operation should be virtually instantaneous.

If you forget to do the reorganize, and the last partition collects some rows, then it's not too late to reorganize it — but it will take a little bit of time, proportional to the number of rows in the partition you split. Reorganizing a partition that has data does require copying data rows, but only the rows of the partition you are reorganizing. Earlier partitions are still untouched.

See https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-range-list.html for more details.