MySQL Bugs: #55742: Enabling events on slave don't work as described in manual

Bug #55742	Enabling events on slave don't work as described in manual
Submitted:	4 Aug 2010 12:35	Modified:	31 Aug 2010 9:08
Reporter:	Sveta Smirnova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	events, replication

Description:
There is test at the http://dev.mysql.com/doc/refman/5.1/en/replication-features-invoked.html:

----<q>----
 When promoting a replication slave having such events to a replication master, use the following query to enable the events:

UPDATE mysql.event
    SET STATUS = 'ENABLED'
    WHERE STATUS = 'SLAVESIDE_DISABLED';

If more than one master was involved in creating events on this slave, and you wish to enable events that were created only on a given master having the server ID master_id, use the following query instead:

UPDATE mysql.event
    SET STATUS = 'ENABLED'
    WHERE ORIGINATOR = master_id
    AND STATUS = 'SLAVESIDE_DISABLED';

Important

Before executing either of the previous two UPDATE statements, you should disable the Event Scheduler on the slave (using SET GLOBAL event_scheduler = OFF;), run the UPDATE, restart the server, then re-enable the Event Scheduler afterward (using SET GLOBAL event_scheduler = ON;). 
----</q>----

But I can not enable events as described here: status changed, but no event executed.

Although test is for master/slave environment same problem applies outside replicated setup.

Workaround: use ALTER EVENT

How to repeat:
--source include/master-slave.inc

set global event_scheduler=on;

connection slave;
set global event_scheduler=on;

connection master;
create event ev1 on schedule every 1 minute do select 1 into @a;
sleep 120;

connection slave;
--vertical_results
select * from mysql.event;

set global event_scheduler=off;
update mysql.event set status='ENABLED' where status='SLAVESIDE_DISABLED';
set global event_scheduler=on;
sleep 120;
select * from mysql.event;

The fact that the server lets you think you're doing something when you're not having any effect is a bug in the server, not the documentation. If we are not going to honour updates to mysql.event, we should explicitly disallow them.

Jon,

I don't understand you comment.

How can we disallow someone with root permissions from updating the events table (or any other) with direct sql?

Any 'action' against the system (adding users, changing grants adding a database) can be hacked by one more direct queries to the mysql tables. If you perform some (but not all of them) you will not be successful (for example granting a user a priv via update to the mysql database and not doing a 'flush' will not have the action take effect although the update will not fail).

I don't see any server bug here - only document the correct way of doing things = with is with 'sql commands' and not direct queries to the mysql database.

Reassigned to self, set prio/target.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.