How can I stop a running MySQL query?
MysqlKillProcesslistMysql Problem Overview
I connect to mysql from my Linux shell. Every now and then I run a SELECT query that is too big. It prints and prints and I already know this is not what I meant. I would like to stop the query.
Hitting Ctrl+C (a couple of times) kills mysql completely and takes me back to shell, so I have to reconnect.
Is it possible to stop a query without killing mysql itself?
Mysql Solutions
Solution 1 - Mysql
mysql> show processlist;
+----+------+-----------+-----+---------+------+---------------------+------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+-----+---------+------+---------------------+------------------------------+----------+
| 14 | usr1 | localhost | db1 | Query | 0 | starting | show processlist | 0.000 |
| 16 | usr1 | localhost | db1 | Query | 94 | Creating sort index | SELECT `tbl1`.* FROM `tbl1` | 0.000 |
+----+------+-----------+-----+---------+------+---------------------+------------------------------+----------+
2 rows in set (0.000 sec)
mysql> kill 16;
Query OK, 0 rows affected (0.004 sec)
mysql> show processlist;
+----+------+-----------+-----+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+-----+---------+------+----------+------------------+----------+
| 14 | usr1 | localhost | db1 | Query | 0 | starting | show processlist | 0.000 |
+----+------+-----------+-----+---------+------+----------+------------------+----------+
1 row in set (0.000 sec)
Solution 2 - Mysql
Just to add
KILL QUERY **Id**
where Id is connection id from show processlist
is more preferable if you are do not want to kill the connection usually when running from some application.
For more details you can read mysql doc here
Solution 3 - Mysql
Connect to mysql
mysql -uusername -p -hhostname
show full processlist:
mysql> show full processlist;
+---------+--------+-------------------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+--------+-------------------+---------+---------+------+-------+------------------+
| 9255451 | logreg | dmin001.ops:37651 | logdata | Query | 0 | NULL | show processlist |
+---------+--------+-------------------+---------+---------+------+-------+------------------+
Kill the specific query. Here id=9255451
mysql> kill 9255451;
If you get permission denied, try this SQL:
CALL mysql.rds_kill(9255451)
Solution 4 - Mysql
Use mysqladmin to kill the runaway query:
Run the following commands:
mysqladmin -uusername -ppassword pr
Then note down the process id.
mysqladmin -uusername -ppassword kill pid
The runaway query should no longer be consuming resources.
Solution 5 - Mysql
If you have mysqladmin available, you may get the list of queries with:
> mysqladmin -uUSERNAME -pPASSWORD pr
+-----+------+-----------------+--------+---------+------+--------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------------+--------+---------+------+--------------+------------------+
| 137 | beet | localhost:53535 | people | Query | 292 | Sending data | DELETE FROM |
| 145 | root | localhost:55745 | | Query | 0 | | show processlist |
+-----+------+-----------------+--------+---------+------+--------------+------------------+
Then you may stop the mysql process that is hosting the long running query:
> mysqladmin -uUSERNAME -pPASSWORD kill 137
Solution 6 - Mysql
You need to run following command to kill the process. Find out the id of the process which you wanted to kill by
> show processlist;
Take the value from id column and fire below command
kill query <processId>;
Query parameter specifies that we need to kill query command process.
The syntax for kill process as follows
> KILL [CONNECTION | QUERY] processlist_id
Please refer this link for more information.
Solution 7 - Mysql
The author of this question mentions that it’s usually only after
MySQL prints its output that he realises that the wrong query was executed.
As noted, in this case, Ctrl-C doesn’t help. However, I’ve noticed that it
will abort the current query – if you catch it before any output is
printed. For example:
mysql> select * from jos_users, jos_comprofiler;
MySQL gets busy generating the Cartesian Product of the above two tables and
you soon notice that MySQL hasn't printed any output to screen (the process
state is Sending data) so you type Ctrl-C:
Ctrl-C -- sending "KILL QUERY 113240" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
Ctrl-C can similarly be used to stop an UPDATE query.