What is MySQL's Online DDL

Posted on 2 April, 2021 at 20:40 +0900 by Gentaro "hibariya" Terada

Today I learned a MySQL term: online DDL.

The online DDL lets you alter tables efficiently, without making tables unavailable during the entire operation and exclusive table locks. The behavior of a DDL operation can be specified using the ALGORITHM and LOCK clauses. You don't have to get overwhelmed by the mysterious terms. Actually, you may have used it before without knowing because MySQL will try to use it by default when you execute ordinary alter table statements.

By placing the ALGORITHM clause to a DDL, you can tell MySQL how to process the operation. With the COPY algorithm, the operation is performed on a copy of the original table. INPLACE avoids copying table data and as a result, the operation will be more efficient. INSTANT could be used for only modifying some metadata. Some of these algorithms have limitations. For example, INPLACE and INSTANT do not support dropping primary keys, and INSTANT does not support renaming columns.

The LOCK clause lets you specify the restrictive level of the locking. The possible values are NONE, SHARED, DEFAULT, and EXCLUSIVE. In some operations like adding an auto-increment column, SHARED or more restrictions are required.

When running a DDL without these clauses, MySQL would try to use INSTANT or INPLACE for the algorithm with as little locking as possible. Even so, occasionally, you would want to specify them explicitly to avoid unexpected behavior, especially in production environments. Let's say the table that is going to be altered is large and the operation must be done as soon as possible without long-lived exclusive locks. So you want to avoid either expensive copy operations or lengthy locks. Although MySQL tries to run the operations in the least expensive way by default, you will want to make sure that. In that case, passing ALGOLITHM and LOCK clauses assure you that the operation will never run in an unexpected way.

ALTER TABLE users CHANGE sex gender integer, ALGORITHM=INPLACE, LOCK=NONE;

If the DDL cannot be executed with in-place operations without lock, MySQL will stop without executing it and tell you the reason.

mysql> ALTER TABLE users CHANGE sex gender integer, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

See Also

Gentaro "hibariya" Terada