[AWS Aurora MySQL] Kill a session or a running query on Aurora MySQL

How do you kill (end) a session or a running query on Aurora MySQL?

Background

If you're DBA, normally you can kill session/query by using "KILL" command on MySQL.

But it might not work on AWS Aurora MySQL.

Permissions are differences between you and the system admin user

Even if you're a DBA, but you're not an actual DBA.

Take a look here.

Of course, 'rdsadmin'@'localhost' which is a system admin has all permissions.

But, you - as a DBA - do not have all permissions.

Now what?

AWS Aurora MySQL provides 2 system stored procedures.

If you want to kill a session, then you can call mysql.rds_kill procedure.

CALL mysql.rds_kill(thread-ID);

For e.g. process id(session id) is 199, then

CALL mysql.rds_kill(199);

If you want to kill a specific running query, then you can call mysql.rds_kill_query procedure.

CALL mysql.rds_kill_query(thread-ID);

For e.g. process id(session id) is 199, then

CALL mysql.rds_kill_query(199);

That's all.

Now, you can kill the user queries. :)

Curiosity

Somehow, I was curious about how does it work.

Luckily, "SHOW CREATE PROCEDURE" command worked.

SHOW CREATE PROCEDURE mysql.rds_kill;

CREATE DEFINER = `rdsadmin` @`localhost`
PROCEDURE `rds_kill`(IN thread BIGINT)
   READS SQL DATA
   DETERMINISTIC
BEGIN
   DECLARE l_user   varchar(16);
   DECLARE l_host   varchar(64);
   DECLARE foo      varchar(255);

   SELECT user, host
   INTO l_user, l_host
   FROM information_schema.processlist
   WHERE id = thread;

   IF l_user = 'rdsadmin' AND l_host LIKE 'localhost%'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSADMIN SESSION`
      INTO foo;
   ELSEIF l_user = 'rdsrepladmin'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSREPLADMIN SESSION`
      INTO foo;
   ELSE
      KILL thread;
   END IF;
END

SHOW CREATE PROCEDURE mysql.rds_kill_query;

CREATE DEFINER = `rdsadmin` @`localhost`
PROCEDURE `rds_kill_query`(IN thread BIGINT)
   READS SQL DATA
   DETERMINISTIC
BEGIN
   DECLARE l_user   varchar(16);
   DECLARE l_host   varchar(64);
   DECLARE foo      varchar(255);

   SELECT user, host
   INTO l_user, l_host
   FROM information_schema.processlist
   WHERE id = thread;

   IF l_user = 'rdsadmin' AND l_host LIKE 'localhost%'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSADMIN QUERY`
      INTO foo;
   ELSEIF l_user = 'rdsrepladmin'
   THEN
      SELECT `ERROR (RDS): CANNOT KILL RDSREPLADMIN QUERY`
      INTO foo;
   ELSE
      KILL QUERY thread;
   END IF;
END

Turned out they are doing the exact same as "KILL" and/or "KILL QUERY" but executing by the local admin user.

References

• Common DBA Tasks for MySQL DB Instances
• MySQL on Amazon RDS SQL Reference