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;
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 ....
Go to tarball downloads.
Use the container quay.io/mariadb-foundation/mariadb-devel:10.7-mdev-25015-convert-partition.
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.