The Ultimate Guide to High Availability with MariaDB

InnoDB Online DDL Operations with ALGORITHM=INPLACE

You are viewing an old version of this article. View the current version here.

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. Changing a Column Name
  13. Adding a New Enum Option
  14. Renaming a Table

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 can not be performed with ALGORITHM=INPLACE in most cases. If you try to do so, then you will see an error. For example:

CREATE TABLE t1 (a INT, e ENUM ('red','green'));
ALTER TABLE t1 MODIFY a INT UNSIGNED, ALGORITHM=INPLACE;
-> ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

In MariaDB 10.2.2 and later, the only exception is that 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.

Changing a Column Name

A column's name can be changed with ALGORITHM=INPLACE.

Adding a New Enum Option

A new ENUM option can be added with ALGORITHM=INPLACE if it is added to the end of the list. For example:

CREATE TABLE t1 (a INT, e ENUM ('red','green'));
ALTER TABLE t1 MODIFY e ENUM('red','green','blue'), ALGORITHM=INPLACE;
-> Query OK, 0 rows affected (0.11 sec)
-> Records: 0  Duplicates: 0  Warnings: 0

Renaming a Table

A table can be renamed with ALGORITHM=INPLACE.

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.