MySQL Bugs: #96132: analyze table cause Waiting for table flush

Bug #96132	analyze table cause Waiting for table flush
Submitted:	9 Jul 2019 1:39	Modified:	9 Jul 2019 15:37
Reporter:	Chenliang Xu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
here is the situation:
session1 running a slow query
session2 analyze table the same table as session1
other sessions do select will blocked and state is Waiting for table flush;

During the analysis, the table is locked with a read lock for InnoDB and MyISAM.

How to repeat:
(root:db1@xucl:09:22:21)[xucl]> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| c1    | int(10) unsigned | NO   | PRI | NULL    |       |
| c2    | int(10) unsigned | NO   |     | 0       |       |
| c3    | int(10) unsigned | NO   |     | 0       |       |
| c4    | int(10) unsigned | NO   |     | 0       |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

(root:db1@xucl:09:22:28)[xucl]> insert into t1 values(1,1,1,1);
Query OK, 1 row affected (0.04 sec)

session1: select *,sleep(120) from t1;
session2: analyze table t1;
session3: select * from t1;
session4: show processlist;

(root:db1@xucl:09:21:59)[(none)]> show processlist;
+----+-----------------+-----------+------+---------+---------+-------------------------+-----------------------------+
| Id | User            | Host      | db   | Command | Time    | State                   | Info                        |
+----+-----------------+-----------+------+---------+---------+-------------------------+-----------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 1590054 | Waiting on empty queue  | NULL                        |
| 12 | root            | localhost | xucl | Sleep   |      82 |                         | NULL                        |
| 13 | root            | localhost | xucl | Query   |      97 | User sleep              | select *,sleep(120) from t1 |
| 14 | root            | localhost | xucl | Query   |       9 | Waiting for table flush | select * from t1            |
| 15 | root            | localhost | NULL | Query   |       0 | starting                | show processlist            |
+----+-----------------+-----------+------+---------+---------+-------------------------+-----------------------------+
5 rows in set (0.01 sec)

Hi Mr. Xu,

Thank you for your bug report.

However, this is not a bug. Two queries are waiting for MDL lock set by ANALYZE command.

This is all described in our Reference Manual.

I would say this a bug. We even have a feature request ticket to fix it - https://bugs.mysql.com/bug.php?id=87065

Hi,

We already replied on the feature request.

Also, feature requests are not bugs and this report is reported as a bug !!!!!