Replication of events is not consistent with MySQL
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership | Status tracked in 5.6 | |||||
| 5.5 |
Undecided
|
Yan Zhang | |||
| 5.6 |
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_
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
description: | updated |
Przemek (pmalkowski) wrote : | #1 |
Przemek (pmalkowski) wrote : | #2 |
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://
Yan Zhang (yan.zhang) wrote : | #3 |
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_
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_
4. If event owner node is re-created using SST, all the nodes have the event now in "SLAVESIDE_
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) |
Changed in codership-mysql: | |
status: | Triaged → In Progress |
Yan Zhang (yan.zhang) wrote : | #4 |
fixed for 5.5.
p1. (one time event is not dropped on slave nodes) http://
p2. (slave node definer is empty when alter event on master node) http://
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://
Yan Zhang (yan.zhang) wrote : | #5 |
few additional fixes for 5.5
p2. (add `remember_name` in `alter event` grammar to support recording stmt definition)
http://
http://
fixed for 5.6
p1. http://
p2. http://
p3. http://
Shahriyar Rzayev (rzayev-sehriyar) wrote : | #6 |
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/
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. SLAVESIDE_ DISABLED.
In PXC 5.6, the event gets re-created but in Status=
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
Type: RECURRING
Starts: 2014-04-26 11:45:19
Ends: NULL
Status: ENABLED
Originator: 0 set_client: latin1 connection: latin1_swedish_ci
*******
Time zone: SYSTEM
Execute at: NULL
Interval value: 1
Interval field: MINUTE
character_
collation_
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
Type: RECURRING
Starts: 2014-04-26 11:45:19
Ends: NULL
Status: SLAVESIDE_DISABLED
Originator: 0 set_client: latin1 connection: latin1_swedish_ci
*******
Time zone: SYSTEM
Execute at: NULL
Interval value: 1
Interval field: MINUTE
character_
collation_
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 DEFINITION: INSERT INTO test.t1 values (null,now( ),@@SERVER_ ID)
EVENT_ TYPE: RECURRING
EXECUTE_ AT: NULL...
*******
EVENT_