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.
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
INTERVAL_VALUE: 1
INTERVAL_FIELD: MINUTE SQL_MODE: STARTS: 2014-04-26 11:45:19 ENDS: NULL STATUS: SLAVESIDE_DISABLED ON_COMPLETION: NOT PRESERVE CREATED: 2014-04-26 11:45:19 LAST_ALTERED: 2014-04-26 11:50:56 LAST_EXECUTED: NULL EVENT_COMMENT: ORIGINATOR: 1
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)
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 VALUE: 1 FIELD: MINUTE
SQL_ MODE:
STARTS: 2014-04-26 11:45:19
ENDS: NULL
STATUS: SLAVESIDE_DISABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2014-04-26 11:45:19
LAST_ALTERED: 2014-04-26 11:50:56
LAST_EXECUTED: NULL
EVENT_COMMENT:
ORIGINATOR: 1 SET_CLIENT: latin1 CONNECTION: latin1_swedish_ci COLLATION: latin1_swedish_ci
*******
EVENT_
INTERVAL_
INTERVAL_
CHARACTER_
COLLATION_
DATABASE_
1 row in set (0.00 sec)