Here is the situation:
- I was running a very complex query on a database which was expected to execute within a few hours over night(server configuration is not good and data set is large). This query is using table
- while the query is being executed, application A (running on the Server) executes
DROP TABLE tblA.
- This DDL Statement locks the metadata for
tblAsince table is in use by another transaction (my query).
- Application B comes in and wants to perform an update on
- Update statement does not execute since the
DROP TABLE tblAholds the metadata lock.
- Application B is multithread and executes another update on
- Application B keep doing that until
max_connectionslimit is met and I receive
Too Many Connectionerror every time I try to connect to server.
- Right at this moment the application I was using to execute the query (HeidiSQL) stop working and crashes.
- I just noticed all the connection to server is through Root user from local host so there is no way to log in to MySQL to kill the DDL statement.
Is there anyway to end this without having to kill or stop MySQL Server?
I already tried killing Application A and B but the process still remains in MySQL queue. It also sounds like my query is not willing to end any time soon.