MySQL Bugs: #106608: Queey hangs with "Waiting for table metadata lock"

Bug #106608	Queey hangs with "Waiting for table metadata lock"
Submitted:	1 Mar 2022 7:36	Modified:	15 Mar 2022 17:37
Reporter:	Abhay Singh	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.17 MySQL Community Server	OS:	Linux (openSUSE Leap 15.1)
Assigned to:		CPU Architecture:	x86 (Intel(R) Xeon(R) Gold 6126 CPU @ 2.60GHz)
Tags:	metadata locking, myisam tables, query hang, storage_engine, Table Locks

Description:
DML query is stuck with status "Waiting for table metadata lock", don't see anything in MySQL log for this, then the engine of the table is MyISAM

| 14569 | cubot       | localhost           | mutual   | Query   | 7938   | Waiting for table metadata lock | Truncate Table MST2_S_INVESTOR | 0.000    |
| 14588 | cubot       | localhost           | mutual   | Query   | 5300   | Waiting for table metadata lock | Truncate Table MST2_S_INVESTOR | 0.000    |

no other query running on this table that might be causing this lock. or even on other query is running at all. A similar problem happens with the drop table also.

How to repeat:
For me it comes anyway after running MySQL for more than just 24 hours.

Hi Mr. Singh,

Thank you very much for your bug report.

However, this is not a bug.

MyISAM works with table locking only, so if there is a single connection running a query, DML or DDL on one table, all other SQL statements involving that table have to wait that other concurrent statements finish.

This is expected behaviour which is explained in our Reference Manual.

Not a bug.

Dear Team,

I know how MySQL locking works, as I mentioned there are no other queries running over this table, it's just one truncate/drop query. if you look at mysqladmin proc shared there are two queries running and both are trying to Truncate the table, there are no other queries on this table, Also check the time since these queries are running, there is the diff of more than 2000 seconds, truncate table doesn't take this much of time.
One more thing here, it's "Metadata Lock" and not "Table level Lock", I believe when there multiple DML queries runs on a table there is "Table level lock".

Hi Mr. Singh,

We do not see the whole process list, so we can't comment on what you are saying. It is enough that a simple SELECT works on that query, so that everything stalls.

Furthermore, you are using 8.0.17, which is not longer supported. Current release is 8.0.28.

Not a bug.

Here is the full processes list there is no other query running here, only one query and that is in wait state

mysqladmin proc

Attachment: mysqladminproc.txt (text/plain), 11.58 KiB.

Hi,

First of all , you are using 8.0.17, which is an ancient release of 8.0. Thousands of bugs have been fixed between that release and 8.0.28.

Second, you did not run the processlist as a root. Third, you could be running some external processing on the same table.

Last, but not least, we can not process bug reports for which we do not have a fully repeatable test case and you have not provided one.