錯誤解決mysql - Event Scheduler: No data - zero rows fetched, selected, or processed

標籤：

當遇到一個NOT FOUND（無資料）的警告時，使用一個包含清除警告語句的條件控制代碼處理，就可以繼續處理常式並退出控制代碼。這個問題在MySQL5.6.3之後的版本已經解決了，所以該解決方案不是必要的。 解決方案： 　　

  DECLARE CONTINUE HANDLER FOR NOT FOUND    BEGIN
　　　 SET done=1      SELECT 1 INTO @handler_invoked FROM (SELECT 1) AS t;    END;

來源：Click the link(http://dev.mysql.com/doc/refman/5.5/en/condition-handling.html) and scroll to the bottom for details but the fix was to include a successful select INSIDE the CONTINUE HANDLER:  原文：

Before MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked. The following discussion demonstrates the issue and provides a workaround.

Suppose that a table t1 is empty. The following procedure selects from it, raising a No Data condition:

CREATE PROCEDURE p1()BEGIN  DECLARE a INT;  DECLARE CONTINUE HANDLER FOR NOT FOUND    BEGIN      SET @handler_invoked = 1;    END;  SELECT c1 INTO a FROM t1;END;

As can be seen from the following sequence of statements, the condition is not cleared by handler invocation (otherwise, the SHOW WARNINGS output would be empty). But as can be seen by the value of @handler_invoked, the handler was indeed invoked (otherwise its value would be 0).

mysql> SET @handler_invoked = 0;Query OK, 0 rows affected (0.00 sec)mysql> CALL p1();Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+---------+------+-----------------------------------------------------+| Level   | Code | Message                                             |+---------+------+-----------------------------------------------------+| Warning | 1329 | No data - zero rows fetched, selected, or processed |+---------+------+-----------------------------------------------------+1 row in set (0.00 sec)mysql> SELECT @handler_invoked;+------------------+| @handler_invoked |+------------------+|                1 |+------------------+1 row in set (0.00 sec)

To work around this issue, use a condition handler containing a statement that clears warnings:

CREATE PROCEDURE p1()BEGIN  DECLARE a INT;  DECLARE CONTINUE HANDLER FOR NOT FOUND    BEGIN      SELECT 1 INTO @handler_invoked FROM (SELECT 1) AS t;    END;  SELECT c1 INTO a FROM t1;END;

This works for CONTINUE and EXIT handlers.

This issue is resolved as of MySQL 5.6.3 and no workaround is needed.

 

 

 

錯誤解決mysql - Event Scheduler: No data - zero rows fetched, selected, or processed