0

We have a production database (MySQL 8.0) that has a federated table in it. The remote server had an IP address update, and whenever a client opens this DB, it takes forever (~5 minutes) since table information can not be retrieved... Our fix is to remove the table as it is no longer needed.

Unfortunately all the following have failed with forever running queries.
Looking at the process list they are in a state of "Waiting for table metadata lock":

1. DROP TABLE db.myfedtable;
2. ALTER TABLE db.myfedtable CONNECTION = 'valid connection string for updated IP';
3. ALTER TABLE db.myfedtable ENGINE = InnoDB;

We have also verified no processes or queries are trying to run against this table. We have tried restarting MySQL since the server connection has been lost. Our next steps is to try and setup a firewall to re-route traffic to the new server IP just so we can run a drop statement. Any ideas would be helpful here.

1 Answer 1

0

After additional troubleshooting, we discovered the following that resulted in us fixing the issue:

  1. The process list had no information about our federated table.
  2. Running the following query showed a high priority metadata lock on the table.
SELECT OBJECT_TYPE,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       LOCK_TYPE,
       LOCK_STATUS,
       THREAD_ID,
       PROCESSLIST_ID,
       PROCESSLIST_INFO
FROM performance_schema.metadata_locks
INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID()

query result

  1. Using the information from #2 above, we realized our server monitoring system (Percona PMM) was attempting to gather table data for every database every x minutes, triggering a never ending metadata lock since it took longer to time out then to run the next check.

  2. Running systemctl stop pmm-agent to stop our collection agent, waiting for the above process to time out, then dropping the table using standard DROP TABLE db.table resulted in success.

Hopefully this helps someone with a similar problem of a process not directly accessing the table but still holding locks.

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.