Ok,
J'ai trouvé un bout de code auquel j'ai rajouté l'événement et cela fonctionne.
Merci à vous.
Cordialement.
Code procédure :
DELIMITER $$
DROP PROCEDURE IF EXISTS kill_user_processes$$
CREATE PROCEDURE `kill_user_processes`(
) -- IN user_to_kill VARCHAR(255)
READS SQL DATA
BEGIN
DECLARE name_val VARCHAR(255);
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE friends_cur CURSOR FOR
SELECT CONCAT('KILL ', id, ';')
FROM
information_schema.processlist
WHERE
user = 'root' AND Time > 100
AND LEFT(Host, 9) = 'localhost'
AND DB = 'PCSecurite4';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
OPEN friends_cur;
SELECT FOUND_ROWS() INTO num_rows;
the_loop: LOOP
FETCH friends_cur
INTO name_val;
IF no_more_rows THEN
CLOSE friends_cur;
LEAVE the_loop;
END IF;
SET @s = name_val;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT name_val;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
SELECT num_rows, loop_cntr;
END $$
DELIMITER ;
Code événement :
DELIMITER $$
GRANT EVENT ON pcsecurite4.* TO
root@10.1.21.144$$
CREATE EVENT ev_kill_massprocess_10
ON SCHEDULE
EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE
DO
CALL kill_user_processes;$$
DELIMITER ;