DDL Cancellation Issue

translator_bot · June 21, 2024, 9:15pm

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: ddl取消问题

| username: Running

I added an index to a dataset of about 100 million records. It has been running for an hour without finishing, and since it affects business operations, I canceled it. However, it has been 24 hours and the cancellation still hasn’t completed. Is there any good method to forcefully terminate it?

translator_bot · June 21, 2024, 9:15pm

| username: Billmay表妹 | Original post link

In TiDB, if the process of adding an index takes a long time, you can try the following methods to forcibly terminate the operation:

First, use the SHOW PROCESSLIST command to view the current process list of TiDB and find the process that is executing the index addition. You can determine whether it is an index addition operation based on the information in the INFO column.
Obtain the process ID of the index addition (i.e., the value in the Id column).
Use the KILL command to terminate the process. For example, if the process ID is 12345, you can run the following command:
```
KILL 12345;
```
This will immediately terminate the process.

Please note that forcibly terminating the index addition operation may lead to data inconsistency or incorrect index status. After terminating the operation, it is recommended to perform a full data synchronization or re-execute the index addition operation to ensure data consistency and correct indexing.

Additionally, if the process of adding an index takes a long time, you can consider optimizing the way the index is added, such as using ALGORITHM=INPLACE or ALGORITHM=COPY to control the index construction method, thereby reducing the impact on business operations. Specific optimization methods can be found in the relevant sections of the TiDB official documentation.