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.

  • 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 '15 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 '15 at 8:31
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.

  • 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 '15 at 4:11
  • As far as I understand, it only drops dead connections, nothing more. – aldwinaldwin Jun 17 '15 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 – Michael - sqlbot Jun 20 '15 at 0:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.