MySQL Bugs: #31643: Setting an event to 'disable on slave' disables it on the master

Bug #31643	Setting an event to 'disable on slave' disables it on the master
Submitted:	16 Oct 2007 15:26	Modified:	3 Dec 2008 14:28
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The 'disable on slave' status is intended as an 'internal' status on a slave server to indicate that the events should not be executed on the slave and to indicate that they can be renewed in the case the slave needs to 'take over' for a failing master (see http://dev.mysql.com/doc/refman/5.1/en/replication-features-invoked.html).

Currently, when setting this status on an event running on the master, it behaves as if the event is disabled.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.22-rc-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> create table tb1 (dt datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> create event ev1 on schedule every 10 second do insert into tb1 values (now());
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep (10);
+------------+
| sleep (10) |
+------------+
|          0 |
+------------+
1 row in set (10.01 sec)

mysql> select * from tb1;
+---------------------+
| dt                  |
+---------------------+
| 2007-10-16 18:06:11 |
| 2007-10-16 18:06:21 |
+---------------------+
2 rows in set (0.00 sec)

mysql> alter event ev1 disable on slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(20);
+-----------+
| sleep(20) |
+-----------+
|         0 |
+-----------+
1 row in set (20.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2007-10-16 18:06:41 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from tb1;
+---------------------+
| dt                  |
+---------------------+
| 2007-10-16 18:06:11 |
| 2007-10-16 18:06:21 |
+---------------------+
2 rows in set (0.00 sec)

>> The event should have been executed at '18:06:31' 

Note: The problem is demonstrated using alter but the same is observed if creating the event with a status of 'disable on slave' to begin with -- it is not executed

How to repeat:
1) Start a MySQL server with events enabled and log into the mysql client.
2) Execute the following sql:
use test;
create table tb1 (dt datetime);
create event ev1 on schedule every 10 second do insert into tb1 values (now());
select sleep (10);
select * from tb1;
alter event ev1 disable on slave;
select sleep(20);
select now();
select * from tb1;

Suggested fix:
Setting to 'disable on slave' should not be allowed using the create/alter event commands.

Give a syntax error in the parser unless thd->slave_thread.

What if you have a master-master setup. Which would be the true "master"? 

I believe that the ALTER EVENT DISABLE ON SLAVE should only take affect on the host you are on, and it should not be replicated, which is NOT what it currently does. 

The ALTER EVENT ENABLE should enable on the host you are on and slave disable everywhere else, which is what it currently does.

The event should be disabled on the slave, if not, then it will run there and we will get double executions.

Any plans to fix it?  I am using 5.1.54 - I guess the way around is to disable scheduler on slave.

Bug still on 8.0.20

same bug in 8.0.22

Bug still exists in 8.0.28. For large corporations that are using MySql, this is a fairly big issue. As we cannot use any scheduled events built into MySql, as there is no way to run it on primary server and not on replicas. All of our servers are redundant with replicas.