Details
-
New Feature
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
Description
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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
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/ |
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.
{code:sql} 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 ; {code} {noformat} +------+------------------+------------------+------------+----------+---------------------+---------------------+---------------+---------------------+------+--------------------+---------------+ | 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 | +------+------------------+------------------+------------+----------+---------------------+---------------------+---------------+---------------------+------+--------------------+---------------+ {noformat} 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: {code:sql} ALTER EVENT event_name ENABLE; {code} 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/ |
| Fix Version/s | 11.6 [ 29515 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Sergei Golubchik [ serg ] |
| Assignee | Sergei Golubchik [ serg ] | Andrei Elkin [ elkin ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Fix Version/s | 11.7 [ 29815 ] | |
| Fix Version/s | 11.6 [ 29515 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Assignee | Andrei Elkin [ elkin ] | Andrew Hutchings [ JIRAUSER52179 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 11.8 [ 29921 ] | |
| Fix Version/s | 11.7 [ 29815 ] |
| Status | Stalled [ 10000 ] | Open [ 1 ] |
| Assignee | Andrew Hutchings [ JIRAUSER52179 ] | Sergei Golubchik [ serg ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Assignee | Sergei Golubchik [ serg ] | Andrei Elkin [ elkin ] |
| Fix Version/s | 11.9 [ 29945 ] | |
| Fix Version/s | 11.8 [ 29921 ] |
| Labels | beginner-friendly event replication | beginner-friendly contribution event replication |
| Status | Open [ 1 ] | Needs Feedback [ 10501 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Status | Needs Feedback [ 10501 ] | Open [ 1 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Fix Version/s | 12.1 [ 29992 ] | |
| Fix Version/s | 12.0 [ 29945 ] |