0

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.

2
  • The title of your post does not agree with the question you actually pose at the end of your post. You should make clear what you want to ask.
    – miracle173
    Jun 18, 2015 at 7:23
  • it does agree actually. my question is just broader than the title. title is a solution to the problem which i am hoping to find but any other alternative is also welcome.
    – Masoud
    Jun 18, 2015 at 8:31

2 Answers 2

1

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.

-1

Try "mysqladmin flush-hosts" on the mysql server.

4
  • Are you sure this works? what else does it do? it's a production server, I cannot risk it. mysqladmin still need a login and too many connection error still shows up!
    – Masoud
    Jun 17, 2015 at 4:11
  • As far as I understand, it only drops dead connections, nothing more. Jun 17, 2015 at 4:22
  • It does nothing of the sort. It's harmless, but all it does is clear the host cache, primarily used to relieve the condition where a host is being denied access because of too many errors ... Not too many connections. dev.mysql.com/doc/refman/5.6/en/flush.html Jun 20, 2015 at 0:25

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.