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