How to kill a query on Amazon Relational Database Service (RDS)
Coming from MySQL / MariaDB, I was surprised that I couldn't kill a query on an
Amazon's Relational Database Service or Amazon Aurora with the kill command.
Fortunately, the functionality isn't omitted, it' just buried under some AWS RDS specific command. Before we go over that, let's go over how to get the ID of a query:
SHOW PROCESSLIST;Which will get you a list similar to this:
+------------+----------+-----------+------+---------+------+--------------+--------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------------+----------+-----------+------+---------+------+--------------+--------------------+
| 2132181096 | rdsadmin | localhost | NULL | Sleep | 0 | cleaning up | NULL |
| 2132181149 | rdsadmin | localhost | NULL | Sleep | 0 | cleaning up | NULL |
| 2132183981 | rdsadmin | localhost | NULL | Sleep | 0 | cleaning up | NULL |
| 2132184028 | rdsadmin | localhost | NULL | Sleep | 75 | cleaning up | NULL |
| 2281457043 | master | localhost | db | Query | 0 | starting | SHOW PROCESSLIST |
| 2281459713 | username | localhost | db | Sleep | 14 | cleaned up | NULL |
| 2281459714 | username | localhost | db | Sleep | 3 | cleaning up | NULL |
| 2281460263 | username | localhost | db | Query | 0 | Sending data | SELECT * FROM... |
| 2281460515 | username | localhost | db | Sleep | 3 | cleaned up | NULL |
| 2281460516 | username | localhost | db | Sleep | 3 | cleaned up | NULL |
| 2281460518 | username | localhost | db | Sleep | 0 | cleaning up | NULL |
| 2281460519 | username | localhost | db | Query | 3 | Sending data | SELECT COUNT(*)... |
| 2281460522 | username | localhost | db | Sleep | 2 | cleaned up | NULL |
| 2281460530 | username | localhost | db | Sleep | 0 | cleaning up | NULL |
| 2281460531 | username | localhost | db | Query | 0 | Sending data | SELECT DISTINCT... |
| 2281460532 | username | localhost | db | Sleep | 0 | cleaned up | NULL |
+------------+----------+-----------+------+---------+------+--------------+--------------------+
16 rows in set (0.00 sec)The value that we're interested in here would the first one, labeled Id. With
the ID of a query we want to kill, all we have to do is pass it to the
AWS-specific command:
-- Replace :id with the Id value
CALL mysql.rds_kill_query(:id);Upon running that command, the query will be obliterated!