MySQL Drop Federated Table When Remote Server is Down
0
votes
1
answer
139
views
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*":
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.
Asked by MadHatter
(101 rep)
Nov 28, 2023, 10:26 PM
Last activity: Jul 21, 2025, 09:02 AM
Last activity: Jul 21, 2025, 09:02 AM