Something to know about the event scheduler and replication

MySQL 5.1 has been GA for 4 months now, and I am sure that many people have been using the event scheduler. There is something that you must know if you are using the event scheduler in a replicated environment. The important thing to know is that, when you use the events in replication, by default the event is active on the master only. The event creation is replicated, but the event on the slaves is not active

The reference manual explains it in detail.
There are two things that you must remember, if you are going to promote a slave to master:

• The event_scheduler variable is not replicated. If you set it on the master, the slaves don't know anything about that.
• The event creation is replicated, but disabled. Even if you set the event_scheduler variable on the slave, the events won't start.

An example will clarify it. Let's create an event on the master:

$ ./m -e "create event e1 on schedule at now() + interval 1 minute do create table et1 (i int);"
$ ./m -e "set global event_scheduler=1";

And then request the status of the event scheduler.

$ ./m -e 'select @@event_scheduler';
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
./m -e 'show events from test\G'
*************************** 1. row ***************************
                 Db: test
               Name: e1
            Definer: msandbox@%
          Time zone: SYSTEM
               Type: ONE TIME
         Execute at: 2009-03-24 00:25:41
     Interval value: NULL
     Interval field: NULL
             Starts: NULL
               Ends: NULL
             Status: ENABLED
         Originator: 1
character_set_client: latin1
collation_connection: latin1_swedish_ci
 Database Collation: latin1_swedish_ci

Everything seems to be in order on the master. Let's see the situation on the slave.

./s1 -e 'select @@event_scheduler';
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+

That was to be expected. We know that SET commands are not replicated. What about the event creation? This should be replicated, and indeed it is:

./s1 -e 'show events from test\G'
*************************** 1. row ***************************
                 Db: test
               Name: e1
            Definer: @
          Time zone: SYSTEM
               Type: ONE TIME
         Execute at: 2009-03-24 00:25:41
     Interval value: NULL
     Interval field: NULL
             Starts: NULL
               Ends: NULL
             Status: SLAVESIDE_DISABLED
         Originator: 1
character_set_client: latin1
collation_connection: latin1_swedish_ci
 Database Collation: latin1_swedish_ci

It's easy to check that the event effects are replicated. After one minute, we list the tables on both master and slaves, and table "et1" was created.

./m -e 'show tables from test'
+----------------+
| Tables_in_test |
+----------------+
| et1            | 
+----------------+

./s1 -e 'show tables from test'
+----------------+
| Tables_in_test |
+----------------+
| et1            | 
+----------------+

But here's the tricky part. The event is created, but disabled. This is the desired behavior, because the effects of the events are replicated and we don't want the same operation executed twice on the slaves.
The only drawback of this situation is that you need to change "SLAVESIDE_DISABLED" into ENABLED when you promote a slave to master. You need to act with administrator powers, because a ALTER EVENT won't achieve the desired result. You need, instead, to update the mysql.event table, as explained in the manual.
It is not a big deal, but you must remember the above two issues when promoting a slave.