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/ |
Hi André
I tried to download your branch and build the server to test your change. But I failed. I am not so familiar (any more) with building MariaDB server and need your help. What I did:
- Check docu: https://mariadb.com/kb/en/get-build-and-test-latest-mariadb-the-lazy-way/
- git clone https://github.com/andremralves/server.git mariadb-MDEV-33782
- cd mariadb-MDEV-33782
- git checkout 11.5
- cmake . -DBUILD_CONFIG=mysql_release && make -j8
After installing some missing dev packages I got this error and I do not know what is missing:
...
– Documentation included from
– Required: zlib;dl;m
– Could NOT find Boost (missing: Boost_INCLUDE_DIR chrono filesystem program_options regex system thread) (Required is at least version "1.81.0")
== MariaDB-Columnstore 23.10.0
– Could NOT find CURL (missing: CURL_LIBRARY CURL_INCLUDE_DIR)
– Found Snappy: /usr/lib/x86_64-linux-gnu/libsnappy.so
– Could NOT find CURL (missing: CURL_LIBRARY CURL_INCLUDE_DIR)
– Could NOT find BZip2 (missing: BZIP2_LIBRARIES BZIP2_INCLUDE_DIR)
– Could NOT find LZ4 (missing: LZ4_LIBRARIES LZ4_INCLUDE_DIRS) (Required is at least version "1.6")
– Could NOT find LZO (missing: LZO_LIBRARIES LZO_INCLUDE_DIRS)
– Found Snappy: /usr/lib/x86_64-linux-gnu/libsnappy.so
CMake Error at cmake/plugin.cmake:330 (MESSAGE):
Plugin AUTH_PAM cannot be built
Call Stack (most recent call first):
CMakeLists.txt:449 (CONFIGURE_PLUGINS)
– Configuring incomplete, errors occurred!
See also "/tmp/tmp/mariadb-MDEV-33782/CMakeFiles/CMakeOutput.log".
See also "/tmp/tmp/mariadb-MDEV-33782/CMakeFiles/CMakeError.log".
I tried to build in an Ubuntu 23.04 container now and cmake has passed with only one error I had to fix. I will file a but about this... Compiling now...
Hi André
It tested it on my machine and it worked fine for my requirements (dbstat) in a Master/Slave set-up. I did NOT test it in a Galera Cluster!
Thank you for implementing this...
| 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 ] |
Now that we're viewing this new feature, I first looked around to have come across Bug#31643 (thanks wenhug for bring it up) which must've made wondering about disable_on_slave semantics.
Indeed this specific disabling could've been designed with prompting the slave to say its preference, rather than decide at once on master.
Compare with slave_run_triggers_for_rbr.
It feels we'd rather fix Bug#31643 with adopting the slave trigger pattern of control from the slave side.
And the same would apparently applicable to this new feature.
Why won't we add a (slave) server system variable instead?
Would its setting on slave suit your practical goals dear gentlemen?
Hmmm. I prefer the ALTER EVENT ... ENABLE ON SLAVE. It feels more natural and is the opposite of the ALTER EVENT ... DISABLE ON SLAVE. So it is just intuitive. Further I can control it in/from my application (thinking in the Dev role) and I am not dependent on the Ops. And last a variable is something which kicks in unexpectedly in the background I have to think about as a Dev.
I am not against a variable in addition but one more?
So my use case is/was: I want to rollout the EVENT in my application deciding that it should also run on Slaves or Galera nodes...
oli, my point is that the DISABLE ON SLAVE pattern is as much ill-conceived that it makes no sense to compare with it. The referred bug corroborates that. And secondly the comparison with the option to activate triggers I thought would be revealing. Modes of slave server execution are normally controlled by slave configurations. This exceptional case is the only one that I knew.
I hope I explained why we should not multiply that mishap.
Your endorsement of the system variable encourages.
But I could not understand your reference to Ops, sorry I am unaware of details I need to know. I think I understood now, the master side session variable is offered below for the "Dev" role.
(Offtopic, but we - users like you and we the server - are in trouble then as your type would be unable to configure the slave triggers...)
If you mean that it's easier for you to control the slave side with logging CREATE or ALTER event carrying an instruction to activate its execution on slave, I might additionally consider a new master@@session var, to be used e.g as
mysql> set statement activate_on_slave for create event ...
would put into binlog
create event ... ENABLE.
With "Ops" I mean Operations (DBAs, Unix administrators). In reality you have very often a separation between Developers (Devs) and Operations (Ops). DevOps in many company is not implemented or sometimes perverted. Sometimes you even have application operations which is neither Dev nor Ops but just operates the application (install and troubleshoot).
So when I, as a Developer (or as a application operator), create an application or roll the application out (install it) I do not want to be dependent on Operations (DBA, Unix admin) and their database settings (server variables) because I cannot influence them. I want to have it under my Dev control how my application behaves. I have seen it too many times that Ops has no clue what they are doing or no time to do it right.
So yes, for me as a Dev it is easier to control it with SQL means than with server variables.
PS: In my heart I am an Ops! :-D
Hmm...
With the SET STATEMENT/SESSION I (as a Dev) have it under control. OK. So my requirement is met.
The only concern I have is, that MariaDB becomes more complicated with this. Another variable I have to know/to remember. ... ENABLE ON SLAVE ... just feels so natural even thougth it is ill-conceived...
oli, thanks. I slowly got it. Just consider the user var method. The var's scope can be extended to cover other features like triggers as the Dev class' control.
> Another variable I have to know/to remember
The var is of a more general scope though none yet asked yet to control the slave trigger kinda your way.
I lean now toward introducing an internal to EVENT option (that you would have to remember 
)
that would do the same. Something like
create event .. log_as [DISABLE|ENABLE]
would make the binlogged create-event have one of DISABLE or ENABLE;
Naturally mysql.event would simplify its status to a binary enum('ENABLED','DISABLED').
Oli, can you help by providing example use case(s) of an event that would be useful to replicate from the master to the slave and have the event be active on both? That could help others understand how the feature could be used.
For events that modify tables (which I assume is common), such changes are replicated by default, is that right? This suggests that most triggers will not want to be replicated as active onto the slaves, to avoid duplicating those modifications on a slave. And that the property of being created active on a slave is a property of the event and thus the option on CREATE EVENT makes sense.
Could there be use cases where the property of wanting the trigger active is a property of the slave? I could imagine that, for example master<->master replication with secondary read-only slaves hanging off the two masters. Maybe there's both a CREATE EVENT option needed for the SQL syntax, and a global variable for the slaves whether to allow events to be active/be replicated as active?
I don't see a final clear answer to what is "right" here. It feels like the replication of events is quite a tricky thing, and probably the current behaviour does not have a very polished and clear semantics. That at least suggests to pick a solution that is as simple as possible both to understand for the user and to implement in the server.
- Kristian.
Kristian, the use case we stumbled over it was dbstat for MariaDB: fromdual.com/dbstat-for-mariadb-and-mysql
"This went quite well until we came up with the idea of activating dbstat on the passive dbstat node on our MariaDB active/passive master/master replication cluster (a similar situation would also occur with a Galera cluster). We realised that the design of dbstat still had potential. After this problem was fixed (v0.0.2 and v0.0.3) and the problem of how to activate events on master AND slave was solved (MDEV-33782: Event is always disabled on slave), everything seemed fine at first glance."
https://fromdual.com/dbstat-for-mariadb-after-one-month-of-productive-use
Uses case of the server EVENT that runs on master and slave and possibly executes non-deterministically on master and slave can be imagined.
Oli, perhaps in the case like you describe replication events produced by server EVENTs should not be either generated or replicated. And for that there're already possibly helpful options of course.
Anyway, back to the slave side server EVENT activation, the Dev preference can be addressed with
a new CREATE|ALTER-event *logging* option. This is a lighter approach (does not extend the Event class, rather reduces it in its state member) than one in the PR. And the Ops' method could base on the slave_run_triggers_for_rbr pattern.
When two clashes i.e log_as is in conflict with @@global.slave_run_events (tentatively name the new var) I would favor the latter which is the Ops' one as the winner.
Opinions?
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.
@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?
| 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 ] |
Hi Oli Sennhauser, I made a PR implementing this feature, maybe you could give your thoughts about the implementation or test it.
PR: https://github.com/MariaDB/server/pull/3174