0

We have a large table in a database that a user tried to run DELETE * FROM table on. It was still running the next day so we killed the process.

I tried renaming, dropping and truncating the affected table, and even the whole database but any query ends up Waiting for table metadata lock. I can query from the table though.

All info I can find suggests there will be a process somewhere that is still going, but I've rebooted the server and the issue persists.

It's a staging server with multiple other databases so it's reasonably busy, but I don't see problems elsewhere and I can add and drop other databases. It's just this one database that seems to have the problem.

After enabling the performance schema I see some entries in performance_schema.metadata_locks table like the following:

table of metadata_locks

I'm not sure what the dictionary_impl.cc:434 refers to but I don't see anything in INFORMATION_SCHEMA.PROCESSLIST relating to this.

The tables referenced in the locks table are all from the database I'm trying to drop.

5
  • truncate the tables before dropping them
    – nbk
    Mar 26 at 15:21
  • Updated the question, it's now not possible to truncate the table
    – George
    Mar 26 at 16:18
  • restart the ec2 from the panel, somthing is iin a deadlock
    – nbk
    Mar 26 at 16:43
  • Have you checked: select * from performance_schema.metadata_locks;? That PS view should be enabled by default on MySQL 8, but if it isn't, that's an issue for AWS support to answer. Mar 26 at 17:17
  • I've enabled the performance_schema now, but not sure how to interpret the results…
    – George
    Mar 26 at 18:12

1 Answer 1

0

I had wrongly assumed that metadata locks would not survive a server restart. After reading the manual on Metadata locking in MySQL8 and confirming there were no active processes trying to maintain a lock on the table it was simply a case of running:

UNLOCK TABLES;

Once that was done, I was able to run queries again on the table and database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.