The following limitations apply to online DDL operations:
The table is copied when creating an index on a
LOCK=NONEis not permitted if there are
ON...SET NULLconstraints on the table.
Before an online DDL operation can finish, it must wait for transactions that hold metadata locks on the table to commit or roll back. An online DDL operation may briefly require an exclusive metadata lock on the table during its execution phase, and always requires one in the final phase of the operation when updating the table definition. Consequently, transactions holding metadata locks on the table can cause an online DDL operation to block. The transactions that hold metadata locks on the table may have been started before or during the online DDL operation. A long running or inactive transaction that holds a metadata lock on the table can cause an online DDL operation to timeout.
An online DDL operation on a table in a foreign key relationship does not wait for a transaction executing on the other table in the foreign key relationship to commit or rollback. The transaction holds an exclusive metadata lock on the table it is updating and shared metadata lock on the foreign-key-related table (required for foreign key checking). The shared metadata lock permits the online DDL operation to proceed but blocks the operation in its final phase, when an exclusive metadata lock is required to update the table definition. This scenario can result in deadlocks as other transactions wait for the online DDL operation to finish.
When running an online DDL operation, the thread that runs the
ALTER TABLEstatement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in
InnoDBin which constraints must hold during a transaction.
OPTIMIZE TABLEfor an
InnoDBtable is mapped to an
ALTER TABLEoperation to rebuild the table and update index statistics and free unused space in the clustered index. Prior to 5.6.17, there is no online DDL support for this operation. Secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key. As of 5.6.17,
OPTIMIZE TABLEis supported with the addition of online DDL support for rebuilding regular and partitioned
Tables created before MySQL 5.6 that include temporal columns (
TIMESTAMP) and have not been rebuilt using
ALGORITHM=COPYdo not support
ALGORITHM=INPLACE. In this case, an
ALTER TABLE ... ALGORITHM=INPLACEoperation returns the following error:
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
The following limitations are generally applicable to online DDL operations on large tables that involve rebuilding the table:
There is no mechanism to pause an online DDL operation or to throttle I/O or CPU usage for an online DDL operation.
Progress monitoring capability for online DDL operations is limited until MySQL 5.7.6, which introduces Performance Schema stage events for monitoring
ALTER TABLEprogress. See Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema.
Rollback of an online DDL operation can be expensive should the operation fail.
Long running online DDL operations can cause replication lag. An online DDL operation must finish running on the master before it is run on the slave. Also, DML that was processed concurrently on the master is only processed on the slave after the DDL operation on the slave is completed.
For additional information related to running online DDL operations on large tables, see Section 14.13.2, “Online DDL Performance and Concurrency”.