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 |
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_
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 |
Changed in codership-mysql: | |
status: | Triaged → In Progress |
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_