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?

Augie

It is worth mentioning that MySQL 5.7 supports a server-side SELECT statement timeout. More information on this here: mysqlserverteam.com/server-side-select-statement-timeouts – Ariel

The article mentioned by @MorganTocker has been moved to dev.mysql.com/blog-archive/… – Cranwell

Answer 1

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)

Rhodonite

but mysql is printing... I can't see the prompt – Augie

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. – Propertied

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 dbasquare.com/2012/03/28/… – Buddy

@Zilverdistel How do you do it in phpMyAdmin? – Flaunty

This didn't work for all queries. They said "sending data" Instead, I used kill query <id number no quotes>; – Snowinsummer

How do you do this WHILE your console is spewing out information? – Dedrick

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 <ID>; or $mysql -uusername -p -hhostname -e 'kill <ID>' – Unregenerate

I get "ERROR 1094 (HY000): Unknown thread id: XXX" with XXX being the id from process list. – Luminance

If using MySQL on AWS RDS, you won't have permission to run KILL, but you can run: CALL mysql.rds_kill(12345) – Magdala

Answer 2

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

Photography

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? – Consumable

Answer 3

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)

Tabbie

Note: That last query is required if using AWS RDS instance. – Magdala

Answer 4

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.

Tabbie

Answer 5

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

Havre

Answer 6

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.

Micro

Answer 7

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.

Beeves