ALTER TABLE Improvements in MariaDB Server 10.3

MariaDB Server 10.3.7 (the first Generally Available release in the series) includes some ALTER TABLE improvements that are worth mentioning. Last October, I wrote about the Instant ADD COLUMN feature that was introduced in the 10.3.2 alpha release. The effort to support instant ALTER TABLE in MariaDB comes from a collaboration with ServiceNow. The first part of that, instant ADD COLUMN, was brainstormed in April 2017 by engineers from MariaDB Corporation, Alibaba and Tencent. A prototype was first developed by Vin Chen (陈福荣) from the Tencent Game DBA Team and was later refined by our team for the MariaDB version. Part of the original plan was to introduce syntax for ALTER TABLE…ALGORITHM=INSTANT in order to be able to give a guarantee that the requested operation will be performed instantly, or not at all. This was finally implemented in MariaDB Server 10.3.7. We also introduced the keyword ALGORITHM=NOCOPY, which will refuse an operation if the table would be rebuilt.

Example

CREATE TABLE t(id INT PRIMARY KEY, u INT UNSIGNED NOT NULL UNIQUE)
ENGINE=InnoDB;
INSERT INTO t(id,u) VALUES(1,1),(2,2),(3,3);

SET alter_algorithm=instant;
ALTER TABLE t ADD COLUMN d DATETIME DEFAULT current_timestamp();
--error ER_ALTER_OPERATION_NOT_SUPPORTED
# There is no instant DROP COLUMN yet
ALTER TABLE t DROP COLUMN u;
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t DROP COLUMN u, ALGORITHM=NOCOPY;
SET alter_algorithm=default;
ALTER TABLE t DROP COLUMN u;

The example illustrates a new configuration parameter alter_algorithm. A DBA could set it globally in the MariaDB Server configuration to NOCOPY in order to prevent expensive ALTER TABLE statements from being executed by mistake. The ALGORITHM=INPLACE syntax, which was added in MariaDB Server 10.0, can misleadingly suggest that no copying ever takes place. However, until the efforts to implement instant schema changes (instant DROP COLUMN and others being worked on for upcoming MariaDB Server releases), the inplace operation often did involve copying, potentially allocating quite a bit of extra space, not only for the new copy of the table, but also for pre-sorting the data and for logging concurrent modifications to the table (ALTER ONLINE TABLE). With the ALGORITHM=INSTANT and ALGORITHM=NOCOPY clauses, which represent proper subsets of ALGORITHM=INPLACE, we are clarifying the situation.

Imitation is the sincerest form of flattery

We’re happy to hear that MySQL 8.0 will add support for instant ADD COLUMN that is based on our work. But you don’t have to wait to try it.

Try it out

MariaDB Server 10.3.7 was announced as GA on May 25, 2018. Download MariaDB TX 3.0, which includes MariaDB Server 10.3.7 to upgrade your current server and immediately get the benefit of instant ADD COLUMN for your old InnoDB tables. Note that if you need to export InnoDB data files to older servers than MariaDB Server 10.3, you must rebuild the table first: ALTER TABLE t FORCE;