Replication of events is not consistent with MySQL

Bug #1312618 reported by Przemek
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Status tracked in 5.6
5.5
Fix Committed
Undecided
Yan Zhang
5.6
Fix Released
Undecided
Yan Zhang
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC
Status tracked in 5.6
5.5
Fix Released
High
Unassigned
5.6
Fix Released
High
Unassigned

Bug Description

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

Tags: i41521
Przemek (pmalkowski)
description: updated
Revision history for this message
Przemek (pmalkowski) wrote :
Download full text (3.6 KiB)

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...

Read more...

Revision history for this message
Przemek (pmalkowski) wrote :

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

Revision history for this message
Yan Zhang (yan.zhang) wrote :

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.

Changed in codership-mysql:
status: New → Triaged
assignee: nobody → Yan Zhang (yan.zhang)
Yan Zhang (yan.zhang)
Changed in codership-mysql:
status: Triaged → In Progress
Revision history for this message
Yan Zhang (yan.zhang) wrote :

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

Revision history for this message
Yan Zhang (yan.zhang) wrote :
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.