Debian Codegraph @

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

raw
1 ################################################################################
2 # The aim of the testcase is to test GR with 'READ-UNCOMMITTED' isolation level
3 #
4 # Test:
5 # 0. Start 2 servers with GR
6 # 1. Set isolation level to 'READ-UNCOMMITTED' on both server1, server2
7 # 2. Create a table (t1), Insert some data
8 # 3. Check the negative scenario with conflicting transactions,
9 # 3.1 set a debug sync and execute a transaction (T1) on server1 so that it
10 # is blocked before broadcast.
11 # 3.2 check that changes made at server1 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 transactions,
24 # 4.1 set a debug sync and execute a transaction (T1) on server1 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 queries 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 'READ-UNCOMMITTED' on server1, server2
43 --let $rpl_connection_name= server1
44 --source include/rpl_connection.inc
45 # Take a back-up of isolation level
46 SET @transaction_isolation_global_orig = @@global.transaction_isolation;
47 SET GLOBAL transaction_isolation= 'READ-UNCOMMITTED';
48 SET @transaction_isolation_session_orig = @@global.transaction_isolation;
49 SET SESSION transaction_isolation= 'READ-UNCOMMITTED';
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= 'READ-UNCOMMITTED';
55 SET @transaction_isolation_session_orig = @@global.transaction_isolation;
56 SET SESSION transaction_isolation= 'READ-UNCOMMITTED';
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 # Check that update on parallel session has propogated.
84 --let $assert_text= 'Table t1 should contain a row with a=1'
85 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 where t1.a= 1, 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 blocked 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 @@GLOBAL.DEBUG= @debug_save;
201
202 --echo
203 --echo ## 4.5 Transaction will be executed
204 --let $rpl_connection_name= server_1
205 --source include/rpl_connection.inc
206 --reap
207
208 --source include/rpl_sync.inc
209 --echo
210 --echo ## 5. Assert that number of certified transactions are the
211 --echo ## expected ones and validate data on server1, server2
212 --let $rpl_connection_name= server1
213 --source include/rpl_connection.inc
214
215 --let $assert_text= 'Table t1 should contain a row with a=1'
216 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.a = 1, count, 1]" = "1"
217 --source include/assert.inc
218
219 --let $assert_text= 'Table t1 should contain a row with a=2'
220 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.a = 2, count, 1]" = "1"
221 --source include/assert.inc
222
223 --let $assert_text= 'Table t1 will contain 2 rows after the above execution'
224 --let $assert_cond= "[SELECT COUNT(*) AS count FROM t1, count, 1]" = "2"
225 --source include/assert.inc
226
227 --let $assert_text= 'The value of Count_Transactions_Checked should be 7 after starting group replication'
228 --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"
229 --source include/assert.inc
230
231 --let $assert_text= 'The value of Count_Conflicts_Detected should be 1 after starting group replication'
232 --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"
233 --source include/assert.inc
234
235 # Check that t1 on Server1, Server2 has same data
236 --let $diff_tables= server1:test.t1, server2:test.t1
237 --source include/diff_tables.inc
238
239 --echo
240 --echo ## 6. Clean up.
241
242 # Restore original isolation levels
243 SET GLOBAL transaction_isolation= @transaction_isolation_global_orig;
244 SET SESSION transaction_isolation= @transaction_isolation_session_orig;
245
246 SET DEBUG_SYNC='RESET';
247
248 --let $rpl_connection_name= server2
249 --source include/rpl_connection.inc
250
251 SET GLOBAL transaction_isolation= @transaction_isolation_global_orig;
252 SET SESSION transaction_isolation= @transaction_isolation_session_orig;
253 # Drop table
254 DROP TABLE t1;
255
256 --source include/group_replication_end.inc