MariaDB starting with 10.0

InnoDB and XtraDB tables support online DDL, permitting concurrent DML or in-place alterations in many circumstances.

The following list describes the state of online DDL for various operations

Adding, Dropping, Reordering Columns

The table is rebuilt, but adding, dropping or re-ordering columns can be performed in-place, and, with the exception of adding an auto-increment column, permit concurrent DML. The operation can be expensive as data may require substantial reorganization.

Changing the Data Type of a Column

Changing column types cannot, with one exception, be performed in-place, requiring a full table copy which does not permit concurrent DML. The only exception, from MariaDB 10.2.2, is increasing the size of a VARCHAR column.

Adding a Primary Key

The table is rebuilt, but adding a primary key can usually be performed in-place, and permits concurrent DML. The operation can be expensive as data may require substantial reorganization. If a column has to be converted to NOT NULL, in-place changes are not permitted.

Dropping a Primary Key

Dropping a primary key cannot, with one exception, be performed in-place, requiring a full table copy which does not permit concurrent DML. The only exception is if the primary key is dropped and another added. This can be performed in-place, and permits concurrent DML, but can still be an expensive operation as data may require substantial reorganization.

Changing Auto-increment Value

Changing the auto_increment does not rebuild the table, and can be performed in-place, permitting concurrent DML

Changing Columns to NULL or NOT NULL

The table is rebuilt, but changing columns to NULL or NOT NULL can be performed in-place, and permit concurrent DML. The operation can be expensive as data may require substantial reorganization. If a column is being changed to NOT NULL, strict mode is required to be set (the default from MariaDB 10.2.4), so the operation will fail if the column contains any NULLs. Changes that would interfere with referential integrity are also not permitted.

Setting Column Default

This operation only changes the metadata, so can be done in-place, does not require a table rebuild, and permits concurrent DML.

Dropping or Renaming an Index

Only changes the metadata, so can be done in-place, does not require a table rebuild, and permits concurrent DML.

Creating or Adding an Index

With the exception of some FULLTEXT indexes, can be done in-place, permitting concurrent DML. FULLTEXT indexes without a user-defined FTS_DOC_ID column require the table to be rebuilt, preventing concurrent DML. After the first rebuild (in which the system adds a hidden FTS_DOC_ID column), further FULLTEXT indexes added to the same table do not require the table to be rebuilt.

Optimizing a Table

OPTIMIZE TABLE statements rebuild the table, and, with the exception of tables with FULLTEXT indexes, can be performed in-place, permitting concurrent DML.

Adding or Dropping Foreign Key Constraints

For dropping foreign key constraints, does not rebuild the table, only updating the metadata, and can be performed in-place, permitting concurrent DML. For adding foreign key constraints, in-place is only supported if the foreign_key_checks system variable is disabled. If it is enabled (the default), the copy algorithm is required.

Comments

Comments loading...