Mysql-Events: How get rid of Errors like No Data-zero rows fetched?

I just try to开发者_开发技巧 kill mysql jobs using event scheduler. Unfortunately the event scheduler fills up my log file with error messages: No Data - zero rows fetched

But I am catching the No data exception.

Why event still throws an error?

CREATE PROCEDURE `kill_run_aways`( IN runtime INT(7), IN username VARCHAR(32) )
BEGIN
        DECLARE done INT DEFAULT 0;
        DECLARE connid INT UNSIGNED;
        DECLARE cur1 CURSOR FOR SELECT ID FROM information_schema.PROCESSLIST 
            WHERE  COMMAND ='Query'
            AND TIME >= runtime AND USER = username;

        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        OPEN cur1;

        REPEAT
                FETCH cur1 INTO connid;
                IF NOT done THEN
                    select * from information_schema.PROCESSLIST where connid=ID;
                    KILL connid;
                END IF;
        UNTIL done END REPEAT;
        CLOSE cur1;
  END;

Thanks Arman.

You should add SQL_CALC_FOUND_ROWS to your query in CURSOR. Next, after open command check whether your CURSOR returns more than one row, e.g.

DECLARE cur1 CURSOR FOR SELECT SQL_CALC_FOUND_ROWS '1' FROM DUAL;
OPEN cur1;
IF Select FOUND_ROWS() /* it concerns SQL_CALC_FOUND_ROWS */  > 0 THEN
    -- do sth
ELSE
    -- do sth else
END IF;

Your code is correct, but a bug/strange behaviour of MySQL causes the warning to appear even if it was handled. You can avoid that if you add a "dummy" statement to the end of your procedure that involves a table and is successful. This will clear the warning. (See http://dev.mysql.com/doc/refman/5.5/en/show-warnings.html)

In your case:

SELECT ID INTO connid FROM information_schema.PROCESSLIST LIMIT 1;

after the end of the loop. On MySQL 5.5.13 the warning disappears for Linux and Windows. I commented on MySQL Bug 60840 and I hope they will fix it some time in the future...

Mysql says that NOT FOUND "is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set" (http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html. So I believe you get this message when line select * from information_schema.PROCESSLIST where connid=ID; is executed.