How do I kill a process in MySQL running within Amazon RDS?

Question

I’m using MySql 5.5.37. As root, I’m trying to kill a transaction that’s locking some tables. I run

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G

and get the output

…
*************************** 6. row ***************************
                    trx_id: 143E6CDE
                 trx_state: RUNNING
               trx_started: 2014-10-20 06:03:56
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2305887
       trx_mysql_thread_id: 158360
                 trx_query: delete from event where id not in (select q.* from (select e.id FROM event e, (select object_id, max(date_processed) d from event group by object_id) o where e.object_id = o.object_id and e.date_processed = o.d) q)
       trx_operation_state: NULL
         trx_tables_in_use: 3
         trx_tables_locked: 3
          trx_lock_structs: 210634
     trx_lock_memory_bytes: 19790264
           trx_rows_locked: 10668793
         trx_rows_modified: 2095253
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000

But when I run a “kill” statement, I get an error.

mysql> kill 158360;
ERROR 1095 (HY000): You are not owner of thread 158360

How do I clear this transaction from MySql?

score 112 · Answer 1 · answered Aug 26 '17 at 15:51

Just to complete Bill answer if you are using RDS MySQL you can use rds_kill() procedure, as the following example:

Connect to MySQL

List process:

SHOW PROCESSLIST;

In my case, I want to kill the process of id 1948452:

CALL mysql.rds_kill(1948452);

Done

score 47 · Accepted Answer · answered Oct 20 '14 at 23:38

You can always kill your own thread, but you need the SUPER privilege to kill someone else's thread.

Are you on RDS? If so, you do not have SUPER privilege even if your user name is 'root'. There's nothing implicitly special about the 'root' name, it's the privilege that counts.

You can confirm your privileges by running:

mysql> SHOW GRANTS;

As for how to kill the thread, if this is RDS, you can call a procedure rds_kill() to do it for you.

Indeed we are using RDS. Your solution works perfectly. – Dave Oct 21 '14 at 14:06 — Dave, Oct 21 '14 at 14:06

score 9 · Answer 3 · answered Oct 31 '18 at 15:52

9

If you are using Azure Database for MySQL, you can use the procedure az_kill:

List process:

SHOW PROCESSLIST;

In case the ID of the process you want to kill is 345, run:

CALL mysql.az_kill(345);

This works even if the current logged in user does not own process 345.

answered Oct 31 '18 at 15:52

remeika

1,075
11
16

If you are using mariadb "CALL mysql.rds_kill(345);" – james.c.funk May 05 '23 at 20:29

How do I kill a process in MySQL running within Amazon RDS?

3 Answers3