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

Question

我只是尝试使用事件调度程序终止 mysql 作业。不幸的是，事件调度程序用错误消息填满了我的日志文件:无数据 - 提取零行

但我正在捕获无数据异常。

为什么事件仍然抛出错误？

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;

谢谢阿尔曼。

Answer 1

您应该将 SQL_CALC_FOUND_ROWS 添加到 CURSOR 中的查询中。接下来，打开命令后检查您的 CURSOR 是否返回多行，例如

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;