I already posted this question in the MySQL Docs forum, but thought more people might read it here. Although the sample code SEEMS to work OK, I'm mostly worried that there might be some kind of undefined behavior going on here because the documentation is so clear about stating that it really shouldn't work at all.
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 DATABASE IF NOT EXISTS mydb;
USE mydb;
CREATE TABLE IF NOT EXISTS 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 ;
SET GLOBAL event_scheduler=ON;
-- verify that it is running:
SHOW PROCESSLIST;
-- output:
Id User Host db Command Time State Info
105 phpmyadmin localhost NULL Sleep 0 NULL
106 bob localhost mydb Query 0 NULL SHOW PROCESSLIST
107 event_scheduler localhost NULL Daemon 0 Waiting on empty queue NULL
CALL mydb.enable_event();
-- verify that the event is enabled:
SELECT * FROM test_event;
CALL mydb.disable_event();
set global event_scheduler=OFF;
SHOW PROCESSLIST;
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.
QUESTION: Does this work reliably? If so, why does this work if the documentation says it shouldn't?