Reorganizing partitions

If the MAXVALUE partition is there, you cannot add a partition after MAXVALUE; in that case, you need to the REORGANIZE MAXVALUE partition into two partitions:

mysql> ALTER TABLE event_history REORGANIZE PARTITION pmax INTO (PARTITION p20171016 VALUES LESS THAN (736983) ENGINE = InnoDB,PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Remember MySQL has to substantially move the data while reorganizing partitions and the table will be locked during that period.

You can also reorganize multiple partitions into a single partition:

mysql> ALTER TABLE event_history REORGANIZE PARTITION p20171001,p20171002,p20171003,p20171004,p20171005,p20171006,p20171007 INTO (PARTITION p2017_oct_week1 VALUES LESS THAN (736974)); ...

Get MySQL 8 Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.