How can I stop a running MySQL query?

Question

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?

It is worth mentioning that MySQL 5.7 supports a server-side SELECT statement timeout. More information on this here: http://mysqlserverteam.com/server-side-select-statement-timeouts/ — Morgan Tocker, Jul 02 '14 at 16:05

score 539 · Accepted Answer · edited May 11 '22 at 12:44

539

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)

edited May 11 '22 at 12:44

BinaryButterfly

18,137
13
50
91

answered Sep 24 '10 at 13:39

baklarz2048

10,699
2
31
37

10

but `mysql` is printing... I can't see the prompt – David B Sep 24 '10 at 13:42
41

I agree with this basic approach, but I think using `KILL QUERY` is slightly preferable to `KILL` for this case. That way the query is killed, but not the connection. – Ike Walker Sep 24 '10 at 18:12
3

a useful tip if your process list is scrolling past your buffer is to set the pager. Just enter '\P more' at the prompt. See more on this tip here http://www.dbasquare.com/2012/03/28/how-to-work-with-a-long-process-list-in-mysql/ – Scott Aug 05 '13 at 17:13
@Zilverdistel How do you do it in phpMyAdmin? – BadHorsie Mar 16 '15 at 18:05
This didn't work for all queries. They said "sending data" Instead, I used kill query ; – user984003 Jul 24 '15 at 22:31
How do you do this WHILE your console is spewing out information? – Lucas Apr 18 '16 at 20:23
You don't show mysql command against the `kill`. It's worthy noting that the the process numbers are not OS, and `kill` applies only to MySQL, and OS `kill`: `mysql> kill ;` or `$mysql -uusername -p -hhostname -e 'kill '` – Mugoma J. Okomba Sep 13 '16 at 13:39
I get "ERROR 1094 (HY000): Unknown thread id: XXX" with XXX being the id from process list. – DrLightman Feb 27 '17 at 10:58
4

If using MySQL on AWS RDS, you won't have permission to run `KILL`, but you can run: `CALL mysql.rds_kill(12345)` – Kip Mar 23 '17 at 19:51

maaz · Answer 2 · 2012-12-22T12:32:22.150

81

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

edited Dec 22 '12 at 12:32

answered Dec 22 '12 at 12:27

maaz

4,371
2
30
48

A question I had related to this, does the connection just respawn, or does killing connections eventually result in the pool size being too small for the application to run? – Uncle Iroh Jun 02 '14 at 23:25

minhas23 · Answer 3 · 2015-01-29T07:09:07.630

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)

Note: That last query is required if using AWS RDS instance. — Kip, Mar 23 '17 at 19:49

score 15 · Answer 4 · edited Dec 28 '13 at 22:07

15

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.

edited Dec 28 '13 at 22:07

Eric Leschinski

146,994
96
417
335

answered Oct 10 '13 at 06:43

minhas23

9,291
3
58
40

score 9 · Answer 5 · answered May 23 '13 at 15:29

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

Shivam Kubde · Answer 6 · 2021-01-08T11:24:07.597

9

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.

edited Jan 08 '21 at 11:24

answered Jan 04 '19 at 09:17

Shivam Kubde

545
1
8
17

score 1 · Answer 7 · edited Aug 14 '20 at 16:57

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.

How can I stop a running MySQL query?

7 Answers7

Linked