Debian Codegraph @

mysql-test/suite/ndb_rpl/t/ndb_rpl_conflict_basic_insert.test

raw
1 #
2 # Test engine native conflict resolution for ndb
3 #
4 #
5 --source include/have_multi_ndb.inc
6 --source include/have_binlog_format_mixed_or_row.inc
7 --source suite/ndb_rpl/ndb_master-slave.inc
8
9 --disable_query_log
10 --connection master
11 CREATE TABLE mysql.ndb_replication
12 (db VARBINARY(63),
13 table_name VARBINARY(63),
14 server_id INT UNSIGNED,
15 binlog_type INT UNSIGNED,
16 conflict_fn VARBINARY(128),
17 PRIMARY KEY USING HASH (db,table_name,server_id))
18 ENGINE=NDB PARTITION BY KEY(db,table_name);
19 --enable_query_log
20
21 insert into mysql.ndb_replication values ("test", "t1_max_ins", 0, 7, "NDB$MAX_INS(X)");
22 insert into mysql.ndb_replication values ("test", "t1_max_del_win_ins", 0, 7, "NDB$MAX_DEL_WIN_INS(X)");
23
24 create table `t1_max_ins$EX`
25 (NDB$server_id int unsigned,
26 NDB$master_server_id int unsigned,
27 NDB$master_epoch bigint unsigned,
28 NDB$count int unsigned,
29 NDB$OP_TYPE ENUM( 'WRITE_ROW','UPDATE_ROW', 'DELETE_ROW',
30 'REFRESH_ROW','READ_ROW') NOT NULL,
31 NDB$CFT_CAUSE ENUM( 'ROW_DOES_NOT_EXIST',
32 'ROW_ALREADY_EXISTS',
33 'DATA_IN_CONFLICT',
34 'TRANS_IN_CONFLICT') NOT NULL,
35 a int not null,
36 primary key(NDB$server_id, NDB$master_server_id, NDB$master_epoch, NDB$count)) engine ndb;
37
38 create table `t1_max_del_win_ins$EX`
39 (NDB$server_id int unsigned,
40 NDB$master_server_id int unsigned,
41 NDB$master_epoch bigint unsigned,
42 NDB$count int unsigned,
43 NDB$OP_TYPE ENUM( 'WRITE_ROW','UPDATE_ROW', 'DELETE_ROW',
44 'REFRESH_ROW','READ_ROW') NOT NULL,
45 NDB$CFT_CAUSE ENUM( 'ROW_DOES_NOT_EXIST',
46 'ROW_ALREADY_EXISTS',
47 'DATA_IN_CONFLICT',
48 'TRANS_IN_CONFLICT') NOT NULL,
49 a int not null,
50 primary key(NDB$server_id, NDB$master_server_id, NDB$master_epoch, NDB$count)) engine ndb;
51
52 create table t1_max_ins (a int primary key, b varchar(32), X int unsigned) engine = ndb;
53 create table t1_max_del_win_ins (a int primary key, b varchar(32), X int unsigned) engine = ndb;
54
55 --sync_slave_with_master
56
57 ###########################################
58 --echo "Test 0 : No conflicts, row applied"
59
60 --connection master
61 --echo Insert a row in both tables to check replication without conflicts
62 insert into t1_max_ins values (1, 'Initial X=1', 1);
63 insert into t1_max_del_win_ins values (1, 'Initial X=1', 1);
64 --sync_slave_with_master
65
66 --connection slave
67 --echo "Expect t1_max_ins* to contain initial master row"
68 select * from t1_max_ins order by a;
69 select * from t1_max_del_win_ins order by a;
70
71 ################################################
72 --echo "Test 1 : No conflict detected, Row applied"
73
74 --echo Insert some rows in slave to cause conflicts
75 insert into t1_max_ins values (2, 'Slave X=2', 2);
76 insert into t1_max_del_win_ins values (2, 'Slave X=2', 2);
77
78 --connection master
79 --echo Insert a row in both tables with a greater timestamp
80 --echo Detect a conflict and accept replication
81 insert into t1_max_ins values (2, 'Master X=20', 20);
82 insert into t1_max_del_win_ins values (2, 'Master X=20', 20);
83 --sync_slave_with_master
84
85 --connection slave
86 --echo "Expect t1_max_ins and t1_max_del_win_ins to contains 2 rows."
87 --echo "1. A row replicated without any conflicts(a=1)."
88 --echo "2. A row replicated successfully because of higher value in the timestamp column(a=2)."
89 select * from t1_max_ins order by a;
90 select * from t1_max_del_win_ins order by a;
91
92 --echo "Expect t1_max_ins* to have no entries"
93 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
94 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
95
96 ####################################################
97 --echo "Test 2 : Conflict detected, Row rejected"
98
99 --connection slave
100 insert into t1_max_ins values (3, 'Slave X=30', 30);
101 insert into t1_max_del_win_ins values (3, 'Slave X=30', 30);
102
103 --connection master
104 --echo Insert a row in both tables with a smaller or equal timestamp
105 --echo Detect a conflict and reject replication
106 insert into t1_max_ins values (3, 'Master X=3', 3);
107 insert into t1_max_del_win_ins values (3, 'Master X=3', 3);
108 --sync_slave_with_master
109
110 --connection slave
111 --echo "Expect t1_max_ins and t1_max_del_win_ins to contains 3 rows."
112 --echo "1. A row replicated without any conflicts(a=1)."
113 --echo "2. A row replicated successfully because of higher value in the timestamp column(a=2)."
114 --echo "3. A row detected conflict and didn't replicate on the slave(a=3)."
115 select * from t1_max_ins order by a;
116 select * from t1_max_del_win_ins order by a;
117
118 --echo "Expect t1_max_ins* to have 1 row."
119 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
120 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
121
122 # Now test with Blobs
123 # Cleanup
124 --connection master
125 drop table t1_max_ins, t1_max_del_win_ins;
126 delete from t1_max_ins$EX;
127 delete from t1_max_del_win_ins$EX;
128
129 --sync_slave_with_master
130 --connection slave
131 # Delete on slave, as $EX table rows are present only on slave
132 delete from t1_max_ins$EX;
133 delete from t1_max_del_win_ins$EX;
134
135 --connection master
136
137 create table t1_max_ins (a int primary key, b longtext, X int unsigned) engine = ndb;
138 create table t1_max_del_win_ins (a int primary key, b longtext, X int unsigned) engine = ndb;
139
140 --sync_slave_with_master
141
142 ###############################################################
143 --echo "Testing with blobs"
144 --echo "Test 3 : Conflict detection and resolution for INSERTs"
145
146 --connection master
147 insert into t1_max_ins values (1, repeat('Initial X=1 ',1000), 1);
148 insert into t1_max_del_win_ins values (1, repeat('Initial X=1 ',1000), 1);
149 --sync_slave_with_master
150
151 --connection slave
152 insert into t1_max_ins values (2, repeat('Slave X=2 ',2000), 2);
153 insert into t1_max_del_win_ins values (2, repeat('Slave X=2 ',2000), 2);
154 insert into t1_max_ins values (3, repeat('Slave X=30 ',3000), 30);
155 insert into t1_max_del_win_ins values (3, repeat('Slave X=30', 3000), 30);
156
157 --connection master
158 --echo Insert a row in both tables with a greater timestamp
159 insert into t1_max_ins values (2, repeat('Master X=20 ',2000), 20);
160 insert into t1_max_del_win_ins values (2, repeat('Master X=20 ',2000), 20);
161 --echo Insert a row in both tables with a smaller timestamp
162 insert into t1_max_ins values (3, repeat('Master X=3 ',3000), 3);
163 insert into t1_max_del_win_ins values (3, repeat('Master X=3 ',3000), 3);
164 --sync_slave_with_master
165
166 --connection slave
167 --echo "Expect t1_max_ins* to contain 3 rows"
168 select a, left(b, 25), length(b), X from t1_max_ins order by a;
169 select a, left(b, 25), length(b), X from t1_max_del_win_ins order by a;
170
171 --echo Expect t1_max_ins* to have 1 entry
172 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
173 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
174
175 # Cleanup
176 --connection master
177 drop table t1_max_ins, t1_max_del_win_ins;
178 delete from t1_max_ins$EX;
179 delete from t1_max_del_win_ins$EX;
180
181 --sync_slave_with_master
182 --connection slave
183 # Delete on slave, as $EX table rows are present only on slave
184 delete from t1_max_ins$EX;
185 delete from t1_max_del_win_ins$EX;
186
187 --connection master
188
189 create table t1_max_ins (a int primary key, b longtext, X int unsigned) engine = ndb;
190 create table t1_max_del_win_ins (a int primary key, b longtext, X int unsigned) engine = ndb;
191
192 ###############################################################
193 --echo "Test 4 : Conflict detection and resolution for UPDATEs"
194
195 insert into t1_max_ins values (1, repeat('Initial X=1',1000), 1);
196 insert into t1_max_del_win_ins values (1, repeat('Initial X=1',1000), 1);
197 --sync_slave_with_master
198
199 --connection slave
200 update t1_max_ins set X = 20, b=repeat('Slave X=20',2000);
201 update t1_max_del_win_ins set X = 20, b=repeat('Slave X=20',2000);
202
203 --connection master
204 update t1_max_ins set X = 2, b=repeat('Master X=2',2000);
205 update t1_max_del_win_ins set X = 2, b=repeat('Master X=2',2000);
206 --sync_slave_with_master
207
208 --connection slave
209 --echo "Expect t1_max* to contain a single row with X=20"
210 select a, left(b, 20), length(b), X from t1_max_ins order by a;
211 select a, left(b, 20), length(b), X from t1_max_del_win_ins order by a;
212
213 --echo Expect t1_max* to have 1 entry for row with X=2 since this update is rejected at slave because of a smaller timestamp
214 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
215 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
216
217 --connection master
218 update t1_max_ins set X = 30, b=repeat('Master X=30',3000);
219 update t1_max_del_win_ins set X = 30, b=repeat('Master X=30',3000);
220 --sync_slave_with_master
221
222 --connection slave
223 --echo "Expect t1_max* to contain a single row with X=30 since there is no conflict(higher timestamp)"
224 select a, left(b, 20), length(b), X from t1_max_ins order by a;
225 select a, left(b, 20), length(b), X from t1_max_del_win_ins order by a;
226
227 --echo Expect t1_max* to have 1 old entry for row with X=2
228 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
229 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
230
231 delete from t1_max_ins$EX;
232 delete from t1_max_del_win_ins$EX;
233
234 ###############################################################
235 --echo "Test 5 : Conflict detection and resolution for DELETEs"
236
237 --connection slave
238 update t1_max_ins set X = 4, b=repeat('Slave X=4',2000);
239 update t1_max_del_win_ins set X = 4, b=repeat('Slave X=4',2000);
240
241 --connection master
242 delete from t1_max_ins;
243 delete from t1_max_del_win_ins;
244 --sync_slave_with_master
245
246 --connection slave
247 --echo "Expect t1_max_ins to contain slave row since before images don't match according to MAX_INS algorithm"
248 --echo "Expect t1_max_del_win_ins to be empty since delete will be applied anyways with MAX_DEL_WIN_INS algorithm"
249 select a, left(b, 20), length(b), X from t1_max_ins order by a;
250 select a, left(b, 20), length(b), X from t1_max_del_win_ins order by a;
251
252 --echo Expect t1_max_ins to contain 1 entry, and t1_max_del_win_ins to be empty
253 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
254 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
255
256 delete from t1_max_ins$EX;
257 delete from t1_max_del_win_ins$EX;
258
259 delete from t1_max_ins;
260 delete from t1_max_del_win_ins;
261
262 --connection master
263
264 delete from t1_max_ins;
265 delete from t1_max_del_win_ins;
266
267 ##########################################################################################
268 --echo "Test 6 : Test that operations affecting Blobs are rejected correctly on the slave"
269
270 drop table t1_max_ins;
271 create table t1_max_ins (a int primary key, b int, c longtext, d longtext, X int unsigned) engine = ndb;
272
273 insert into t1_max_ins values (1, 1, repeat("B", 10000), repeat("E", 10001), 1);
274 insert into t1_max_ins values (2, 2, repeat("A", 10002), repeat("T", 10003), 1);
275
276 --sync_slave_with_master
277 --connection slave
278
279 # Bump up tuple timestamp
280 update t1_max_ins set X=20;
281
282 --echo Initial values on Slave
283 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max_ins order by a;
284
285 --connection master
286 --echo Originate update which will be rejected due to NDB_MAX_INS algorithm.
287 update t1_max_ins set c=repeat("Z", 10006), d=repeat("I", 10005), X=2 where a=1;
288
289 --sync_slave_with_master
290 --connection slave
291 --echo Check slave has rejected due to smaller timestamp.
292 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max_ins order by a;
293
294 --connection master
295 --echo Originate delete which will be rejected due to NDB_OLD algorithm.
296 # Delete would NOT be rejected if the table was configured with NDB$MAX_DEL_WIN_INS.
297 # Because, it doesn't fall back to NDB_OLD like NDB_MAX_INS algorithm in case of DELETEs.
298 delete from t1_max_ins where a=1;
299
300 --sync_slave_with_master
301 --connection slave
302 --echo Check slave has rejected due to before image mismatch.
303 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max_ins order by a;
304
305 --connection master
306 --echo Originate insert which will be rejected due to NDB_MAX_INS algorithm.
307 insert into t1_max_ins values (1, 1, repeat("R", 10004), repeat("A", 10007), 1);
308
309 --sync_slave_with_master
310 --connection slave
311 --echo Check slave has rejected due to smaller timestamp.
312 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max_ins order by a;
313
314 --echo Expect t1_max_ins to have 3 entries
315 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
316 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
317
318 delete from t1_max_ins$EX;
319 delete from t1_max_del_win_ins$EX;
320
321
322 --connection master
323
324 #############################################################################
325 --echo Test 7 : Check that non-Blob related operations in a batch with a Blob
326 --echo operation are still subject to conflict detection.
327 --echo
328 insert into mysql.ndb_replication values ("test", "t2_max", 0, 7, "NDB$MAX(X)");
329
330 create table `t2_max$EX`
331 (server_id int unsigned,
332 master_server_id int unsigned,
333 master_epoch bigint unsigned,
334 count int unsigned,
335 a int not null,
336 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
337
338 create table t2_max (a int primary key, b int, X bigint unsigned) engine=ndb;
339
340 insert into t2_max values (1,1,10), (2,2,10), (3,3,10), (4,4,10), (5,5,10);
341
342 --sync_slave_with_master
343
344 --connection slave
345 insert into t1_max_ins values (3, 3, repeat("R", 3000), repeat("A", 3000), 3);
346
347 --connection master
348 --echo Now issue a transaction with a successful Blob op, and unsuccessful
349 --echo non-Blob op. Check that the Blob op succeeds, and the unsuccessful
350 --echo non-Blob op is handled as expected.
351
352 begin;
353 update t2_max set b=b+1, X=1 where a=3; # conflicts
354 insert into t1_max_ins values (3, 3, repeat("MAX_INS", 3000), repeat("Success", 3000), 30); # ok
355 commit;
356
357 --sync_slave_with_master
358
359 --connection slave
360 --echo Contents on Slave
361 --echo Expect Blob data applied to t1_max_ins, no update applied to t2_max
362 select a,b,left(c,7), length(c), left(d,7), length(d), X from t1_max_ins where a=3;
363 select * from t2_max order by a;
364
365 --echo Expect 1 conflict detected, but not applied in t2_max$EX
366 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
367 select server_id, master_server_id, count, a from t2_max$EX order by count;
368
369 # Cleanup
370 --connection master
371 drop table t2_max, t2_max$EX;
372 drop table t1_max_ins, `t1_max_ins$EX`, t1_max_del_win_ins, `t1_max_del_win_ins$EX`;
373 drop table mysql.ndb_replication;
374
375 --source include/rpl_end.inc