If you are using table partitioning, you have likely heard of the ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... command. It existed in MariaDB since forever. But if you check the manual (any manual) or search the web, you will see that almost the only use case of it is converting a partition to a standalone non-partitoned table, or converting a standalone non-partitoned table into a partition.
And the usage was designed back then to be anything but obvious. To convert a partition to a table you need first to create an empty table with the same structure as a partition, then you exchange it with a partition, and then you drop the empty partition. Something like
-- create an empty non-partitioned table
CREATE TABLE normal_table LIKE partitioned_table;
ALTER TABLE normal_table REMOVE PARTITIONING;
-- perform the exchange
ALTER TABLE partitioned_table EXCHANGE PARTITION part1 WITH TABLE normal_table;
-- and remove the now-empty partition
ALTER TABLE partitioned_table DROP PARTITION part1;Similarly, to convert a table to a partition, you need to create a partition first, then exchange, then drop the table:
-- create an empty partition
ALTER TABLE partitioned_table ADD PARTITION (PARTITION part1 VALUES LESS THAN (12345));
-- perform the exchange
ALTER TABLE partitioned_table EXCHANGE PARTITION part1 WITH TABLE normal_table;
-- and remove the now-empty table
DROP TABLE normal_table;Given that this is the main use of this EXCHANGE feature, it could’ve been easier to use, thought we. And being usability freaks that we are, we’ve implemented two new ALTER TABLE variants that do just that. Now one can convert a partition to a table and back with ease:
ALTER TABLE partitioned_table CONVERT PARTITION part1 TO TABLE normal_table;and
ALTER TABLE partitioned_table CONVERT TABLE normal_table TO PARTITION part1 VALUES LESS THAN (12345);As a additional benefit, new commands are not only more convenient, but also crash safe — they will always end up being either fully completed or completely reverted, even if your server will crash or will be killed in the middle of the operation. This, of course, was never true for the complex multi-statement sequence of ALTER TABLE ... EXCHANGE ... WITH TABLE ....
How to try the 10.7 CONVERT PARTITION preview yourself?
Tarball
Go to tarball downloads.
Container
Use the container quay.io/mariadb-foundation/mariadb-devel:10.7-mdev-25015-convert-partition.
Feedback Welcome
If you come across any problems in this feature preview, with the design, or edge cases that don’t work as expected, please let us know with a JIRA bug/feature request in the MDEV project. You are welcome to chat about it on Zulip.