Bug #16409 Events: can't drop procedure containing CREATE EVENT statement
Submitted: 12 Jan 2006 3:56 Modified: 17 Sep 2006 5:42
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1.6-alpha-debug OS:Linux (SUSE 10.0)
Assigned to: Andrey Hristov CPU Architecture:Any

[12 Jan 2006 3:56] Peter Gulutzan
Description:
I have a procedure which contains a CREATE EVENT statement.
The event's schedule clause depends on a @variable.
If that variable is not positive, I can't drop the procedure.

How to repeat:

mysql> set @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p_16 () create event e_16 on schedule every @a second do set @a=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set @a = null;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure p_16;
ERROR 1516 (HY000): INTERVAL must be positive
[27 Jan 2006 15:22] Andrey Hristov
The same problem is valid for CREATE PROCEDURE and ALTER PROCEDURE because in all three cases the server code compiles the body of the procedure (though on DROP it's not needed and probably on ALTER also not).
[5 May 2006 11:34] Andrey Hristov
Will be fixed when #18896 is fixed. Tested with the patch for 18896 and this problem does not happen anymore. More or less it's because SP were changed not to compile the body when doing DROP PROCEDURE.
[1 Jun 2006 10:27] Andrey Hristov
When bug#18896 is fixed then there will be no possibility to create an event inside a SP, which obsoletes this bug report.
[15 Sep 2006 15:16] Dmitry Lenev
Fixed in 5.1.12
[17 Sep 2006 5:42] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.12 changelog.

Updated Event Limitations in the 5.1 Manual.