mysql 删除表卡死，或是截断（truncate）卡死解决办法

利用工具进行truncate表的时候，一直运行，运行了十几分钟也没有成功。中止之后再运行也是一样。但是删除表的数据以及查询表数据都是可以的。猜测是锁死了。

使用

show processlist;

发现Waiting for table metadata lock 问题；

mysql> show processlist;+-------+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------------------------------------+| ID    | USER            | HOST      | DB   | COMMAND | TIME   | STATE                           | INFO          |+-------+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------------------------------------+|     1 | event_scheduler | localhost | NULL | Daemon  | 365869 | Waiting on empty queue          | NULL          || 13040 | root            | localhost | NULL | Query   |      0 | executing                       | select * from information_schema.processlist where command != 'sleep' || 13044 | root            | localhost | NULL | Query   |      4 | Waiting for table metadata lock | truncate table test_;               |+-------+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------------------------------------+

1. 尝试利用 kill 杀死 Waiting for table metadata lock 的线程，但是重新执行 truncate 操作 仍然会报 这个问题。

mysql> kill 13044;

2. 重启一下mysql服务，再去执行命令，还是会报这个问题。。。😒😒😒
3. 执行命令查询死锁 SELECT * FROM information_schema.INNODB_TRX/G

mysql> SELECT * FROM information_schema.INNODB_TRX; （这里如果感觉用命令行的时候看不清排版，在后面加/G，变成竖向排版输出）+------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+| trx_id     | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight |+------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+| 2234640445 | RUNNING   | 2023-03-09 12:26:38 | NULL                  | NULL             |          5 |                   0 | NULL      | NULL                |                 0 |                 2 |                3 |                  1346 |               1 |                 2 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 | NULL                |+------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+

发现trx_mysql_thread_id = 0的不能通过 kill 来进行杀死。如果不为0可直接kill。
如果不能kill，我们需要进行事务回滚操作。

1. 先使用 xa recover 获取XA的事务信息。

mysql> xa recover;+----------+--------------+--------------+-------------------------------------------+| formatID | gtrid_length | bqual_length | data          |+----------+--------------+--------------+-------------------------------------------+|        1 |           39 |            2 | kdisieus-783s-780e-kdsk-bf1eafe7sdd6:5434 |+----------+--------------+--------------+-------------------------------------------+

2. 根据事务信息，进行回滚操作。按照以下格式进行带入# xa rollback ‘left(data,gtrid_length)’，‘substr(data,gtrid_length+1,bqual_length)’, formatID;

mysql> xa rollback ‘kdisieuns-783s-780e-kdsk-bf1eafe7sdd6:54’,‘34’, 1;

3. 再次执行truncate ，执行成功。

插叙：中间还去mysql的数据存储位置，将这个表直接删除了。但是再去创建这个表的时候发现提示表已经存在。这里猜测是可能是因为事务还在运行，在某个地方还缓存着这个表。
最后将锁死解决之后，查询这个表已经不存在了。但是还是不能创建这个表。报了1030 (HY000): Got error 168 from storage engine错误。
最终从别的地方又将这两个文件导入进来了，在进行drop，然后在create，解决完毕。

可能还有其它办法，欢迎进行评论。
这里不建议修改mysql的一些参数，毕竟在公司的话，参数也都是经过DBA精心设计的。（当然了也有可能都是默认的）。

来源地址：https://blog.csdn.net/tianrun1110/article/details/129435797