Replication of events is not consistent with MySQL

[Przemek]

Following behaviour is not consistent with normal MySQL replication (ROW based):
1 - "ONE TIME" events are not dropped on slave nodes after expiring on master (event creator node)
2 - After doing ALTER EVENT, slave nodes end up with empty Definer "@"

Following Issues with SST probably need documenting and/or warnings in error log:
 - After SST from master node (the one where event is ENABLED) - you will end up with the event enabled on two nodes, hence it's now being executed twice
 - If event owner node is re-created using SST, all the nodes have the event now in "SLAVESIDE_DISABLED" state, hence manual action is needed to re-enable the event on one of the nodes.

Especially having an event ENABLED on more then one node may be problematic and maybe should be automatically checked and disabled on joiner?

Happens on both PXC 5.5.34 and 5.6.15

[Przemek (pmalkowski)]

Another unexpected behaviour, which happens only for PXC 5.5 tough - when a node that is the event "owner", so the only one that has Status=ENABLED, is re-created using SST from any other node, it looses the event information.
In PXC 5.6, the event gets re-created but in Status=SLAVESIDE_DISABLED.

Simple test scenario:

percona1 mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` datetime DEFAULT NULL,
  `server` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

  percona1 mysql> CREATE EVENT e_min ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO test.t1 values (null,now(),@@SERVER_ID);
Query OK, 0 rows affected (0.05 sec)

percona1 mysql> 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: 2014-04-26 11:45:19
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

percona5 mysql> 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: 2014-04-26 11:45:19
                Ends: NULL
              Status: SLAVESIDE_DISABLED
          Originator: 0
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

percona2 mysql> select * from t1;
+----+---------------------+--------+
| id | time | server |
+----+---------------------+--------+
| 2 | 2014-04-26 11:53:19 | 1 |
| 11 | 2014-04-26 11:54:19 | 1 |
| 20 | 2014-04-26 11:55:19 | 1 |
+----+---------------------+--------+
3 rows in set (0.00 sec)

[root@percona1 ~]# /etc/init.d/mysql stop
Shutting down MySQL (Percona XtraDB Cluster)....... SUCCESS!
[root@percona1 ~]# rm -fr /var/lib/mysql/*

[root@percona1 ~]# /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster).....SST in progress, setting sleep higher
... SUCCESS!
[root@percona1 ~]# mysql test
(...)
percona1 mysql> show events;
Empty set (0.00 sec)

percona1 mysql> select * from information_schema.events;
Empty set (0.00 sec)

percona5 mysql> select * from information_schema.events\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: test
          EVENT_NAME: e_min
             DEFINER: @
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: INSERT INTO test.t1 values (null,now(),@@SERVER_ID)
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL...

[Przemek (pmalkowski)]

Btw the difference between 5.5 and 5.6 in terms of events being lost, is due to this bug being fixed only in 5.6: http://bugs.mysql.com/bug.php?id=37666

[Yan Zhang (yan.zhang)]

So there are five problems here. I think to fix any one of them needs some efforts because it can't be fixed in wsrep patch only but needs some changes in MySQL code too.

1. "ONE TIME" events are not dropped on slave nodes after expiring on master (event creator node)
This is because when event scheduler drop one-time event, it does not call TOI to trigger replication.

2. After doing ALTER EVENT, slave nodes end up with empty Definer "@"
It's because wsrep_TOI_begin does not save 'definer' information into trx hold by galera (more precisely, Query_log_event::write does not pass 'definer' into IO_CACHE)

3. After SST from master node (the one where event is ENABLED) - you will end up with the event enabled on two nodes, hence it's now being executed twice.
This can be a feature(?). It needs to be well documented. However to my mind, it can be solved by comparing event's originator with server_id. if not equal, then change its status to 'SLAVESIDE_DISABLED'

4. If event owner node is re-created using SST, all the nodes have the event now in "SLAVESIDE_DISABLED" state, hence manual action is needed to re-enable the event on one of the nodes.
This can be a feature too(?). It needs to be well documented. However compared to previous problem, It much harder to solve. Because you can not set event's status to 'ENABLE' in master simply.

5. MySQL 5.5 loses event if this event is SLAVESIDE_DISABLED and replicated from slaves.

[Yan Zhang (yan.zhang)]

fixed for 5.5.

p1. (one time event is not dropped on slave nodes) http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3994

p2. (slave node definer is empty when alter event on master node) http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3992

p3. (when mysqld starts up and loads events, check originator is equal to server id or not. if not equal, change status to slaveside_disabled) http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3993

[Yan Zhang (yan.zhang)]

few additional fixes for 5.5

p2. (add `remember_name` in `alter event` grammar to support recording stmt definition)
http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3995
http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3996

fixed for 5.6
p1. http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/4094
p2. http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/4092
p3. http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/4093

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-999