Debian Codegraph @

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

raw
1 ################################################################################
2 # The aim of the testcase is to test GR with 'REPEATABLE-READ' isolation level
3 #
4 # Test:
5 # 0. Start 2 servers with GR
6 # 1. Set isolation level to 'REPEATABLE-READ' on both server1, server2
7 # 2. Create a table (t1), Insert some data
8 # 3. Check the negative scenarios with conflicting transaction,
9 # 3.1 set a debug sync and execute a transaction (T1) on server_1 so that it
10 # is blocked before broadcast.
11 # 3.2 check that changes made in connection server_1 has not propogated and
12 # wait until server1 reaches the debug sync point.
13 # 3.3 execute a conflicting transaction (T2) on server2 that will reach
14 # certification first.
15 # 3.4 Signal the waiting thread on server1 to resume.
16 # 3.5 Check the error on server1, it will rollback T1 as it is conflicting
17 # with T2.
18 # Another conflicting transaction, but transaction will rollback
19 # 3.6 ROLL BACK a transaction on server1
20 # 3.7 Execute a transaction on server2
21 # 3.8 Transaction originated on server2 will be successful as ROLLED BACK
22 # transactions are not broadcasted.
23 # 4. Check the positive scenarios with non-conflicting transaction,
24 # 4.1 set a debug sync and execute a transaction (T1) on server_1 so that it
25 # is blocked before broadcast.
26 # 4.2 wait until server1 reaches the debug sync point.
27 # 4.3 execute a non-conflicting transaction (T2) on server2 that will reach
28 # certification first.
29 # 4.4 Signal the waiting thread on server1 to resume.
30 # 4.5 Check that both T1 and T2 are executed successfully.
31 # 5. Validate that server1, server2 has same set of data and assert that number
32 # of negatively certified quries are expected ones.
33 # 6. clean-up
34 ################################################################################
35
36 --source include/big_test.inc
37 --source include/have_debug_sync.inc
38 --source include/have_group_replication_plugin.inc
39 --source include/group_replication.inc
40
41 --echo
42 --echo ## 1. Set global isolation level to 'REPEATABLE-READ' on server1, server2
43 --let $rpl_connection_name= server1
44 --source include/rpl_connection.inc
45 # Store default isolation level
46 SET @transaction_isolation_global_orig = @@global.transaction_isolation;
47 SET GLOBAL transaction_isolation= 'REPEATABLE-READ';
48 SET @transaction_isolation_session_orig = @@global.transaction_isolation;
49 SET SESSION transaction_isolation= 'REPEATABLE-READ';
50
51 --let $rpl_connection_name= server2
52 --source include/rpl_connection.inc
53 SET @transaction_isolation_global_orig = @@global.transaction_isolation;
54 SET GLOBAL transaction_isolation= 'REPEATABLE-READ';
55 SET @transaction_isolation_session_orig = @@global.transaction_isolation;
56 SET SESSION transaction_isolation= 'REPEATABLE-READ';
57
58 --echo
59 --echo ## 2. Create table and insert some data
60 --let $rpl_connection_name= server1
61 --source include/rpl_connection.inc
62 CREATE TABLE t1 (a INT PRIMARY KEY);
63 INSERT INTO t1 VALUES (10),(20);
64 --source include/rpl_sync.inc
65
66 --echo
67 --echo ## 3.1 Set a debug sync before broadcast message to group on server1
68 --echo ## Commit a transaction that will be blocked before broadcast.
69 --let $rpl_connection_name= server_1
70 --source include/rpl_connection.inc
71 SET @debug_save= @@GLOBAL.DEBUG;
72 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
73 BEGIN;
74 UPDATE t1 set a= 1 where a= 10;
75 --send COMMIT
76
77 --echo
78 --echo ## 3.2 Wait until server_1 connection reaches the
79 --echo ## 'group_replication_before_message_broadcast' debug sync point.
80 --let $rpl_connection_name= server1
81 --source include/rpl_connection.inc
82
83 # Assert that data on t1 remain unchanged
84 --let $assert_text= 'Table t1 should contain a row with a=10'
85 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 where t1.a= 10, count, 1]= 1
86 --source include/assert.inc
87
88 --let $assert_text= 'Table t1 should contain a row with a=20'
89 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 where t1.a= 20, count, 1]= 1
90 --source include/assert.inc
91
92 --let $wait_condition=SELECT COUNT(*)=1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'debug sync point: now'
93 --source include/wait_condition.inc
94
95 --echo
96 --echo ## 3.3. Execute a transaction on server2, that will reach first
97 --echo ## certification, since server_1 is blocked before broadcast.
98 --let $rpl_connection_name= server2
99 --source include/rpl_connection.inc
100 BEGIN;
101 UPDATE t1 SET a=3 WHERE a=10;
102 DELETE FROM t1 where a=20;
103 COMMIT;
104
105 --echo
106 --echo ## 3.4 Signal the waiting thread on server1 to resume.
107 --let $rpl_connection_name= server1
108 --source include/rpl_connection.inc
109 SET DEBUG_SYNC='now SIGNAL waiting';
110 SET @@GLOBAL.DEBUG= @debug_save;
111
112 --echo
113 --echo ## 3.5 It will end up in an error stating that it was aborted, since
114 --echo ## transactions are conflicting and server2 was ordered first.
115 --let $rpl_connection_name= server2
116 --source include/rpl_connection.inc
117 --let $sync_slave_connection= server1
118 --source include/sync_slave_sql_with_master.inc
119
120 --let $rpl_connection_name= server_1
121 --source include/rpl_connection.inc
122 --error ER_TRANSACTION_ROLLBACK_DURING_COMMIT
123 --reap
124
125 --echo
126 --echo ## 3.6 ROLL BACK a transaction on server1
127 --let $rpl_connection_name= server_1
128 --source include/rpl_connection.inc
129 BEGIN;
130 UPDATE t1 set a= 30 where a= 3;
131 --send ROLLBACK
132
133 --echo
134 --echo ## 3.7 Execute a transaction on server2
135 --let $rpl_connection_name= server2
136 --source include/rpl_connection.inc
137 BEGIN;
138 UPDATE t1 SET a=10 WHERE a=3;
139 COMMIT;
140 --let $sync_slave_connection= server1
141 --source include/sync_slave_sql_with_master.inc
142
143 --echo
144 --echo ## 3.8 Transaction originated on server2 will be successful as ROLLED BACK
145 --echo ## transactions are not broadcasted.
146 --let $rpl_connection_name= server_1
147 --source include/rpl_connection.inc
148 --reap
149
150 --echo ## Validating data on server1 and server2
151 --let $rpl_connection_name= server1
152 --source include/rpl_connection.inc
153 --let $assert_text= 'Table t1 has a row with a=10'
154 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.a = 10, count, 1]" = "1"
155 --source include/assert.inc
156
157 --let $assert_text= 'Table t1 will contain 1 row after the above execution'
158 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1, count, 1]" = "1"
159 --source include/assert.inc
160
161 # Check that t1 on Server1, Server2 has same data
162 --let $diff_tables= server1:test.t1, server2:test.t1
163 --source include/diff_tables.inc
164
165 --echo
166 --echo ## Checking the positive case in which there is no conflict.
167 --echo
168 --echo ## 4.1 Commit a transaction that will be block before broadcast.
169 --echo ## Set a debug sync before broadcast message to group on server1
170 --let $rpl_connection_name= server_1
171 --source include/rpl_connection.inc
172 SELECT * FROM t1;
173 SET @@GLOBAL.DEBUG='d,group_replication_before_message_broadcast';
174 BEGIN;
175 INSERT INTO t1 VALUES (2);
176 --send COMMIT
177
178 --echo
179 --echo ## 4.2 Wait until server_1 connection reaches the
180 --echo ## group_replication_before_message_broadcast debug sync point.
181 --let $rpl_connection_name= server1
182 --source include/rpl_connection.inc
183 --let $wait_condition=SELECT COUNT(*)=1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'debug sync point: now'
184 --source include/wait_condition.inc
185
186 --echo
187 --echo ## 4.3 Execute a non-conflicting transaction on server2, that will reach
188 --echo ## first certification, since server_1 is blocked before broadcast.
189 --let $rpl_connection_name= server2
190 --source include/rpl_connection.inc
191 UPDATE t1 SET a=1 where a=10;
192 --let $sync_slave_connection= server1
193 --source include/sync_slave_sql_with_master.inc
194
195 --echo
196 --echo ## 4.4 Signal the waiting thread on server_1 to resume.
197 --let $rpl_connection_name= server1
198 --source include/rpl_connection.inc
199 SET DEBUG_SYNC='now SIGNAL waiting';
200 SET DEBUG_SYNC= 'RESET';
201 SET @@GLOBAL.DEBUG= @debug_save;
202
203 --echo
204 --echo ## 4.5 Transaction will be executed
205 --let $rpl_connection_name= server_1
206 --source include/rpl_connection.inc
207 --reap
208
209 --source include/rpl_sync.inc
210 --echo
211 --echo ## 5. Assert that number of certified transactions are the
212 --echo ## expected ones.
213 --let $rpl_connection_name= server1
214 --source include/rpl_connection.inc
215
216 --let $assert_text= 'Table t1 should contain a row with a=1'
217 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.a = 1, count, 1]" = "1"
218 --source include/assert.inc
219
220 --let $assert_text= 'Table t1 should contain a row with a=2'
221 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.a = 2, count, 1]" = "1"
222 --source include/assert.inc
223
224 --let $assert_text= 'Table t1 will contain 2 rows after the above execution'
225 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1, count, 1]" = "2"
226 --source include/assert.inc
227
228 --let $assert_text= 'The value of Count_Transactions_Checked should be 7 after starting group replication'
229 --let $assert_cond= "[SELECT Count_Transactions_Checked from performance_schema.replication_group_member_stats where member_id in (SELECT @@server_uuid), Count_Transactions_Checked, 1]" = "7"
230 --source include/assert.inc
231
232 --let $assert_text= The value of Count_Conflicts_Detected should be 1 after starting group replication
233 --let $assert_cond= "[SELECT Count_Conflicts_Detected from performance_schema.replication_group_member_stats where member_id in (SELECT @@server_uuid), Count_Conflicts_Detected, 1]" = "1"
234 --source include/assert.inc
235
236 # Check that t1 on Server1, Server2 is same
237 --let $diff_tables= server1:test.t1, server2:test.t1
238 --source include/diff_tables.inc
239
240 --echo
241 --echo ## 6. Clean up.
242
243 # Restore original isolation levels
244 SET GLOBAL transaction_isolation= @transaction_isolation_global_orig;
245 SET SESSION transaction_isolation= @transaction_isolation_session_orig;
246
247 --let $rpl_connection_name= server2
248 --source include/rpl_connection.inc
249
250 SET GLOBAL transaction_isolation= @transaction_isolation_global_orig;
251 SET SESSION transaction_isolation= @transaction_isolation_session_orig;
252 # Drop table
253 DROP TABLE t1;
254
255 --source include/group_replication_end.inc