killing processes on mysql
-
i created an event on one of my db to kill process using root as the definer.
DELIMITER $$
CREATE DEFINER='root'@'localhost' EVENT `db`.`kill_processes`ON SCHEDULE EVERY 5 SECOND
ENABLE
DO
BEGIN
DECLARE process_to_kill VARCHAR(25);
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE find_process CURSOR FOR
SELECT CONCAT('KILL ', `ID`, ';') FROM `information_schema`.`PROCESSLIST`
WHERE `information_schema`.`PROCESSLIST`.`USER` = 'some_user' AND
`information_schema`.`PROCESSLIST`.`COMMAND` = 'Sleep' AND
`information_schema`.`PROCESSLIST`.`TIME` > 90;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN find\_process; loop\_process : LOOP FETCH find\_process INTO process\_to\_kill; IF done THEN CLOSE find\_process; LEAVE loop\_process; END IF; SET @f\_p = find\_process; PREPARE stmt FROM @f\_p; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP loop\_process; END$$DELIMITER ;
but unfortunately it is not working, the event is executing every 5 seconds but it doesnt kill any process that is runnig more than 90 seconds. any help will be appreciated. thanks
-
i created an event on one of my db to kill process using root as the definer.
DELIMITER $$
CREATE DEFINER='root'@'localhost' EVENT `db`.`kill_processes`ON SCHEDULE EVERY 5 SECOND
ENABLE
DO
BEGIN
DECLARE process_to_kill VARCHAR(25);
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE find_process CURSOR FOR
SELECT CONCAT('KILL ', `ID`, ';') FROM `information_schema`.`PROCESSLIST`
WHERE `information_schema`.`PROCESSLIST`.`USER` = 'some_user' AND
`information_schema`.`PROCESSLIST`.`COMMAND` = 'Sleep' AND
`information_schema`.`PROCESSLIST`.`TIME` > 90;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN find\_process; loop\_process : LOOP FETCH find\_process INTO process\_to\_kill; IF done THEN CLOSE find\_process; LEAVE loop\_process; END IF; SET @f\_p = find\_process; PREPARE stmt FROM @f\_p; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP loop\_process; END$$DELIMITER ;
but unfortunately it is not working, the event is executing every 5 seconds but it doesnt kill any process that is runnig more than 90 seconds. any help will be appreciated. thanks
anyone please :(
-
i created an event on one of my db to kill process using root as the definer.
DELIMITER $$
CREATE DEFINER='root'@'localhost' EVENT `db`.`kill_processes`ON SCHEDULE EVERY 5 SECOND
ENABLE
DO
BEGIN
DECLARE process_to_kill VARCHAR(25);
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE find_process CURSOR FOR
SELECT CONCAT('KILL ', `ID`, ';') FROM `information_schema`.`PROCESSLIST`
WHERE `information_schema`.`PROCESSLIST`.`USER` = 'some_user' AND
`information_schema`.`PROCESSLIST`.`COMMAND` = 'Sleep' AND
`information_schema`.`PROCESSLIST`.`TIME` > 90;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN find\_process; loop\_process : LOOP FETCH find\_process INTO process\_to\_kill; IF done THEN CLOSE find\_process; LEAVE loop\_process; END IF; SET @f\_p = find\_process; PREPARE stmt FROM @f\_p; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP loop\_process; END$$DELIMITER ;
but unfortunately it is not working, the event is executing every 5 seconds but it doesnt kill any process that is runnig more than 90 seconds. any help will be appreciated. thanks
I am do dumb to to write this and didn't figure it out quickly
SET @f_p = find_process;
:wtf: BTW it is working now