Bug #17858 events definition is (row-based) replicated but events don't start
Submitted: 2 Mar 2006 12:33 Modified: 27 May 2006 6:55
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.7 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[2 Mar 2006 12:33] Giuseppe Maxia
Description:
Event definitions created on master are replicated to the slaves (table mysql.event), but they are not used in the slave (unless the slave is restarted). 

If the slave server is restarted while the events are still valid, the events will work as if they were created on the slave itself, thus creating a possible conflict between master and slaves.
(Slaves should not have the event_scheduler variable set until they are promoted to master in case of a failover, but be aware of this possible conflict).

How to repeat:
on the master:
# ------ CUT
set global event_scheduler=1;
use test ;
drop table if exists t1;
CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `c` varchar(50) NOT NULL,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

create event justonce on schedule at now() + interval 5 second do insert into t1 (c) values ('from justonce 2');
# ------ CUT
Check the master after  6 seconds.
The event has been removed (since we did not include a PRESERVE clause) and table t1 has one more row.
Now check the slave. 
The event is there, as you can see by issuing a SHOW EVENTS command, and it is listed as ENABLED.
However, it does not work. Table t1 has not been updated (bug #17857) and the event was not removed even though execution time has expired.
The event is a valid one. If you perform an ALTER EVENT statement, changing the scheduled time, it will work.

Suggested fix:
Event definitions should be replicated, if we want a slave to be a reliable master candidate.
However, there must be some flag to indicate that an event should only work in the master or in both master and slaves.
[2 Mar 2006 14:38] Valeriy Kravchuk
Thank you for a problem report. Are you sure that 

SET GLOBAL event_scheduler = ON;

was executed on slave? Event scheduler simply does not work by default. Please, check.
[2 Mar 2006 14:43] Valeriy Kravchuk
Oh, sorry - my fault. I missed that first statement in your test case. According to your further statements ("If you perform an ALTER EVENT statement, changing the scheduled time, it will work.") scheduler got activated through replication... Are you sure that time zone/time is the same on slave as on master?
[2 Mar 2006 14:46] Giuseppe Maxia
Important addition.
The behavior described in this bug report happens only with row-based replication.
When using statement-based replication, the events are correctly removed from mysql.event, and they don't show up in the SHOW EVENTS command.
[2 Mar 2006 14:57] Giuseppe Maxia
Further information, as requested.
Both the master and slave have  "event_scheduler = 1" in their configuration.
Both are using the same time zone. They are in fact, for the purpose of this test, in the  same machine, using the same basedir to different data directories (with different port and socket).

Here are the relative configuration files.

#master my.cnf
[mysqld]
port            = 10010
socket          = /tmp/mysqlcluster1master.sock
server-id       = 10
basedir         = /usr/local/mysql/
datadir         = /tests/cluster1/master/data
log_bin_trust_function_creators=1
log-bin
binlog_format   = row
event_scheduler = 1

#slave my.cnf
[mysqld]
port            = 10020
socket          = /tmp/mysqlcluster1slave20.sock
server-id       = 20
basedir         = /usr/local/mysql/
datadir         = /tests/cluster1/slave20/data
log-bin
log_bin_trust_function_creators=1
master-host     = 127.0.0.1
master-port     = 10010
master-user     = cluster1_slave20
master-password = #MYPASSWORD#
report-host     = slave_20
binlog_format   = row
event_scheduler = 1
[26 Apr 2006 9:51] Valeriy Kravchuk
Sorry for a long delay with this bug report. Please, try to repeat with a newer version, 5.1.9-beta, and inform about the results.
[26 May 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[27 May 2006 5:01] Giuseppe Maxia
Tested again with 5.1.9, 5.1.10 and 5.1.12.
The bug does not show up. It seem to be fixed.
Thanks
Giuseppe
[27 May 2006 6:55] Valeriy Kravchuk
Looks like the problem is not repeatable with 5.1.9 and newer versions.