Tuesday, March 24, 2009

Something to know about the event scheduler and replication


Event scheduler

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.

2 comments:

noneed said...

After UPDATE-ing mysql.event on the slave, by changing slaveside_disabled to enabled one has to restart the server. The reason is simple, the scheduler reads mysql.event just once, during boot, and loads all the events in memory, where the authoritative source of information for the scheduler is.

gmax said...

@noneed,
Yes, the manual page linked from the article explains this point as well.

Giuseppe

Vote on Planet MySQL