ALTER TABLE Improvements in MariaDB Server 10.4

spacer

MariaDB Server 10.4 includes new ALTER TABLE improvements. A year earlier, the first GA release of MariaDB Server 10.3 introduced support for ADD COLUMN with ALGORITHM=INSTANT. Now, MariaDB Server 10.4 introduces more classes of ALGORITHM=INSTANT operations, among them instant DROP COLUMN and changing the order of columns. This further extends the InnoDB data file format that was extended in MariaDB Server 10.3.

The ALGORITHM=INPLACE syntax, which was added in MariaDB Server 10.0, can misleadingly suggest that no copying ever takes place. With the ALGORITHM=INSTANT and ALGORITHM=NOCOPY clauses, which represent proper subsets of ALGORITHM=INPLACE, MariaDB Server 10.3 clarified the situation. You can specify these clauses or SET alter_algorithm to request an error be returned if the ALTER TABLE operation cannot be executed instantly or without copying the table.

MariaDB Server 10.4 improved some other cases that do not require any changes to the data format. For example, some unnecessary limitations for instantly extending VARCHAR columns were lifted. Also, we support some instant conversions of character encoding, most notably from the Basic Multilingual Plane subset of UTF-8 (utf8mb3) to generic UTF-8, to allow the insertion of emojis and other special characters: 𝄞𝅘𝅥 💯🎂. Note that changing the collation of indexed columns involves rebuilding the indexes, but the operation can still be ALGORITHM=NOCOPY, which is an improvement from earlier versions of MariaDB (and MySQL).

Try it 

MariaDB Server 10.4.6 was announced as GA on June 18, 2019. Download the latest version of MariaDB Server, which is the foundation of MariaDB Platform, to upgrade your current server and immediately get the benefit of instant ALTER TABLE for your old InnoDB tables.

Note that if you can still add or drop or reorder columns in the old-fashioned (and data-format compatible) way, by requesting the table to be rebuilt: ALTER TABLE t FORCE; .