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

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes	Yes	Yes*	No
Dropping a column	Yes	Yes	Yes	No
Renaming a column	Yes	No	Yes*	Yes
Reordering columns	Yes	Yes	Yes	No
Setting a column default value	Yes	No	Yes	Yes
Changing the column data type	No	Yes	No	No
Dropping the column default value	Yes	No	Yes	Yes
Changing the auto-increment value	Yes	No	Yes	No*
Making a column NULL	Yes	Yes*	Yes	No
Making a column NOT NULL	Yes*	Yes*	Yes	No
Modifying the definition of an ENUM or SET column	Yes	No	Yes	Yes

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;
   
   # 在10s内获取不到MDL锁，直接退出 alter 操作
   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2. kill the corresponding thread and transaction

    $ select group_concat(stat separator ' ') from (select concat('kill query ',id,';') as stat from information_schema.processlist  where command != 'Sleep' and Time > 5 order by Time desc) as stats;
   
   +-------------------------------------+
   | group_concat(stat separator ' ')    |
   +-------------------------------------+
   | kill query 42510; kill query 42514; |
   +-------------------------------------+
   1 row in set (0.00 sec)
   
   
   $ select group_concat(stat separator ' ') from (select concat('kill ',trx_mysql_thread_id,';')   as stat from information_schema.innodb_trx order by trx_started desc) as stats;
   
   +-------------------------------------------------------------------------+
   | group_concat(stat separator ' ')                                        |
   +-------------------------------------------------------------------------+
   | kill 42436; kill 42435; kill 42521; kill 42511; kill 42510; kill 42483; |
   +-------------------------------------------------------------------------+
   1 row in set (0.01 sec)

3. Perform DDL operations:

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

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.

6. References

​InnoDB and Online DDL