Investigation of MySQL 5.7 Opening‑Table State Caused by Federated Engine Queries

The author, a DBA from the iKangSheng team, reports a MySQL 5.7.32 instance whose monitoring thread is constantly stuck in the Opening table state while querying information_schema.tables .

Initial checks show a moderate number of opened tables, reasonable MySQL parameters, and no obvious ulimit limits; the MySQL error log contains no related warnings.

Using the pstack tool, the stack trace reveals that the query invokes the Federated storage engine, which attempts to access remote tables.

Two tables using the Federated engine are identified, and a telnet test confirms that the remote server’s IP and port are unreachable.

The hypothesis is that when the monitoring thread queries information_schema.tables , it must retrieve metadata for Federated tables, which requires a network connection to the remote server; failure to connect leaves the thread in the Opening table state.

To validate this, the author conducts an experiment: enable the Federated engine, create a Federated table pointing to a non‑existent server, query information_schema.tables , and observe that the thread remains in the Opening table state.

The experiment confirms the hypothesis, demonstrating that unreachable Federated tables can cause monitoring threads to hang.

Keywords: #故障处理#, Federated storage engine.