Unable to Execute and Cancel DDL Operations

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

Original topic: DDL操作无法执行和取消

| username: seiang

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] v6.5.1
[Encountered Problem: Problem Phenomenon and Impact]

The table has approximately 1500 rows of data. When executing the alter operation to modify the table column type, it gets stuck;
ALTER TABLE xxxx MODIFY COLUMN cost decimal(10,2) DEFAULT 0.00 NOT NULL;

It did not complete for a long time, so I executed admin cancel ddl jobs 78841; to cancel the DDL task, and the result returned as successful.
mysql> admin cancel ddl jobs 78841;
±-------±-----------+
| JOB_ID | RESULT |
±-------±-----------+
| 78841 | successful |
±-------±-----------+
1 row in set (0.00 sec)

The DDL job status changed to cancelling.

The session thread executing the DDL operation also cannot be killed.

kill tidb 4565996912247287715;

[Attachment: Screenshot/Log/Monitoring]

| username: dba-kit | Original post link

It is highly likely that the issue is caused by metalock, and it will resolve itself after some time. I encountered this once before, and the status returned to normal after waiting for 3-4 hours. Fortunately, it does not affect DML writes and reads.

| username: zhanggame1 | Original post link

Just restart all TiDB server components.

| username: TiDBer_oHSwKxOH | Original post link

Simply restart the TiDB server on each TiDB node: service tidb stop and service tidb start.

| username: dba-kit | Original post link

It is recommended to set tidb_enable_metadata_lock to OFF in version 6.5.

| username: h5n1 | Original post link

  1. Check mysql.tidb_mdl_view to kill blocking processes.
  2. Shut down all TiDB servers, then restart them.
| username: 像风一样的男子 | Original post link

Recently, I’ve seen a lot of DDL blockages, all of which were resolved by restarting TiDB.

| username: seiang | Original post link

Checked mysql.tidb_mdl_view, did not see any blocked threads.

| username: seiang | Original post link

Fortunately, it does not affect DML writes and reads.

Currently, checking the mysql.tidb_mdl_view table, there are no blocked threads.

| username: seiang | Original post link

Do I need to shut down all TiDB servers and then restart them? Can I restart each TiDB server individually?

| username: h5n1 | Original post link

Closing all of them at once is generally more effective than closing them one by one.

| username: zhanggame1 | Original post link

There is a simple way to restart:
tiup cluster restart <cluster_name> -R tidb

| username: seiang | Original post link

Okay, thank you.

| username: 天蓝色的小九 | Original post link

Restart the TiDB server.

| username: redgame | Original post link

Everyone’s big move: restart.

| username: 昵称想不起来了 | Original post link

Does the reboot method work?

| username: seiang | Original post link

Useful, but it needs to be completely shut down and restarted.

| username: 昵称想不起来了 | Original post link

Got it, thanks.

| username: Fly-bird | Original post link

The almighty reboot method