Kill long running mysql select queries

kill-mysql-processes.txt

To kill stuck processes

select group_concat(concat('KILL ',id,';') separator ' ')
from information_schema.processlist
where Time>'100' and Info like 'select %';

To kill sleeping processes and allow alter table to occur

select group_concat(concat('KILL ',id,';') separator ' ')
from information_schema.processlist
where Command='Sleep' and User='sqluser';

To find blocked and blocking transactions:

SELECT requesting_trx_id, r.trx_mysql_thread_id as requesting_process_id,
r.trx_started as requesting_trx_started, r.trx_query as requesting_trx_query,
r.trx_wait_started as requesting_wait_started,
blocking_trx_id, b.trx_mysql_thread_id as blocking_process_id,
b.trx_started as blocking_trx_started, b.trx_query as blocking_trx_query,
b.trx_wait_started as blocking_wait_started
FROM information_schema.INNODB_LOCK_WAITS
INNER JOIN information_schema.INNODB_TRX as r on requesting_trx_id=r.trx_id
INNER JOIN information_schema.INNODB_TRX as b on blocking_trx_id=b.trx_id;

SELECT b.trx_mysql_thread_id as blocking_process_id,count(*)
FROM information_schema.INNODB_LOCK_WAITS
INNER JOIN information_schema.INNODB_TRX as r on requesting_trx_id=r.trx_id
INNER JOIN information_schema.INNODB_TRX as b on blocking_trx_id=b.trx_id
group by 1 order by 2 ;


select group_concat(concat('KILL ',b.trx_mysql_thread_id,';') separator ' ')
FROM information_schema.INNODB_LOCK_WAITS
INNER JOIN information_schema.INNODB_TRX as r on requesting_trx_id=r.trx_id
INNER JOIN information_schema.INNODB_TRX as b on blocking_trx_id=b.trx_id;