Mysql-Events：如何摆脱“未获取数据零行”之类的错误？

发布于 2024-11-06 15:53:23 字数 937 浏览 5 评论 0原文

我只是尝试使用事件调度程序来终止 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;

谢谢阿曼。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

幼儿园老大 2024-11-13 15:53:24

您应该将 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;

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;

回复收藏 0 原文

眼眸印温柔 2024-11-13 15:53:24

您的代码是正确的，但是 MySQL 的错误/奇怪行为会导致出现警告，即使它已被处理。如果您在涉及表的过程末尾添加一条“虚拟”语句并且成功，则可以避免这种情况。这将清除警告。（参见http://dev.mysql.com/doc/refman /5.5/en/show-warnings.html）

在您的情况下：

SELECT ID INTO connid FROM information_schema.PROCESSLIST LIMIT 1;

循环结束后。在 MySQL 5.5.13 上，Linux 和 Windows 的警告消失。我对 MySQL Bug 60840 发表了评论，希望他们将来能修复它......

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...

回复收藏 0 原文