MySQL Bugs: #16409: Events: can't drop procedure containing CREATE EVENT statement

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

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

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

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.

When bug#18896 is fixed then there will be no possibility to create an event inside a SP, which obsoletes this bug report.

Fixed in 5.1.12

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.