MySQL Bugs: #60840: Event Scheduler: No data - zero rows fetched, selected, or processed

Bug #60840	Event Scheduler: No data - zero rows fetched, selected, or processed
Submitted:	12 Apr 2011 10:02	Modified:	26 Apr 2011 19:08
Reporter:	Sadeq NOT_FOUND	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Event

Description:
I scheduled a store procedure to run an stored procedure.
When I run stored procedure manually there is not any warning nor error
(The store procedure does not make any result set)

And the problem: when the event call that SP, it make an error in log (both mysql error log file and windows event):
Event Scheduler: [root@%][testdb.testevent] No data - zero rows fetched, selected, or processed.
I tired to handle it using:
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' ...
but it does not help!

How to repeat:
1. create a database.
2. create stored procedure that run several queries.
3. create a event that call the stored procedure periodic.
4. check event log of windows or error log in data folder.

Please, send the exact code of procedure and CREATE EVENT statement. Does this problem happen when you run procedure "manually"?

No, It does not happen if call the stored procedures when call them manually!

The Stored procedures and the Event definition:

DELIMITER ;;

CREATE PROCEDURE `FixTable`(IN `TableName` VARCHAR (400), IN `FixLevel` INT)

BEGIN

  declare done int default 0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  CASE FixLevel
	WHEN 1 THEN SET @SQLText = CONCAT('REPAIR TABLE `',TableName,  '` QUICK');
	WHEN 2 THEN SET @SQLText = CONCAT('REPAIR TABLE `',TableName,  '` EXTENDED');
	WHEN 3 THEN SET @SQLText = CONCAT('REPAIR TABLE `',TableName,  '` USE_FRM');
	WHEN 4 THEN SET @SQLText = CONCAT('OPTIMIZE LOCAL TABLE `',TableName,  '` ');
  END CASE;
  PREPARE RES from @SQLText;
  EXECUTE RES;
END ;;

DELIMITER ;

DELIMITER ;;

CREATE PROCEDURE `fixAllTables`(IN `FixLevel` INT)

begin

  DECLARE done INT DEFAULT 0;

  declare TableName VARCHAR(30);

  DECLARE tablestatusCur CURSOR FOR

  SELECT TABLE_NAME FROM information_schema.`TABLES` T 
	where TABLE_SCHEMA=SCHEMA() and Table_comment not like '%VIEW%' and Table_ROWS is null;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 open tablestatusCur;

 repeat

   FETCH tablestatusCur INTO TableName;

   if not done then

     call FixTable(TableName,FixLevel);

   end if;

 until done end repeat;

end ;;

DELIMITER ;

DELIMITER ;;
CREATE EVENT `fixTablesHourly` 
	ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP 
	ON COMPLETION NOT PRESERVE 
	ENABLE 
	COMMENT 'Check for damaged tables and fix them.' 
DO BEGIN
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @LastEventErrorTime=CURRENT_TIMESTAMP;
	call fixAllTables(1);
	call fixAllTables(2);
	call fixAllTables(3);
END  ;;

DELIMITER ;

The Create procedure and Event in pastebin: http://pastebin.com/v13HYyWM

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.5/en/news-5-5-6.html:

----<q>----
# Incompatible Change: Handling of warnings and errors during stored program execution was problematic:

    *   If one statement generated several warnings or errors, only the handler for the first was activated, even if another might be more appropriate.
    * Warning or error information could be lost.

(Bug #36185, Bug #5889, Bug #9857, Bug #23032)
----</q>-----

This means now, after this fix, warnings from SP which was called first time, are not cleared and accessible in the EVENT. You have to handle them in EVENT too.

Well, he DOES handle the warning in the Event: he declared a CONTINUE HANDLER.

He was wrong when he wrote that the warning does not appear when executing the stored procedure manually. If he had executed a "SHOW WARNINGS" after the call, he would have seen the warning. It is just not recorded in the logs when the call is made manually.

So this is a bug, and it occurs everytime you use a cursor in a stored procedure. The reason is, that the warning is remembered, although it has been handled. A handled warning should be cleared.
At least the manual sections 12.7.4. and 12.7.5. should be updated, mentioning that a warning generated in a stored procedure will be remembered even it was handled, as long as it is not cleared by a subsequent query.

This leads us to a workaround:
Manual section 12.4.5.41. says, that "a statement that uses a table but generates no messages clears the message list".
I was able to suppress the 1329/No data warning when I added another statement near the end of the stored procedure. I just selected a value from a table that contains data, so that it will be successful.

In this case:

SELECT TABLE_NAME INTO TableName FROM information_schema.`TABLES` LIMIT 1;
near the end of `fixAllTables` will let the warning disappear.

This is a very odd behaviour and I would consider this being a bug.

I have the same issue when I upgraded my server to 5.5.14-log.
I still have an instance of my old server 5.1.47-log and the issue doesn't occur there.

This IS  a bug and is still persistent as of 5.5.19-log (x64 on Ubuntu 11.10).

The explanation from MySQL is incorrect. The error/warning should be cleared if it's handled.