MySQL: Kill sleeping connections

Platform: MySQL 5.x

Most of the time it would be handy to have a native MySQL script which would allow one to kill the sleeping connections which are in sleep state for more than 180 sec..  On the other hand DBA's can use  "wait_timeout" etc parameters to control this..

[code]

 # -- Make sure you are logged as MySQL 'root' user or any user who have got super privileges 



DELIMITER $$
DROP PROCEDURE IF EXISTS `uKillSleepingSessions`$$


CREATE PROCEDURE `uKillSleepingSessions`()
COMMENT 'This routne is used to kill idle sessions'
READS SQL DATA


BEGIN


DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE uID bigint(4);

DECLARE my_cur CURSOR FOR
SELECT ID
  FROM information_schema.PROCESSLIST PL
WHERE PL.COMMAND='Sleep' AND PL.TIME > 180;


DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;


OPEN my_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP


FETCH my_cur
INTO uID;


IF no_more_rows THEN
    CLOSE my_cur;
    LEAVE the_loop;
END IF;


SET @tmp_sql= CONCAT("KILL ",uID);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;


DEALLOCATE PREPARE s1;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;


END$$
DELIMITER ;



# Usage from mysql prompt/gui client
# Once called , it would cleanup idle connections which are sleeping for more than 180 sec
call uKillSleepingSessions();

[/code]