Jun.01
2009
2009
Somehow you ended up with a query that is taking too long to execute and you need to kill it. First, I’d advise against using this in an UPDATE or INSERT command as I don’t know what will happen. Most likely it’ll will just roll back the DB. For a complex SELECT that just got out of hand, this should be safe.
- Log into MySQL from your Linux console.
- Show your running queries and find the ID with a long run time. I prefer to check this first in mytop to get a real time view and to see the actual query.
- Identify the transaction ID.
- Kill it.
- Exit and you’re done.
[root@server /]# mysql -u root -p
Enter password: "your password"
mysql>
mysql> show processlist;
+---------+---------+-------------------+-----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+---------+-------------------+-----------+---------+------+-------+------------------+
| 7614534 | root | localhost | NULL | Query | 0 | NULL | SHOW Processlist |
| 7614557 | root | 10.10.0.111:53626 | myDB | Query | 9999| | NULL |
| 7614641 | root | 10.10.0.111:53721 | myDB | Sleep | 5 | | NULL |
+---------+---------+-------------------+-----------+---------+------+-------+------------------+
3 rows in set (0.02 sec)
mysql> kill 7614557;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@server /]#
Enter password: "your password"
mysql>
mysql> show processlist;
+---------+---------+-------------------+-----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+---------+-------------------+-----------+---------+------+-------+------------------+
| 7614534 | root | localhost | NULL | Query | 0 | NULL | SHOW Processlist |
| 7614557 | root | 10.10.0.111:53626 | myDB | Query | 9999| | NULL |
| 7614641 | root | 10.10.0.111:53721 | myDB | Sleep | 5 | | NULL |
+---------+---------+-------------------+-----------+---------+------+-------+------------------+
3 rows in set (0.02 sec)
mysql> kill 7614557;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@server /]#
I came across your site, i think your blog is awsome, keep working !