Description:
If you make use of MySQL Events to carry out queries at set times, then you generally want those events to run on the master database, and then replicate to the slaves.
If you have setup a replicaset using MySQL 8.0.19 and MySQL Shell you have the option to use setPrimaryInstance() to promote a slave database to be the new master. Doing so however, still leaves all events running on the original database (now a slave) where they will fail to run due to being on a database that is 'super_read_only'=1.
It would be useful to have the option to update all events at the same time as promoting the database, so they now run on the new master.
How to repeat:
Set up a replicatSet with two databases Master (3306) and Slave (3307).
Set up an event on the master (3306). e.g.
DELIMITER $$
CREATE DEFINER=`username`@`%` EVENT `event_test` ON SCHEDULE EVERY 1 MINUTE STARTS NOW() ON COMPLETION PRESERVE ENABLE COMMENT 'test' DO BEGIN
insert into test.table1 values (1);
END$$
DELIMITER ;
Check the Master (3306) is showing as enabled and the Slave (3307) is showing the event as 'slaveside_disabled'.
MASTER:
+------------+---------+
| EVENT_NAME | STATUS |
+------------+---------+
| event_test | ENABLED |
+------------+---------+
SLAVE:
+------------+--------------------+
| EVENT_NAME | STATUS |
+------------+--------------------+
| event_test | SLAVESIDE_DISABLED |
+------------+--------------------+
Change 3307 to become the new master
MySQL localhost:3306 ssl JS> rs.setPrimaryInstance('DB1:3307')
Now connect to the Master (now 3307) and check the event
+------------+--------------------+
| EVENT_NAME | STATUS |
+------------+--------------------+
| event_test | SLAVESIDE_DISABLED |
+------------+--------------------+
Connect to the Slave (now 3306) and check the event
+------------+---------+
| EVENT_NAME | STATUS |
+------------+---------+
| event_test | ENABLED |
+------------+---------+
Suggested fix:
One option could be to have it as an option on setPrimaryInstance. e.g:
rs.setPrimaryInstance('dbname:3306',{moveAllEvents: true})
Alternatively could be the option to run a custom script, that contains the SQL required to move the Events, and anything else that may be required.
rs.setPrimaryInstance('dbname2:3306',{scriptOnNewMaster: /tmp/myscript.sql})
This option could be useful in other circumstances as well if you need to set other flags on the new master.
Description: If you make use of MySQL Events to carry out queries at set times, then you generally want those events to run on the master database, and then replicate to the slaves. If you have setup a replicaset using MySQL 8.0.19 and MySQL Shell you have the option to use setPrimaryInstance() to promote a slave database to be the new master. Doing so however, still leaves all events running on the original database (now a slave) where they will fail to run due to being on a database that is 'super_read_only'=1. It would be useful to have the option to update all events at the same time as promoting the database, so they now run on the new master. How to repeat: Set up a replicatSet with two databases Master (3306) and Slave (3307). Set up an event on the master (3306). e.g. DELIMITER $$ CREATE DEFINER=`username`@`%` EVENT `event_test` ON SCHEDULE EVERY 1 MINUTE STARTS NOW() ON COMPLETION PRESERVE ENABLE COMMENT 'test' DO BEGIN insert into test.table1 values (1); END$$ DELIMITER ; Check the Master (3306) is showing as enabled and the Slave (3307) is showing the event as 'slaveside_disabled'. MASTER: +------------+---------+ | EVENT_NAME | STATUS | +------------+---------+ | event_test | ENABLED | +------------+---------+ SLAVE: +------------+--------------------+ | EVENT_NAME | STATUS | +------------+--------------------+ | event_test | SLAVESIDE_DISABLED | +------------+--------------------+ Change 3307 to become the new master MySQL localhost:3306 ssl JS> rs.setPrimaryInstance('DB1:3307') Now connect to the Master (now 3307) and check the event +------------+--------------------+ | EVENT_NAME | STATUS | +------------+--------------------+ | event_test | SLAVESIDE_DISABLED | +------------+--------------------+ Connect to the Slave (now 3306) and check the event +------------+---------+ | EVENT_NAME | STATUS | +------------+---------+ | event_test | ENABLED | +------------+---------+ Suggested fix: One option could be to have it as an option on setPrimaryInstance. e.g: rs.setPrimaryInstance('dbname:3306',{moveAllEvents: true}) Alternatively could be the option to run a custom script, that contains the SQL required to move the Events, and anything else that may be required. rs.setPrimaryInstance('dbname2:3306',{scriptOnNewMaster: /tmp/myscript.sql}) This option could be useful in other circumstances as well if you need to set other flags on the new master.