Debian Codegraph @

mysql-test/suite/galera/t/galera_events2.test

raw
1 --source include/galera_cluster.inc
2
3 # Save original auto_increment_offset values.
4 --let $node_1=node_1
5 --let $node_2=node_2
6 --source include/auto_increment_offset_save.inc
7
8 #
9 # Test case 1: "ONE TIME" events should be dropped on slave nodes after expiring on master (event creator node)
10 #
11
12 --connection node_1
13
14 CREATE TABLE event_table(a int) engine=innodb;
15
16 CREATE EVENT event_2 ON SCHEDULE EVERY 1 SECOND
17 ENDS NOW() + INTERVAL 6 SECOND
18 ON COMPLETION NOT PRESERVE
19 DO
20 INSERT INTO event_table VALUES (1);
21
22 --echo # node_1 event should be there
23 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2';
24
25 --connection node_2
26 set global wsrep_sync_wait=15;
27 --echo # node_2 event should be there
28 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2';
29
30 --connection node_1
31 SET GLOBAL event_scheduler=ON;
32 SHOW VARIABLES LIKE 'event_scheduler';
33
34 # Let event_2 reach the end of its execution interval
35 let $wait_condition=select count(*) = 0 from information_schema.events where event_name='event_2';
36 --source include/wait_condition.inc
37
38 --echo # node_1 event should be removed
39 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2';
40
41 --connection node_2
42 --echo # node_2 event should be removed
43 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2';
44
45 --connection node_1
46 SET GLOBAL event_scheduler=OFF;
47 DROP TABLE event_table;
48
49 #
50 # Test case 2: After doing ALTER EVENT, slave nodes should have same definer as master
51 #
52
53 --connection node_1
54
55 CREATE DATABASE IF NOT EXISTS events_test;
56 use events_test;
57 CREATE USER ev_test@localhost;
58 GRANT ALL ON events_test.* to ev_test@localhost;
59 connect (ev_con1,localhost,ev_test,,events_test);
60
61 CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123;
62 --replace_column 8 # 9 #
63 SHOW EVENTS;
64 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME;
65 ALTER EVENT one_event ON SCHEDULE EVERY 10 SECOND;
66 --echo "The definer should be ev_test@localhost"
67 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
68
69 --connection node_2
70 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
71 --source include/wait_condition.inc
72
73 use events_test;
74 --echo "The definer should be ev_test@localhost"
75 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
76
77 --connection node_1
78 --disconnect ev_con1
79 use test;
80 DROP EVENT events_test.one_event;
81 DROP USER ev_test@localhost;
82 DROP DATABASE events_test;
83
84 #
85 # Test case 3: After SST from master node (the one where event is ENABLED) , slave event status should be 'SLAVESIDE_DISABLED'
86 #
87
88 --connection node_1
89 use test;
90 CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123;
91
92 --echo # node_1 Event should be enabled
93 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
94
95 --connection node_2
96 use test;
97 --echo # node_2 Event should be SERVERSIDE_DISABLED
98 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
99
100 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)) engine=InnoDB;
101 INSERT INTO t1 VALUES (1, 'a'), (2, 'a'), (3, 'a');
102
103 SELECT * FROM t1;
104
105 # Initiate normal shutdown on the node 2 and wait until shutdown has been completed:
106
107 --echo Shutting down server ...
108 --source include/shutdown_mysqld.inc
109
110 --connection node_1
111
112 --let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'
113 --source include/wait_condition.inc
114
115 UPDATE t1 SET f2 = 'b' WHERE f1 > 1;
116 UPDATE t1 SET f2 = 'c' WHERE f1 > 2;
117
118 SELECT * FROM t1;
119
120 --connection node_2
121
122 # Remove the "grastate.dat" file (to initiate new SST) and restart node 2
123
124 --remove_file $MYSQLTEST_VARDIR/mysqld.2/data/grastate.dat
125
126 --echo # Force SST from node_1 to node_2
127 --let $start_mysqld_params=
128 --echo Starting server ...
129 --source include/start_mysqld.inc
130
131 --let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'
132 --source include/wait_condition.inc
133
134 # Sanity check (node 2 is running now and can perform SQL operators):
135
136 SELECT * FROM t1;
137 --echo # node_2 Event should be SERVERSIDE_DISABLED
138 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
139
140 call mtr.add_suppression("WSREP: Failed to scan the last segment to the end\\. Last events may be missing\\. Last recovered event: ");
141
142 --connection node_1
143 SELECT * FROM t1;
144 --echo # node_1 Event should be ENABLED
145 SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event';
146
147 DROP TABLE t1;
148 DROP EVENT one_event;
149
150 --source include/auto_increment_offset_restore.inc