Kill Slow Running MySQL Queries

KillLongRunningQueries.sql

--
-- Kills long running queries
-- 
-- @see https://stackoverflow.com/a/27293134
--

DROP PROCEDURE IF EXISTS `KillLongRunningQueries`;
SET GLOBAL event_scheduler = ON;

--
-- SP: KillLongRunningQueries()
-- 
-- Checks for any queries that have been running for > 300 seconds.
-- If any are found, they are killed using KILL QUERY.
--

DELIMITER //
CREATE PROCEDURE KillLongRunningQueries ()
BEGIN

  DECLARE v_qid BIGINT;
  DECLARE v_finished INT DEFAULT 0;
  DECLARE c_queries CURSOR FOR SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Query' AND TIME > 300;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

  OPEN c_queries;

  l_fetch_queries: LOOP
    FETCH c_queries INTO v_qid;
    IF v_qid > 0 THEN
      KILL QUERY v_qid;
    END IF;
    IF v_finished THEN
      LEAVE l_fetch_queries;
    END IF;
  END LOOP l_fetch_queries;
  CLOSE c_queries;

END//

--
-- EVENT: KillLongRunningQueries
-- 
-- Schedules KillLongRunningQueries() to run every 60 seconds
-- 

DELIMITER ;
CREATE EVENT KillLongRunningQueries
ON SCHEDULE EVERY 60 SECOND
DO CALL KillLongRunningQueries();