MySQL Forums
Forum List  »  Events (Job Scheduler)

Events and stored procedures
Posted by: Robert Hairgrove
Date: May 04, 2013 04:37AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Events and stored procedures
7439
May 04, 2013 04:37AM


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.