How to remove a innodb metadata lock with thread_id 0 in mariadb / mysql?
0
votes
2
answers
1730
views
Today, suddenly our mysql database server got down. Few minutes after restarting the server went down again, and again. To see what is happening, we temporarily put our application in maintenance mode and found that the database didn't go down anymore. This helped me to find out what was happening;
Every
or
on one specific table caused lots of
queries (on that same table) to "Waiting for table metadata lock". As a result of that many open connections already appear within a few seconds and finally the server is crashing.
I couldn't understand why a simple app.notes SET user_id=5 WHERE note_id=1
query took so long and even after 10 minutes waiting wasn't finished. The PROCESSLIST
state on that query was still Executing.
Again, I restarted the server and after some research, I found that there is one metadata lock on that specific table
MariaDB [(none)]> SELECT * FROM information_schema.METADATA_LOCK_INFO;
+-----------+------------+---------------+---------------------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+------------+---------------+---------------------+--------------+------------+
| 0 | MDL_SHARED | NULL | Table metadata lock | app | notes |
+-----------+------------+---------------+---------------------+--------------+------------+
1 row in set (0.000 sec)
But since that
is 0, I can't find anything how to delete the lock.
So where I am at the moment; the server is rebooted, MySQL is started, no query has been executed yet on that specific table and that one metalock on app.notes is still there.
All these have empty results:
Transaction section in ENGINE innodb STATUS;
* FROM INNODB_LOCK_WAITS;
* FROM INNODB_LOCKS;
* FROM INNODB_TRX;
FULL PROCESSLIST;
I've already tried to TABLE
but that also results in "Waiting for table metadata lock".
What can I do or try to make the lock go away??
Asked by Ricky Heijnen
(11 rep)
Sep 10, 2021, 12:43 AM
Last activity: Jul 15, 2024, 02:52 AM
Last activity: Jul 15, 2024, 02:52 AM