MySQL use database时hang住怎么办？

现象：

在会话一执行了一个事务，但是没有执行commit操作。mysql> use test;mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t22;Empty set (0.00 sec)这里并没有执行commit或者rollback操作。

在会话二执行了一个drop table语句。mysql> use test;
mysql> drop table t22;  -----这里会hang住
此时元数据锁信息如下mysql> select * from performance_schema.metadata_locks;+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+--------| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_E+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+--------| GLOBAL      | NULL               | NULL           | NULL        |       140114987430048 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5590  |              44 || BACKUP LOCK | NULL               | NULL           | NULL        |       140114986626928 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5597  |              44 || SCHEMA      | test               | NULL           | NULL        |       140114987147792 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5577  |              44 || TABLE       | test               | t22            | NULL        |       140114987147552 | EXCLUSIVE           | TRANSACTION   | PENDING     | sql_parse.cc:6164 |              44 || TABLE       | test               | t22            | NULL        |       140115553689936 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6164 |              40 || TABLE       | performance_schema | metadata_locks | NULL        |       140115550395792 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6164 |              40 |+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+--------6 rows in set (0.00 sec)


在会话三执行了一个use test语句。mysql> use test;   -----这里会hang住，无法进入数据库
此时元数据锁信息如下mysql> select * from performance_schema.metadata_locks;+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+--------| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_E+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+--------| GLOBAL      | NULL               | NULL           | NULL        |       140114987430048 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5590  |              44 || BACKUP LOCK | NULL               | NULL           | NULL        |       140114986626928 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5597  |              44 || SCHEMA      | test               | NULL           | NULL        |       140114987147792 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5577  |              44 || TABLE       | test               | t22            | NULL        |       140114987147552 | EXCLUSIVE           | TRANSACTION   | PENDING     | sql_parse.cc:6164 |              44 || TABLE       | test               | t22            | NULL        |       140115187975408 | SHARED_READ         | TRANSACTION   | PENDING     | sql_parse.cc:6164 |              49 || TABLE       | test               | t22            | NULL        |       140115553689936 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6164 |              40 || TABLE       | performance_schema | metadata_locks | NULL        |       140115550395792 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6164 |              40 |+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+--------7 rows in set (0.00 sec)

解决：

观察到进入数据库时都会显示一句话：

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A

在客户端登录数据库时添加-A参数

[mysql@yeshuo ~]$ mysql -uroot -p -AEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 16Server version: 8.4.2 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;Database changed

结束。