Mysql – Killing a process without login to MySQL

MySQLmysql-5.5windows-server

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.

Best Answer

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.

Related Solutions

MySQL – Reasons for Long Process Killing Time

The reason the kill takes so long is most likely due to the rollbacks issued by the innodb transaction. From InnoDB performance tips:

Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times as long to perform as the corresponding insert. Killing the database process does not help because the rollback starts again on server startup.

Edit: The innodb force recovery methods might be of use to you (glad you did this on a test environment)

You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

And next time, insert a small subset of data each time. 10 million rows shouldn't take that long to insert, but there could be multiple reasons it does. Without knowing your environment I can't offer advice on that.

Mysql – Cannot write to thesql database while select query in process

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before

Best Answer

Related Solutions

MySQL – Reasons for Long Process Killing Time

Mysql – Cannot write to thesql database while select query in process

Related Question