MySQL Forums
Forum List  »  Docs

Events and stored procedures
Posted by: Robert Hairgrove
Date: May 01, 2013 08:15AM

We are using MySQL 5.1.69, and I'm a little confused about this bit from the documentation concerning events:

"An event may not be created, altered, or dropped by a stored routine, trigger, or another event. An event also may not create, alter, or drop stored routines or triggers. (Bug #16409, Bug #18896)"
http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html#stored-routines-event-restrictions

But I have seen numerous instances on the web which give code examples of stored procedures which contain ALTER EVENT statements. When I tried this myself, it seemed to work OK:

CREATE TABLE mydb.test_event (
id int(11) NOT NULL AUTO_INCREMENT,
dummy varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'x',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DELIMITER //

CREATE EVENT mydb.event_test
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
DISABLE
DO insert into mydb.test_event (dummy) VALUES ('x');
//

CREATE PROCEDURE mydb.enable_event()
MODIFIES SQL DATA
BEGIN
ALTER EVENT mydb.event_test ENABLE;
END//

CREATE PROCEDURE mydb.disable_event()
MODIFIES SQL DATA
BEGIN
ALTER EVENT mydb.event_test DISABLE;
END//

DELIMITER ;

I have a different user account which has only SELECT and EXECUTE privileges on mydb. Assuming that the event scheduler is running, now that user can turn the event 'test_event' on and off, which was the whole purpose of this exercise.

Why does this work if the documentation says it shouldn't?



Edited 1 time(s). Last edit at 05/01/2013 08:41AM by Robert Hairgrove.

Options: ReplyQuote


Subject
Views
Written By
Posted
Events and stored procedures
5207
May 01, 2013 08:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.