Killing a process without login to MySQL 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 tblA
while the query is being executed, application A (running on the Server) executes  DROP TABLE tblA.
This DDL Statement locks the metadata for tblA since table is in use by another transaction (my query).
Application B comes in and wants to perform an update on tblA.
Update statement does not execute since the DROP TABLE tblA holds the metadata lock.
Application B is multithread and executes another update on tblA
Application B keep doing that until max_connections limit is met and I receive Too Many Connection error 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.


Question is:

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.
For cases like this, there is "one extra connection available".  Anyone with SUPER priv (eg. "root") can get in to kill the DROP, which would have uncorked the mess.

However, a prerequisite for doing that is to not have users and applications log in as "root".  Instead they should have their own logins, preferable (for security reasons) limited to the database that they need access to.

Reserve "root" for 'admin' actions such as the one you needed in this case.