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.