When I create an event on the master it is replicated to the slave BUT it is set to 'DISABLE ON SLAVE' there and thus not executed on slave. This is documented and fine so far.
DELIMITER //
|
|
CREATE OR REPLACE DEFINER = `dbstat`@`localhost` EVENT purge_table_size
|
ON SCHEDULE EVERY 5 MINUTE
|
ENABLE
|
DO
|
BEGIN
|
DO 'nothing';
|
END;
|
//
|
|
DELIMITER ;
|
|
SELECT db, name, definer, execute_at, CONCAT(interval_value, ' ', interval_field) AS 'interval'
|
, created, modified, last_executed, starts, ends, status, on_completion
|
FROM mysql.event
|
ORDER BY db, name ASC
|
;
|
+------+------------------+------------------+------------+----------+---------------------+---------------------+---------------+---------------------+------+--------------------+---------------+
|
| db | name | definer | execute_at | interval | created | modified | last_executed | starts | ends | status | on_completion |
|
+------+------------------+------------------+------------+----------+---------------------+---------------------+---------------+---------------------+------+--------------------+---------------+
|
| test | purge_table_size | dbstat@localhost | NULL | 5 MINUTE | 2024-03-27 17:03:50 | 2024-03-27 17:03:50 | NULL | 2024-03-27 16:03:50 | NULL | SLAVESIDE_DISABLED | DROP |
|
+------+------------------+------------------+------------+----------+---------------------+---------------------+---------------+---------------------+------+--------------------+---------------+
|
But in some cases one wants to have the events also enabled on the slave.
In this case I have to enable the event on the slave as well with:
ALTER EVENT event_name ENABLE;
|
This has now 2 problems:
1.) Whenever I REPLACE the event on Master I have to think about to re-enable the event on the slave again. This can lead to errors and is not idempotent/reentrant. So I can hardly do this automatized...
2.) In a Master/Master topology, when I run the ALTER EVENT ... ENABLE on the passive Master it will deactivate the event on the active Master again. So I have to work with sql_log_bin = off which is also dangerous. So not idempotent/reentrant as well.
So we have some problems in this situation which are dangerous for production systems...
My suggestion is to add a new functionality: ENDABLE ON SLAVE which does the opposite of DISABLE ON SLAVE: It enables the event on master AND slave.
https://mariadb.com/kb/en/create-event/
New Feature
Major
