| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.7 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.