[MDEV-19165] EVENT can be SLAVESIDE_DISABLED on all nodes after Galera SST Created: 2019-04-03  Updated: 2023-10-12

Status: Stalled
Project: MariaDB Server
Component/s: Galera, Galera SST, wsrep
Affects Version/s: 10.1, 10.2
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Muhammad Irfan Assignee: Julius Goryavsky
Resolution: Unresolved Votes: 1
Labels: None


 Description   

In MariaDB Galera Cluster, when node which is "owner" of EVENT gets executed fine and that EVENT is SLAVESIDE_DISABLED on other nodes of cluster which seems correct.
However, when somehow "owner" of the EVENT node gets SST from other nodes of cluster then event status becomes SLAVESIDE_DISABLED too which seems incorrect.
It should get status back to ENABLED on SST on "originating" node of EVENT.

I think event status should be updated accordingly when mysqld starts up and load events and check originator of event via server_id may be.

[root@galera-node-3 /]#
 
MariaDB [test]> CREATE TABLE `t1` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `time` datetime DEFAULT NULL,  PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
Query OK, 0 rows affected (0.03 sec)
 
 
 
MariaDB [test]> CREATE EVENT e_min ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO test.t1 values (null,now());
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> show events\G
 
*************************** 1. row ***************************
 
                  Db: test
 
                Name: e_min
 
             Definer: root@localhost
 
           Time zone: SYSTEM
 
                Type: RECURRING
 
          Execute at: NULL
 
      Interval value: 1
 
      Interval field: MINUTE
 
              Starts: 2019-04-03 19:42:46
 
                Ends: NULL
 
              Status: ENABLED
 
          Originator: 0
 
character_set_client: utf8
 
collation_connection: utf8_general_ci
 
  Database Collation: latin1_swedish_ci
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1;
 
+----+---------------------+
 
| id | time                |
 
+----+---------------------+
 
|  6 | 2019-04-03 19:49:46 |
 
|  9 | 2019-04-03 19:50:31 |
 
| 12 | 2019-04-03 19:51:31 |
 
+----+---------------------+
 
3 rows in set (0.00 sec)
 
 
 
[root@galera-node-2 /]#
 
MariaDB [test]> show events\G
 
*************************** 1. row ***************************
 
                  Db: test
 
                Name: e_min
 
             Definer: root@localhost
 
           Time zone: SYSTEM
 
                Type: RECURRING
 
          Execute at: NULL
 
      Interval value: 1
 
      Interval field: MINUTE
 
              Starts: 2019-04-03 19:42:46
 
                Ends: NULL
 
              Status: SLAVESIDE_DISABLED
 
          Originator: 0
 
character_set_client: utf8
 
collation_connection: utf8_general_ci
 
  Database Collation: latin1_swedish_ci
 
1 row in set (0.00 sec)
 
 
 
[root@galera-node-1 /]#
 
MariaDB [test]> show events\G
 
*************************** 1. row ***************************
 
                  Db: test
 
                Name: e_min
 
             Definer: root@localhost
 
           Time zone: SYSTEM
 
                Type: RECURRING
 
          Execute at: NULL
 
      Interval value: 1
 
      Interval field: MINUTE
 
              Starts: 2019-04-03 19:42:46
 
                Ends: NULL
 
              Status: SLAVESIDE_DISABLED
 
          Originator: 0
 
character_set_client: utf8
 
collation_connection: utf8_general_ci
 
  Database Collation: latin1_swedish_ci
 
1 row in set (0.01 sec)
 
 
 
Now, trigger SST on node3 which is "owner" of event.
 
 
 
[root@galera-node-3 mysql]# rm -fr *
 
[root@galera-node-3 mysql]# /etc/init.d/mysql start
 
Starting MySQL.190403 19:55:16 mysqld_safe Logging to '/var/lib/mysql/galera-node-3.err'.
 
190403 19:55:16 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
 
....... SUCCESS! 
 
 
MariaDB [test]> show events\G
 
*************************** 1. row ***************************
 
                  Db: test
 
                Name: e_min
 
             Definer: root@localhost
 
           Time zone: SYSTEM
 
                Type: RECURRING
 
          Execute at: NULL
 
      Interval value: 1
 
      Interval field: MINUTE
 
              Starts: 2019-04-03 19:50:31
 
                Ends: NULL
 
              *Status: SLAVESIDE_DISABLED*
 
          Originator: 0
 
character_set_client: utf8
 
collation_connection: utf8_general_ci
 
  Database Collation: latin1_swedish_ci
 
1 row in set (0.00 sec)



 Comments   
Comment by Geoff Montee (Inactive) [ 2019-04-03 ]

I think event status should be updated accordingly when mysqld starts up and load events and check originator of event via server_id may be.

I don't think server_id would work for everyone. It is common to set server_id to the same value on all nodes in a given cluster.

Comment by Jan Lindström (Inactive) [ 2019-09-05 ]

MariaDB cluster disables events in "slave nodes", but does not automatically enable disabled events e.g. after SST.

Comment by Jan Lindström (Inactive) [ 2019-09-05 ]

greenman Can you please add to documentation.

Comment by Rick Pizzi [ 2020-08-07 ]

You can execute ALTER event from init-file on the node where the event is originally created to re-enable it after an SST.

ALTER DEFINER=‘root’@‘localhost’ EVENT test.myevent enable;

Comment by Julius Goryavsky [ 2020-08-11 ]

Re-opened because after restarting the server on node which created the event, this event has the status SLAVESIDE_DISABLED on all nodes of the cluster, but If in the initial state of the event it was ENABLED, then after restart of the node it must remain ENABLED

Comment by Claudio Nanni [ 2020-09-07 ]

I think init-file is not a safe long term solution, a node might disappear forever for any reason.

While a Galera only based solution would be to enable all events in the SST script after SST completed, that will make sure that events are always enabled after SST (and disabled on the other nodes).

If MaxScale is used then a solution would come if this is implemented:

https://jira.mariadb.org/browse/MXS-3161

The two solutions conflict thou because MaxScale would move the status only on Master switch, so in case of SST a Slave would got them ENABLED. Nevertheless data would be safe as long the event is executed on one node only.
MaxScale might add to 3161 a periodical(1 hour?) ALTER EVENT ... ENABLE on the Master.

Generated at Thu Feb 08 08:49:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.