Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33782

Event is always disabled on slave

Details

    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/

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          View 16 older comments
          knielsen Kristian Nielsen added a comment -

          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.

          knielsen Kristian Nielsen added a comment - 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.
          oli Oli Sennhauser added a comment -

          @Andrei I favour the Dev variant. But at the end it does not matter...

          oli Oli Sennhauser added a comment - @Andrei I favour the Dev variant. But at the end it does not matter...
          andre André Alves added a comment -

          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.

          andre André Alves added a comment - 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 .
          Elkin Andrei Elkin added a comment -

          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.

          Elkin Andrei Elkin added a comment - 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.
          Elkin Andrei Elkin added a comment -

          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?

          Elkin Andrei Elkin added a comment - 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?

          People

            Elkin Andrei Elkin
            oli Oli Sennhauser
            Votes:
            2 Vote for this issue
            Watchers:
            10 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.