Log an Online DDL operation

Log an Online DDL operation

To support the user account deletion function, a field deleted needs to be added to the user table.

1. Environment

Database: Mysql5.6

Operated table user: the order of magnitude is 100w, and there are more than 200 foreign keys

Action: alter table user add deleted boolean NOT NULL default false comment '用户注销标识' , algorithm=inplace, lock=none;

Second, the execution process analysis

After Mysql5.6, mysql supports Online DDL operations.

Online DDL Support for Column Operations

OperationIn PlaceRebuilds TablePermits Concurrent DMLOnly Modifies Metadata
Adding a columnYesYesYes*No
Dropping a columnYesYesYesNo
Renaming a columnYesNoYes*Yes
Reordering columnsYesYesYesNo
Setting a column default valueYesNoYesYes
Changing the column data typeNoYesNoNo
Dropping the column default valueYesNoYesYes
Changing the auto-increment valueYesNoYesNo*
Making a column NULLYesYes*YesNo
Making a column NOT NULLYes*Yes*YesNo
Modifying the definition of an ENUM or SET columnYesNoYesYes

As shown in the figure, the entire process of adding a column is performed as follows:

  • Initialization: To create the table structure of the temporary table, obtain the exclusive lock of MDL
  • Execution: According to the parameters algorithm=inplace, lock=none, the MDL lock is downgraded to a shared lock for data copying
  • Commit: MDL locks need to be upgraded to exclusive locks due to incremental backups and temporary table renaming involved

After analysis, it is believed that the entire process only needs to use the MDL exclusive lock (affecting the online) in the very short process of initialization and submission, so the operation is attempted directly online.

3. Problems encountered

​ The MDL exclusive lock needs to be used in the initialization and submission stages, and if the DDL operation has not acquired the MDL lock (the default time-out time for acquiring the MDL lock is one year), it will cause the Waiting for table metadata lock state, which will also block the following All operations on the user table (including select). Later you will see that the connection is full, service 502:

After encountering such a problem, in order not to affect the online, I tried the tool later.

4. Tool try

​ The tools involve pt-online-schema-change, gh-ost and Alibaba Cloud lock-free DDL. The three tools are similar in that they all use a temporary table, copy the data of the original table to the temporary table, and finally rename the temporary table to replace the original table. The difference is that in incremental synchronization, one uses triggers and the other uses binlog logs. However, in terms of processing foreign keys, pt-online-schema-change uses to delete and rebuild foreign keys, while gh-ost and Alibaba Cloud lock-free DDL do not support the change of primary table foreign keys. eg: Failed attempts of Alibaba Cloud’s lock-free DDL

Therefore, it is not suitable to use tools for DDL operations.

Five, Online DDL attempt

After testing 30w data locally, it only takes 440ms to add a new column, and then try to select the operation of online ddl:

  1. Set the session level to obtain the MDL lock waiting time to avoid blocking other threads for a long time $ set lock_wait_timeout=10;
  2. kill the corresponding thread and transaction
  3. Perform DDL operations:
  4. result:

The above is the result of the operation after the server is stopped, of which 2 operations do not have an ID that requires kill after the server is stopped.