Bug #63864 SP genereates ERROR CODE : 1329 when event is handled
Submitted: 28 Dec 2011 13:27 Modified: 3 Jan 2012 10:05
Reporter: Van Stokes Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.5.19, 5.5.20 OS:Linux (Ubuntu 11.10 x86_64)
Assigned to: Assigned Account CPU Architecture:Any
Tags: 1329, error, Event, PROCEDURE, stored

[28 Dec 2011 13:27] Van Stokes 
Description:
Continuation of MySQL bug 60840 ( http://bugs.mysql.com/bug.php?id=60840 ).
Stored procedures generates:

Error Code : 1329
No data - zero rows fetched, selected, or processed

when the event is handled.

The explanation "not a bug" explanation provided in 60840 does not make sense.

How to repeat:
Procedure source here: http://pastebin.com/Uhkgyjnw

Suggested fix:
Flush the warning/error when the event is handled in code:
[28 Dec 2011 15:26] Valeriy Kravchuk 
Indeed, warning is NOT cleared, and this is a problem. Here is a simple test case:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.20-debug |
+--------------+
1 row in set (0.01 sec)

mysql> delimiter //
mysql> create table t1 (c1 int)//
Query OK, 0 rows affected (0.27 sec)

mysql> create procedure proc2() begin declare done int default 0; declare continue handler for not found begin set done=1; set @a=1; end; select c1 into @a from t1; end//
Query OK, 0 rows affected (0.05 sec)

mysql> select @a;
    -> //
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> call proc2()//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @a//
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1329
Message: No data - zero rows fetched, selected, or processed
1 row in set (0.00 sec)

So, continue handler worked (@a was set in it), but warning is still visible.
[28 Dec 2011 15:33] Valeriy Kravchuk 
If, for whatever reason, this behavior is considered normal now, manual should explain it, with examples, at http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html.
[30 Dec 2011 13:42] Van Stokes 
The error log is peppered with entries like this:

[ERROR] Event Scheduler: [vstokes@%][lwoti.GenLCLExportDailyStatsRange] No data - zero rows fetched, selected, or processed
[3 Jan 2012 10:05] Jon Olav Hauglid 
Closing this bug as a duplicate of Bug#55843 - fixed in 5.6.3.