Debian Codegraph @

mysql-test/suite/ndb_rpl/r/ndb_rpl_conflict_basic.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 insert into mysql.ndb_replication values ("test", "t1_old", 0, 7, "NDB$OLD(X)");
7 insert into mysql.ndb_replication values ("test", "t1_max", 0, 7, "NDB$MAX(X)");
8 insert into mysql.ndb_replication values ("test", "t1_max_delete_win", 0, 7, "NDB$MAX_DELETE_WIN(X)");
9 create table `t1_old$EX`
10 (server_id int unsigned,
11 master_server_id int unsigned,
12 master_epoch bigint unsigned,
13 count int unsigned,
14 a int not null,
15 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
16 create table `t1_max$EX`
17 (server_id int unsigned,
18 master_server_id int unsigned,
19 master_epoch bigint unsigned,
20 count int unsigned,
21 a int not null,
22 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
23 create table `t1_max_delete_win$EX`
24 (server_id int unsigned,
25 master_server_id int unsigned,
26 master_epoch bigint unsigned,
27 count int unsigned,
28 a int not null,
29 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
30 create table t1_old (a int primary key, b varchar(32), X int unsigned) engine = ndb;
31 create table t1_max (a int primary key, b varchar(32), X int unsigned) engine = ndb;
32 create table t1_max_delete_win (a int primary key, b varchar(32), X int unsigned) engine = ndb;
33 "Test 1"
34 insert into t1_old values (1, 'Initial X=1', 1);
35 insert into t1_max values (1, 'Initial X=1', 1);
36 insert into t1_max_delete_win values (1, 'Initial X=1', 1);
37 update t1_old set X = 2, b='Slave X=2';
38 update t1_max set X = 2, b='Slave X=2';
39 update t1_max_delete_win set X = 2, b='Slave X=2';
40 update t1_old set X = 3, b='Master X=3';
41 update t1_max set X = 3, b='Master X=3';
42 update t1_max_delete_win set X = 3, b='Master X=3';
43 "Expect t1_old to contain slave row, and t1_max* to contain master row"
44 select * from t1_old;
45 a b X
46 1 Slave X=2 2
47 select * from t1_max;
48 a b X
49 1 Master X=3 3
50 select * from t1_max_delete_win;
51 a b X
52 1 Master X=3 3
53 update t1_old set X = 3, b='Master X=3';
54 "Test 2"
55 update t1_old set X = 4, b='Slave X=4';
56 update t1_max set X = 4, b='Slave X=4';
57 update t1_max_delete_win set X = 4, b='Slave X=4';
58 delete from t1_old;
59 delete from t1_max;
60 delete from t1_max_delete_win;
61 "Expect t1_old and t1_max to contain slave row, and t1_max_delete_win to be empty(as master)"
62 select * from t1_old;
63 a b X
64 1 Slave X=4 4
65 select * from t1_max;
66 a b X
67 1 Slave X=4 4
68 select * from t1_max_delete_win;
69 a b X
70 delete from t1_old;
71 delete from t1_max;
72 drop table t1_old, t1_max, t1_max_delete_win;
73 delete from t1_old$EX;
74 delete from t1_max$EX;
75 delete from t1_max_delete_win$EX;
76 delete from t1_old$EX;
77 delete from t1_max$EX;
78 delete from t1_max_delete_win$EX;
79 create table t1_old (a int primary key, b longtext, X int unsigned) engine = ndb;
80 create table t1_max (a int primary key, b longtext, X int unsigned) engine = ndb;
81 create table t1_max_delete_win (a int primary key, b longtext, X int unsigned) engine = ndb;
82 "Test 3"
83 insert into t1_old values (1, repeat('Initial X=1',1000), 1);
84 insert into t1_max values (1, repeat('Initial X=1',1000), 1);
85 insert into t1_max_delete_win values (1, repeat('Initial X=1',1000), 1);
86 update t1_old set X = 2, b=repeat('Slave X=2',1001);
87 update t1_max set X = 2, b=repeat('Slave X=2',1001);
88 update t1_max_delete_win set X = 2, b=repeat('Slave X=2',1001);
89 update t1_old set X = 3, b=repeat('Master X=3',1002);
90 update t1_max set X = 3, b=repeat('Master X=3',1002);
91 update t1_max_delete_win set X = 3, b=repeat('Master X=3',1002);
92 "Expect t1_old to contain slave row, and t1_max* to contain master row"
93 select a, left(b, 20), length(b), X from t1_old;
94 a left(b, 20) length(b) X
95 1 Slave X=2Slave X=2Sl 9009 2
96 select a, left(b, 20), length(b), X from t1_max;
97 a left(b, 20) length(b) X
98 1 Master X=3Master X=3 10020 3
99 select a, left(b, 20), length(b), X from t1_max_delete_win;
100 a left(b, 20) length(b) X
101 1 Master X=3Master X=3 10020 3
102 Expect t1_old to have 1 entry, and t1_max* to have no entries
103 select server_id, master_server_id, count, a from t1_old$EX order by count;
104 server_id master_server_id count a
105 2 1 1 1
106 select server_id, master_server_id, count, a from t1_max$EX order by count;
107 server_id master_server_id count a
108 select server_id, master_server_id, count, a from t1_max_delete_win$EX order by count;
109 server_id master_server_id count a
110 delete from t1_max$EX;
111 delete from t1_max_delete_win$EX;
112 delete from t1_old$EX;
113 update t1_old set X = 3, b=repeat('Master X=3', 1002);
114 "Test 4"
115 update t1_old set X = 4, b=repeat('Slave X=4',2000);
116 update t1_max set X = 4, b=repeat('Slave X=4',2000);
117 update t1_max_delete_win set X = 4, b=repeat('Slave X=4',2000);
118 delete from t1_old;
119 delete from t1_max;
120 delete from t1_max_delete_win;
121 "Expect t1_old and t1_max to contain slave row, and t1_max_delete_win to be empty(as master)"
122 select a, left(b, 20), length(b), X from t1_old;
123 a left(b, 20) length(b) X
124 1 Slave X=4Slave X=4Sl 18000 4
125 select a, left(b, 20), length(b), X from t1_max;
126 a left(b, 20) length(b) X
127 1 Slave X=4Slave X=4Sl 18000 4
128 select a, left(b, 20), length(b), X from t1_max_delete_win;
129 a left(b, 20) length(b) X
130 Expect t1_old and t1_max to contain 1 entry, and t1_max_delete_win to be empty
131 select server_id, master_server_id, count, a from t1_old$EX order by count;
132 server_id master_server_id count a
133 2 1 2 1
134 select server_id, master_server_id, count, a from t1_max$EX order by count;
135 server_id master_server_id count a
136 2 1 1 1
137 select server_id, master_server_id, count, a from t1_max_delete_win$EX order by count;
138 server_id master_server_id count a
139 delete from t1_max$EX;
140 delete from t1_max_delete_win$EX;
141 delete from t1_old$EX;
142 delete from t1_old;
143 delete from t1_max;
144 delete from t1_max_delete_win;
145 delete from t1_old;
146 delete from t1_max;
147 delete from t1_max_delete_win;
148 "Test 5"
149 Test that Updates affecting Blobs are rejected
150 correctly on the slave
151 drop table t1_max;
152 create table t1_max (a int primary key, b int, c longtext, d longtext, X int unsigned) engine = ndb;
153 insert into t1_max values (1, 1, repeat("B", 10000), repeat("E", 10001), 1);
154 insert into t1_max values (2, 2, repeat("A", 10002), repeat("T", 10003), 1);
155 update t1_max set X=20;
156 Initial values on Slave
157 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max order by a;
158 a b SHA1(c) length(c) SHA1(d) length(d) X
159 1 1 4a222e18b539cdefbf0960eaa7f4362a4976e1e0 10000 9641d473ab1bd921263190eee074397084933e2d 10001 20
160 2 2 f833241322c062495632923d74314a6a5c23034d 10002 2dad269dfa115f6c7e53e91a73251e597aab8fe9 10003 20
161 Originate update which will be rejected
162 update t1_max set c=repeat("Z", 10006), d=repeat("I", 10005), X=2 where a=1;
163 Check slave has rejected due to lower version
164 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max order by a;
165 a b SHA1(c) length(c) SHA1(d) length(d) X
166 1 1 4a222e18b539cdefbf0960eaa7f4362a4976e1e0 10000 9641d473ab1bd921263190eee074397084933e2d 10001 20
167 2 2 f833241322c062495632923d74314a6a5c23034d 10002 2dad269dfa115f6c7e53e91a73251e597aab8fe9 10003 20
168 Originate delete which will be rejected (due to NDB-OLD) algorith
169 delete from t1_max where a=1;
170 Check slave has rejected due to before image mismatch
171 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max order by a;
172 a b SHA1(c) length(c) SHA1(d) length(d) X
173 1 1 4a222e18b539cdefbf0960eaa7f4362a4976e1e0 10000 9641d473ab1bd921263190eee074397084933e2d 10001 20
174 2 2 f833241322c062495632923d74314a6a5c23034d 10002 2dad269dfa115f6c7e53e91a73251e597aab8fe9 10003 20
175 Originate insert which will be rejected (as row exists)
176 insert into t1_max values (1, 1, repeat("R", 10004), repeat("A", 10007), 1);
177 Check slave has rejected due to row existing already
178 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max order by a;
179 a b SHA1(c) length(c) SHA1(d) length(d) X
180 1 1 4a222e18b539cdefbf0960eaa7f4362a4976e1e0 10000 9641d473ab1bd921263190eee074397084933e2d 10001 20
181 2 2 f833241322c062495632923d74314a6a5c23034d 10002 2dad269dfa115f6c7e53e91a73251e597aab8fe9 10003 20
182 Expect t1_max to have 3 entries
183 select server_id, master_server_id, count, a from t1_old$EX order by count;
184 server_id master_server_id count a
185 select server_id, master_server_id, count, a from t1_max$EX order by count;
186 server_id master_server_id count a
187 2 1 1 1
188 2 1 2 1
189 2 1 3 1
190 select server_id, master_server_id, count, a from t1_max_delete_win$EX order by count;
191 server_id master_server_id count a
192 delete from t1_max$EX;
193 delete from t1_max_delete_win$EX;
194 delete from t1_old$EX;
195 Test 6
196 Check that non-Blob related operations in a batch with a Blob
197 operation are still subject to conflict detection.
198
199 insert into mysql.ndb_replication values ("test", "t2_max", 0, 7, "NDB$MAX(X)");
200 create table `t2_max$EX`
201 (server_id int unsigned,
202 master_server_id int unsigned,
203 master_epoch bigint unsigned,
204 count int unsigned,
205 a int not null,
206 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
207 create table t2_max (a int primary key, b int, X bigint unsigned) engine=ndb;
208 insert into t2_max values (1,1,10), (2,2,10), (3,3,10), (4,4,10), (5,5,10);
209 Now issue a transaction with a successful Blob op, and unsuccessful
210 non-Blob op. Check that the Blob op succeeds, and the unsuccessful
211 non-Blob op is handled as expected.
212 begin;
213 update t2_max set b=b+1, X=1 where a=3;
214 update t1_max set c=repeat("R", 10008), d=repeat("A", 10009), X = 21 where a=1;
215 commit;
216 Contents on Slave
217 Expect Blob data applied to t1_max, no update applied to t2_max
218 select a,b,left(c,1), length(c), left(d,1), length(d), X from t1_max where a=1;
219 a b left(c,1) length(c) left(d,1) length(d) X
220 1 1 R 10008 A 10009 21
221 select * from t2_max order by a;
222 a b X
223 1 1 10
224 2 2 10
225 3 3 10
226 4 4 10
227 5 5 10
228 Expect No conflict in t1_max, 1 conflict in t2_max
229 select server_id, master_server_id, count, a from t1_max$EX order by count;
230 server_id master_server_id count a
231 select server_id, master_server_id, count, a from t2_max$EX order by count;
232 server_id master_server_id count a
233 2 1 1 3
234 drop table t2_max, t2_max$EX;
235 drop table t1_old, `t1_old$EX`, t1_max, `t1_max$EX`, t1_max_delete_win, `t1_max_delete_win$EX`;
236 delete from mysql.ndb_replication;
237 Test that online table distribution sets up conflict functions and exceptions tables
238 insert into mysql.ndb_replication values ("test", "t1allsame", 0, 7, "NDB$MAX(X)");
239 insert into mysql.ndb_replication values ("test", "t2diffex", 1, 7, "NDB$OLD(X)");
240 insert into mysql.ndb_replication values ("test", "t2diffex", 3, 7, "NDB$MAX(X)");
241 insert into mysql.ndb_replication values ("test", "t3oneex", 3, 7, "NDB$EPOCH()");
242 create table t2diffex$EX (
243 server_id int unsigned,
244 master_server_id int unsigned,
245 master_epoch bigint unsigned,
246 count int unsigned,
247 a int not null,
248 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
249 create table t3oneex$EX (
250 server_id int unsigned,
251 master_server_id int unsigned,
252 master_epoch bigint unsigned,
253 count int unsigned,
254 a int not null,
255 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
256 create table t1allsame(a int primary key, b varchar(200), X int unsigned) engine=ndb;
257 create table t2diffex(a int primary key, b varchar(200), X int unsigned) engine=ndb;
258 create table t3oneex(a int primary key, b varchar(200)) engine=ndb;
259 Warnings:
260 Warning 1296 Node <nodeid> 'Distribution of CREATE TABLE 't3oneex' failed'
261 show variables like 'server_id';
262 Variable_name Value
263 server_id 1
264 MySQLD error output for server 1.1 matching pattern %Replica:%
265 relevant
266 [system] replica: table test.t2diffex using conflict_fn ndb$old on attribute x.
267 [system] replica: table test.t2diffex logging exceptions to test.t2diffex$ex
268 [system] replica: table test.t1allsame using conflict_fn ndb$max on attribute x.
269 [system] replica: table test.t2_max using conflict_fn ndb$max on attribute x.
270 show variables like 'server_id';
271 Variable_name Value
272 server_id 3
273 MySQLD error output for server 2.1 matching pattern %Replica:%
274 relevant
275 [warning] replica: table test.t3oneex : no extra row author bits in table.
276 [system] replica: table test.t3oneex : ndb$epoch, low epoch resolution
277 [system] replica: table test.t2diffex using conflict_fn ndb$max on attribute x.
278 [system] replica: table test.t2diffex logging exceptions to test.t2diffex$ex
279 [system] replica: table test.t1allsame using conflict_fn ndb$max on attribute x.
280 [system] replica: table test.t2_max using conflict_fn ndb$max on attribute x.
281 drop table t3oneex, t2diffex, t1allsame, t3oneex$EX, t2diffex$EX;
282 delete from mysql.ndb_replication;
283 Test exceptions table schema flexibility
284 insert into mysql.ndb_replication values ("test", "t1", 0, 7, "NDB$MAX(X)");
285 Test 'normal' mandatory column names + all table pks
286 create table test.t1$EX(
287 server_id int unsigned,
288 master_server_id int unsigned,
289 master_epoch bigint unsigned,
290 count int unsigned,
291 a int not null,
292 b int not null,
293 c int not null,
294 primary key (server_id, master_server_id, master_epoch, count)) engine=ndb;
295 create table test.t1 (a int, b int, c int, d int, e int, X int unsigned,
296 primary key(a,b,c)) engine=ndb;
297 Generate a conflict on the slave
298 insert into test.t1 values (1,1,1,1,1,1);
299 update test.t1 set X=0 where a=1 and b=1 and c=1;
300 Check that conflict has been recorded.
301 select * from test.t1$EX;
302 server_id master_server_id master_epoch count a b c
303 2 1 <epoch_num> 1 1 1 1
304 drop table test.t1;
305 drop table test.t1$EX;
306 Test 'normal' mandatory column names + all table pks +
307 extra columns with same and different names to main table columns
308 Also a defaulted extra column.
309 create table test.t1$EX(
310 server_id int unsigned,
311 master_server_id int unsigned,
312 master_epoch bigint unsigned,
313 count int unsigned,
314 a int not null,
315 b int not null,
316 c int not null,
317 d int, # Same name as main table, but user defined
318 lilljeholmen varchar(50) default 'Slussen',
319 # Separate, user defined
320 primary key (server_id, master_server_id, master_epoch, count)) engine=ndb;
321 create table test.t1 (a int, b int, c int, d int, e int, X int unsigned,
322 primary key(a,b,c)) engine=ndb;
323 Generate a conflict on the slave
324 insert into test.t1 values (1,1,1,1,1,1);
325 update test.t1 set X=0 where a=1 and b=1 and c=1;
326 Check that conflict has been recorded.
327 select * from test.t1$EX;
328 server_id master_server_id master_epoch count a b c d lilljeholmen
329 2 1 <epoch_num> 1 1 1 1 NULL Slussen
330 drop table test.t1;
331 drop table test.t1$EX;
332 Test unusual mandatory column names + all table pks +
333 extra columns with same and different names to main table columns
334 Also a defaulted extra column.
335 create table test.t1$EX(
336 monteverdi int unsigned,
337 asparagi int unsigned,
338 plenipotentiary bigint unsigned,
339 mountebank int unsigned,
340 a int not null,
341 b int not null,
342 c int not null,
343 d int, # Same name as main table, but user defined
344 lilljeholmen varchar(50) default 'Slussen',
345 # Separate, user defined
346 primary key (monteverdi, asparagi, plenipotentiary, mountebank)) engine=ndb;
347 create table test.t1 (a int, b int, c int, d int, e int, X int unsigned,
348 primary key(a,b,c)) engine=ndb;
349 Generate a conflict on the slave
350 insert into test.t1 values (1,1,1,1,1,1);
351 update test.t1 set X=0 where a=1 and b=1 and c=1;
352 Check that conflict has been recorded.
353 select * from test.t1$EX;
354 monteverdi asparagi plenipotentiary mountebank a b c d lilljeholmen
355 2 1 <epoch_num> 1 1 1 1 NULL Slussen
356 drop table test.t1;
357 drop table test.t1$EX;
358 Test unusual mandatory column names + all table pks which are same
359 as 'normal' exceptions table column names plus extra columns with
360 same and different names to main table columns
361 Also a defaulted extra column.
362 create table test.t1$EX(
363 monteverdi int unsigned,
364 asparagi int unsigned,
365 plenipotentiary bigint unsigned,
366 mountebank int unsigned,
367 server_id int unsigned not null,
368 master_server_id int unsigned not null,
369 master_epoch bigint unsigned not null,
370 count int unsigned not null,
371 d int, # Same name as main table, but user defined
372 lilljeholmen varchar(50) default 'Slussen',
373 # Separate, user defined
374 primary key (monteverdi, asparagi, plenipotentiary, mountebank)) engine=ndb;
375 create table test.t1 (server_id int unsigned,
376 master_server_id int unsigned,
377 master_epoch bigint unsigned,
378 count int unsigned,
379 d int, e int, X int unsigned,
380 primary key(server_id, master_server_id,
381 master_epoch, count)) engine=ndb;
382 Generate a conflict on the slave
383 insert into test.t1 values (1,1,1,1,1,1,1);
384 update test.t1 set X=0 where server_id=1 and master_server_id=1 and master_epoch=1 and count=1;
385 Check that conflict has been recorded.
386 select * from test.t1$EX;
387 monteverdi asparagi plenipotentiary mountebank server_id master_server_id master_epoch count d lilljeholmen
388 2 1 <epoch_num> 1 1 1 1 1 NULL Slussen
389 drop table test.t1;
390 drop table test.t1$EX;
391 And some bad exceptions table schemata
392 Keys in wrong positions
393 create table test.t1$EX(
394 a int not null,
395 b int not null,
396 c int not null,
397 d int, # Same name as main table, but user defined
398 lilljeholmen varchar(50) default 'Slussen',
399 # Separate, user defined
400 server_id int unsigned,
401 master_server_id int unsigned,
402 master_epoch bigint unsigned,
403 count int unsigned,
404 primary key (server_id, master_server_id, master_epoch, count)) engine=ndb;
405 create table test.t1 (a int, b int, c int, d int, e int, X int unsigned,
406 primary key(a,b,c)) engine=ndb;
407 show warnings;
408 Level Code Message
409 MySQLD error output for server 1.1 matching pattern %Replica\:%
410 relevant
411 [system] replica: table test.t1 using conflict_fn ndb$max on attribute x.
412 [warning] replica: exceptions table t1$ex has wrong definition (initial 4 columns)
413 drop table test.t1;
414 drop table test.t1$EX;
415 Test conflict_fn definitions in ndb_replication which cover
416 exceptions tables and verify that conflict functions are not
417 set up for exceptions tables.
418 insert into mysql.ndb_replication values ("test", "t2%", 1, 7, "NDB$EPOCH()");
419 create table test.t2$EX (
420 server_id int unsigned NOT NULL,
421 master_server_id int unsigned NOT NULL,
422 master_epoch bigint unsigned NOT NULL,
423 count int unsigned NOT NULL,
424 a int NOT NULL,
425 PRIMARY KEY (server_id,master_server_id,master_epoch,count)
426 ) ENGINE=ndbcluster;
427 create table test.t2 (
428 a int primary key,
429 b varchar(100)) engine=ndb;
430 count(*)
431 1
432 count(*)
433 1
434 drop table test.server_error_log;
435 drop table test.t2;
436 drop table test.t2$EX;
437 "Cleanup"
438 drop table mysql.ndb_replication;
439 include/rpl_end.inc