The Ultimate Guide to High Availability with MariaDB

Online DDL Overview

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.0

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

Contents

  1. Adding, Dropping, Reordering Columns
  2. Changing the Data Type of a Column
  3. Adding a Primary Key
  4. Dropping a Primary Key
  5. Changing Auto-increment Value
  6. Changing Columns to NULL or NOT NULL
  7. Setting Column Default
  8. Dropping or Renaming an Index
  9. Creating or Adding an Index
  10. Optimizing a Table
  11. Adding or Dropping Foreign Key Constraints
  12. Limitations

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

Adding, Dropping, Reordering Columns

The table is rebuilt, and, with the exception of adding an auto-increment column, permit concurrent DML. The operation can be expensive, as the entire table will be copied and indexes rebuilt.

Starting with MariaDB 10.3.2, ADD COLUMN as the last column can be performed instantaneously, without rebuilding the table. If a hidden FTS_DOC_ID column is present, table rebuild will still be needed.

Starting with MariaDB 10.4, ADD COLUMN, DROP COLUMN, and reordering columns can be performed instantaneously, without rebuilding the table.

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, which is performed as an instantaneous metadata update.

Adding a Primary Key

The table is rebuilt. Concurrent DML is permitted. The operation can be expensive, as the entire table will be copied and indexes rebuilt. If a column has to be converted to NOT NULL, online operation will be refused except if strict mode is set.

Dropping a Primary Key

Dropping a primary key cannot, with one exception, be performed online, 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 online, permitting concurrent DML while the table is being copied and indexes rebuilt.

Changing Auto-increment Value

Changing the auto_increment does not rebuild the table, and can be performed instantly.

Changing Columns to NULL or NOT NULL

The table is rebuilt, but changing columns to NULL or NOT NULL 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. 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, generally permit concurrent DML (see Limitations below). If both innodb_defragment and innodb_optimize_fulltext_only are OFF (the default setting), OPTIMIZE TABLE will be equivalent to ALTER TABLE…FORCE.

If either of these variables is set, OPTIMIZE TABLE will optimize some data without rebuilding the table, and will not shrink any files.

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.

Limitations

If FULLTEXT or SPATIAL indexes exist in a table, concurrent DML will not be permitted during ALTER TABLE operations.

If multiple FULLTEXT indexes exist in the table after the ALTER TABLE or OPTIMIZE TABLE operation, then only ALGORITHM=COPY will be possible.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.