Maintenance of databases or servers is quite often performed by database administrators at night. But these routines sometimes get blocked by long-running queries or applications that hang onto locks much longer than expected.
Regularly, priority is given to the application, and maintenance routines are often canceled in order not to interfere with the application. But sometimes it happens that you need to kill a MySQL query that is time-consuming and hasn’t been completed by a certain time. In this case, you might need a quick way to terminate the connection involved.
In this article, we aim to consider two ways to solve the above-mentioned problem. We’ll use dbForge Studio for MySQL for that purpose.
Method 1. Killing MySQL connections based on a user login using an Event
The first method comprises creating a special MySQL Event. When you create an event, you create a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time.
Use the following script to create an event on a database:
DELIMITER $
CREATE EVENT kill_process_event
ON SCHEDULE EVERY '1' DAY
STARTS '2019-03-20 08:45:30'
DO
BEGIN
DECLARE kill_done int;
DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
BEGIN
DECLARE cursor_ID int;
DECLARE cursor_i CURSOR FOR
SELECT
id
FROM information_schema.PROCESSLIST
WHERE USER LIKE 'test%'
AND ID != CONNECTION_ID();
OPEN cursor_i;
read_loop:
LOOP
FETCH cursor_i INTO cursor_ID;
KILL CONNECTION cursor_ID;
IF kill_done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE cursor_i;
END;
END
$
ALTER EVENT kill_process_event
ENABLE
$ DELIMITER ;Next steps
• Assign your own values to the required parameters. Namely, the schedule, time and user login.
• Schedule the Event
• Set the server variable event_scheduler = ON
How to schedule the Event
To schedule the Event, right-click it in the Object Explorer and select Edit Event from the pop-up window. In the wizard that opens, you can configure Start and End date, Recurrence, and other relevant options for the Event.
In our example, we want to kill the connection for the test% user on the sakila database. The picture below shows the output for MySQL query SHOW FULL PROCESSLIST before implementing actions described in Method 1.
After executing the preceding script all the connections with user login (‘test%’) will be killed.
The drawback of the method is that it is necessary to change the object (Event) when changing the conditions for selecting session ID.
Alternatively, you can create a Stored Procedure and an Event to call this procedure.
Terminating MySQL connections based on a user login using an Event calling a Stored Procedure
Basically, this method is the extension of the proceeding one. It involves creating a MySQL procedure and then scheduling the event to call this procedure.
- First, you need to create a Stored Procedure on a database:
DELIMITER $
CREATE PROCEDURE kill_process_proc()
BEGIN
DECLARE kill_done int;
DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
BEGIN
DECLARE cursor_ID int;
DECLARE cursor_i CURSOR FOR
SELECT
id
FROM information_schema.PROCESSLIST
WHERE USER LIKE 'test%'
AND ID != CONNECTION_ID();
OPEN cursor_i;
read_loop:
LOOP
FETCH cursor_i INTO cursor_ID;
KILL CONNECTION cursor_ID;
IF kill_done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE cursor_i;
END;
END$
DELIMITER ;You need to assign the needed value to the USER parameter. In our example, it is ‘test%’.
2. Next, having the procedure ready, you can create an event to call it and execute it on schedule. Please, consider the following script:
CREATE DEFINER = 'root'@'localhost'
EVENT sakila.kill_process_event
ON SCHEDULE EVERY '1' DAY
STARTS '2019-03-19 14:53:00'
DO
BEGIN
CALL kill_process_proc();
END;
ALTER EVENT sakila.kill_process_event
ENABLE;3. Next steps
• Assign your own values to the required parameters. Namely, the schedule, time, database and user login.
• Schedule the event
• Set the server variable event_scheduler = ON
After executing the preceding steps all the connections with a user login (‘test%’) will be killed.
Method 2. Creating a bat file to launch MySQL client and a procedure with parameters
You can also create an executable BAT file to launch MySQL client and a procedure with parameters via the command-line interface.
First, you need to create a procedure with parameters. To do this, please use the following script:
DELIMITER $
CREATE PROCEDURE kill_process_param(IN user VARCHAR(255))
BEGIN
DECLARE kill_done int;
DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
BEGIN
DECLARE cursor_ID int;
DECLARE cursor_i CURSOR FOR
SELECT
id
FROM information_schema.PROCESSLIST
WHERE USER LIKE user
AND ID != CONNECTION_ID();
OPEN cursor_i;
read_loop:
LOOP
FETCH cursor_i INTO cursor_ID;
KILL CONNECTION cursor_ID;
IF kill_done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE cursor_i;
END;
END$
DELIMITER ;The corresponding procedure will appear on the database. Let’s have a look at it with the help of dbForge Studio for MySQL.
Next, you need to create a BAT file.
To create a BAT file:
1. Open a plain text editor such as Notepad.
2. Type in the following code:
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" --user=<user> --password=<password> --host=<host> --port=<port> --database=<database_name> --execute="CALL kill_process_param('%1')"
3. Assign your own values to the user, password, host, port, and database name parameters.
4. Save the file with the .bat extension.
Now, you need to run the bat-file with the parameter.
All the connections with user login (‘test%’) will be killed as a result.
Conclusion
Knowing how to kill a process in MySQL Server can be a handy tool to have in your toolbox. However, we recommend using the given methods sparingly.
