Native DDL Tools

COPY Algorithm

1. It creates a temporary table based on the original table definition.
2. It adds a write lock to the original table (DML is prohibited).
3. It executes DDL statements in the temporary table created in 1.
4. It copies the data in the original table to the temporary table.
5. It releases the write lock of the original table.
6. It deletes the original table and renames the temporary table as the original table.

When the COPY algorithm is used, the table needs to be locked and DML write operations are forbidden. If Lock is set to Shared, read operations are allowed but write operations are not allowed. If Lock is set to Exclusive, both read and write operations are forbidden. This algorithm can be used in almost all DDL scenarios.

INPLACE Algorithm

INPLACE modifies the original table without generating a temporary table or copying data. There are two types:

• rebuild: The table needs to be rebuilt (with the clustered index reorganized), for example, optimizing a table, adding an index, adding or deleting a column, and modifying the NULL/NOT NULL attribute of a column.
• no-rebuild: The table does not need to be rebuilt. Only the metadata of the table needs to be modified, for example, deleting an index, changing a column name, changing the default value of a column, and changing the auto-increment value of a column.

If rebuild is used, the DML statements to be executed during DDL execution are cached. After the DDL operations are complete, the DML statements are applied to tables. Since metadata write locks will be degraded to metadata read locks during data copy, DML operations are almost not blocked during DDL execution.

Constraints on the INPLACE Algorithm

The INPLACE algorithm supports most DDL operations. But it has the following constraints, so in a few scenarios only the COPY algorithm can be used:

• It does not allow you to delete a primary key or add two primary keys at the same time.
• It does not allow you to change the data types of fields.
• It does not allow you to extend the length of the VARCHAR columns from less than 256 bits to more than 256 bits, because doing so will change the occupied space from 1 byte to 2 bytes. It does not allow you to reduce the length of the VARCHAR columns.
• It does not allow you to change the order of virtual columns or stored columns.
• It does not allow you to add foreign key constraints when foreign_key_checks is set to 1.
• It does not allow you to partition tables, or optimize or delete partitions.