O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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)); ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required