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?

Answer 1

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.