Debian Codegraph @

mysql-test/suite/group_replication/t/gr_concurrent_events_insert_with_select.test

raw
1 # This test emulates a scenario of concurrent writes with selects using events.
2 # Each group member has an event that writes to a single table concurrently.
3 #
4
5 --source include/big_test.inc
6 --source include/have_group_replication_plugin.inc
7 --let $rpl_server_count= 3
8 --source include/group_replication.inc
9
10 --echo #
11 --echo # Create the test table on server 1
12 --echo #
13
14 --let $rpl_connection_name= server1
15 --source include/rpl_connection.inc
16
17 CREATE TABLE IF NOT EXISTS t1 (a INT PRIMARY KEY);
18
19 --source include/rpl_sync.inc
20
21 --echo #
22 --echo # Create the events in each member.
23 --echo #
24
25 SET @old_event_scheduler= @@global.event_scheduler;
26 SET SESSION sql_log_bin= 0;
27 delimiter |;
28
29 SET GLOBAL event_scheduler=on|
30 CREATE EVENT IF NOT EXISTS test.event1
31 ON SCHEDULE
32 EVERY 1 SECOND
33 STARTS CURRENT_TIMESTAMP + INTERVAL 1 SECOND
34 ENDS CURRENT_TIMESTAMP + INTERVAL 30 SECOND
35 ON COMPLETION PRESERVE
36 DISABLE
37 COMMENT 'Test with events'
38 DO
39 BEGIN
40 DECLARE v INTEGER;
41 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
42
43 SET v=0;
44 WHILE v < 1000
45 DO
46 SELECT max(a) + 1 INTO v FROM t1;
47 INSERT INTO t1 values (v);
48 SET v = v+1;
49 END WHILE;
50 END|
51
52 delimiter ;|
53 SET SESSION sql_log_bin= 1;
54
55 --let $rpl_connection_name= server2
56 --source include/rpl_connection.inc
57
58 SET @old_event_scheduler= @@global.event_scheduler;
59 SET SESSION sql_log_bin= 0;
60 delimiter |;
61
62 SET GLOBAL event_scheduler=on|
63 CREATE EVENT IF NOT EXISTS test.event2
64 ON SCHEDULE
65 EVERY 1 SECOND
66 STARTS CURRENT_TIMESTAMP + INTERVAL 1 SECOND
67 ENDS CURRENT_TIMESTAMP + INTERVAL 30 SECOND
68 ON COMPLETION PRESERVE
69 DISABLE
70 COMMENT 'Test with events'
71 DO
72 BEGIN
73 DECLARE v INTEGER;
74 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
75
76 SET v=0;
77 WHILE v < 1000
78 DO
79 SELECT max(a) + 1 INTO v FROM t1;
80 INSERT INTO t1 values (v);
81 SET v = v+1;
82 END WHILE;
83 END|
84
85 delimiter ;|
86 SET SESSION sql_log_bin= 1;
87
88 --let $rpl_connection_name= server3
89 --source include/rpl_connection.inc
90
91 SET @old_event_scheduler= @@global.event_scheduler;
92 SET SESSION sql_log_bin= 0;
93 delimiter |;
94
95 SET GLOBAL event_scheduler=on|
96 CREATE EVENT IF NOT EXISTS test.event3
97 ON SCHEDULE
98 EVERY 1 SECOND
99 STARTS CURRENT_TIMESTAMP + INTERVAL 1 SECOND
100 ENDS CURRENT_TIMESTAMP + INTERVAL 30 SECOND
101 ON COMPLETION PRESERVE
102 DISABLE
103 COMMENT 'Test with events'
104 DO
105 BEGIN
106 DECLARE v INTEGER;
107 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
108
109 SET v=0;
110 WHILE v < 1000
111 DO
112 SELECT max(a) + 1 INTO v FROM t1;
113 INSERT INTO t1 values (v);
114 SET v = v+1;
115 END WHILE;
116 END|
117
118 delimiter ;|
119 SET SESSION sql_log_bin= 1;
120
121 --echo #
122 --echo # Start the events in each member.
123 --echo #
124
125 --let $rpl_connection_name= server1
126 --source include/rpl_connection.inc
127
128 SET GLOBAL event_scheduler=on;
129 SET SESSION sql_log_bin= 0;
130 ALTER EVENT event1 ENABLE;
131 SET SESSION sql_log_bin= 1;
132
133 --let $rpl_connection_name= server2
134 --source include/rpl_connection.inc
135
136 SET GLOBAL event_scheduler=on;
137 SET SESSION sql_log_bin= 0;
138 ALTER EVENT event2 ENABLE;
139 SET SESSION sql_log_bin= 1;
140
141 --let $rpl_connection_name= server3
142 --source include/rpl_connection.inc
143
144 SET GLOBAL event_scheduler=on;
145 SET SESSION sql_log_bin= 0;
146 ALTER EVENT event3 ENABLE;
147 SET SESSION sql_log_bin= 1;
148
149 --echo #
150 --echo # Wait for the events to be disabled
151 --echo #
152
153 --let $rpl_connection_name= server1
154 --source include/rpl_connection.inc
155
156 --let $wait_timeout= 60
157 --let $wait_condition= SELECT status="DISABLED" FROM information_schema.events WHERE event_schema='test' AND event_name="event1"
158 --source include/wait_condition_or_abort.inc
159
160 --let $rpl_connection_name= server2
161 --source include/rpl_connection.inc
162
163 --let $wait_condition= SELECT status="DISABLED" FROM information_schema.events WHERE event_schema='test' AND event_name="event2"
164 --source include/wait_condition_or_abort.inc
165
166 --let $rpl_connection_name= server3
167 --source include/rpl_connection.inc
168
169 --let $wait_condition= SELECT status="DISABLED" FROM information_schema.events WHERE event_schema='test' AND event_name="event3"
170 --source include/wait_condition_or_abort.inc
171
172 --echo #
173 --echo # Check the members are still operational
174 --echo #
175
176 --let $rpl_connection_name= server1
177 --source include/rpl_connection.inc
178
179 --let $assert_text= 'The group replication applier channel is ON'
180 --let $assert_cond= [SELECT COUNT(*) AS count FROM performance_schema.replication_connection_status where CHANNEL_NAME="group_replication_applier" and SERVICE_STATE="ON", count, 1] = 1
181 --source include/assert.inc
182
183 --let $rpl_connection_name= server2
184 --source include/rpl_connection.inc
185
186 --let $assert_text= 'The group replication applier channel is ON'
187 --let $assert_cond= [SELECT COUNT(*) AS count FROM performance_schema.replication_connection_status where CHANNEL_NAME="group_replication_applier" and SERVICE_STATE="ON", count, 1] = 1
188 --source include/assert.inc
189
190 --let $rpl_connection_name= server3
191 --source include/rpl_connection.inc
192
193 --let $assert_text= 'The group replication applier channel is ON'
194 --let $assert_cond= [SELECT COUNT(*) AS count FROM performance_schema.replication_connection_status where CHANNEL_NAME="group_replication_applier" and SERVICE_STATE="ON", count, 1] = 1
195 --source include/assert.inc
196
197 --echo #
198 --echo # Remove the events and the data
199 --echo #
200
201 --let $rpl_connection_name= server1
202 --source include/rpl_connection.inc
203
204 SET SESSION sql_log_bin= 0;
205 DROP EVENT IF EXISTS test.event1;
206 SET SESSION sql_log_bin= 1;
207
208 SET GLOBAL event_scheduler= @old_event_scheduler;
209
210 --let $rpl_connection_name= server2
211 --source include/rpl_connection.inc
212
213 SET SESSION sql_log_bin= 0;
214 DROP EVENT IF EXISTS test.event2;
215 SET SESSION sql_log_bin= 1;
216
217 SET GLOBAL event_scheduler= @old_event_scheduler;
218
219 --let $rpl_connection_name= server3
220 --source include/rpl_connection.inc
221
222 SET SESSION sql_log_bin= 0;
223 DROP EVENT IF EXISTS test.event3;
224 SET SESSION sql_log_bin= 1;
225
226 SET GLOBAL event_scheduler= @old_event_scheduler;
227
228 --let $rpl_connection_name= server1
229 --source include/rpl_connection.inc
230
231 DROP TABLE t1;
232
233 --source include/group_replication_end.inc