How can I stop running a MySQL query?


Before stopping a running query of MySQL, first we need to see how many processes are running with the help of show command.

The query for that is given as follows −

mysql> show processlist;

After executing the above query, we will get the output with some id’s. This is given as follows −

+----+-----------------+-----------------+----------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+----------+---------+-------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 71034 | Waiting on empty queue |
NULL |
| 8 | Manish | localhost:53496 | business | Query | 0 | starting |show processlist|
+----+-----------------+-----------------+----------+---------+-------+------------------------+------------------+
2 rows in set (0.00 sec)

As seen from the above output, the db currently in use is ‘business’. and its id is 8. To stop the query the command call can be used with the given id that is in process list. The syntax for that is as follows −

call mysql.rds_kill(valueOfGivenIdInProcesslist);

Now, the above syntax is applied to the query and valueOfGivenIdInProcesslist is put as 8. This is shown below −

mysql> CALL mysql.rds_kill(8);

After executing the above query, the following output is obtained −

ERROR 2013 (HY000): Lost connection to MySQL server during query

This happens as the above query stops running. The use command is used to check if it has stopped or not. The syntax for that is as follows −

use yourDatabaseName;

The above syntax is applied to the mydatabase name ‘business’ in the system. The query is as follows −

mysql> use business;

After executing the above query, the following output is obtained −

No connection. Trying to reconnect...
Connection id: 10
Current database: *** NONE ***



Database changed
mysql>

So, it is clear that the query has stopped because the message is coming “Trying to reconnect…” in MySQL. And it is also showing that the current database is “none”. After some time the connection with the database is resumed.

Updated on: 24-Jun-2020

827 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements