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
@Andrei I favour the Dev variant. 
But at the end it does not matter...
Hi all,
I completely agree that DISABLE ON SLAVE was ill-conceived and unintuitive for users. I was curious about why it was implemented this way and also found that bug report, but since it was from 2007, I assumed people were just fine with this behavior.
Now that I know fixing Bug#31643 is preferable, I agree that adding ENABLE ON SLAVE would only complicate things further.
If I understand correctly, Andrei's initial idea was to create something similar to how it's done with triggers using global variables, but that wouldn't meet Oli's development requirements since a SQL command would be preferable.
This led Andrei to propose another solution: a new option for EVENTS that simply specifies how we should replicate this event on slaves—either ENABLED or DISABLED. I liked this idea.
At the end of the day, as Andrei mentioned, both solutions could be implemented (the Dev's and the Ops'). If both are set, we prioritize the Ops' solution, which uses global variables.
This approach sounds good to me. I think it would be worthwhile to modify my PR to implement these ideas. However, I will be able to give this issue more attention after I finish my GSoC project, MDEV-30645.
Thanks Andre for a good resume.
I am all but certain on your old decision assumption. There were no event replication users to ask from either at that time.
Hi Andrew.
This work is going to be improved. Andre agreed to re-arrange the patch. I am not full sure, but I think meanwhile until a new PR request the owner of this MDEV should you, correct?
Thanks, Oli. This makes sense, IIUC the event is populating local tables with data about the local instance. I assume the event sets sql_log_bin=0 or something to not replicate its dbstat tables.
So in this case, indeed it seems a property of the event (and the decision of the event developer) that it is something that wants to be active also on the slaves.
On the other hand, installing a monitoring tool on a server is not something I normally would expect to use MariaDB replication for? It is not clear to me why you mentioned it as risky to install the dbstat separately on each server with sql_log_bin = off. Still, this is always a difficult line to draw when using things like stored procedures, events, etc. in SQL databases, when something is part of the data, and when it is part of the software deployment.