Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_restart.test

raw
1 -- source include/have_ndb.inc
2 -- source ndb_mgm.inc
3 -- source ndb_waiter.inc
4
5 # Ignore the warning generated by ndbcluster's binlog thread
6 # when cluster is restarted
7 --disable_query_log ONCE
8 call mtr.add_suppression("cluster disconnect An incident event has been written");
9
10 # Ignore the warning generated by ndbcluster's binlog thread
11 # when cluster is restarted - also on the second mysqld
12 connect(mysqld2,127.0.0.1,root,,test,$MASTER_MYPORT1);
13 connection mysqld2;
14 --disable_query_log ONCE
15 call mtr.add_suppression("cluster disconnect An incident event has been written");
16 connection default;
17
18 # Create rows table and fill with 1000 rows
19 create table `rows`(a int primary key) engine=ndb;
20
21 --disable_query_log
22 let $i = 0;
23 while($i < 1000)
24 {
25 eval insert into `rows`(a) values ($i);
26 inc $i;
27 }
28 --enable_query_log
29 select count(a) from `rows`;
30
31 create table p1 (name varchar(255),
32 id int(11) auto_increment,
33 parent_id int,
34 parent_c1 int,
35 parent_c2 int,
36 primary key(id),
37 unique(name),
38 key k2(parent_id),
39 unique(parent_c1, parent_c2)
40 )engine = ndb;
41
42 create table c1(c1 int AUTO_INCREMENT,
43 id int(11),
44 primary key(c1),
45 unique key abc (id),
46 key (c1),
47 constraint fk1 foreign key (id) references p1 (id)
48 ) engine=ndb;
49
50 create table c2(c2 varchar(100),
51 id int(11),
52 parent_temp int,
53 parent_rc1 int,
54 parent_rc2 int,
55 parent_rc3 int,
56 primary key(c2),
57 unique key abc(c2),
58 unique key abd(parent_temp),
59 constraint c2_fk1 foreign key (id) references p1 (id),
60 constraint c2_fk2 foreign key (parent_rc1, parent_rc2) references p1(parent_c1, parent_c2),
61 constraint c2_fk3 foreign key (parent_rc3) references c1(id)
62 ) engine=ndb;
63
64 # Populate parent table
65 insert into p1(parent_id) select a from `rows`;
66
67 select count(1) from test.p1;
68
69 --echo -- Show Fks working
70 insert into test.c1(id) select id from p1;
71 delete from test.c1;
72 insert into test.c1(id) select id from p1;
73 delete from test.c1;
74 delete from test.p1;
75 insert into p1(parent_id) select a from `rows`;
76 insert into test.c1(id) select id from p1;
77 delete from test.c1;
78
79
80 --echo Check interaction of inplace alters and restarts
81
82 create table parent (parent_pk int,
83 parent_uk int,
84 b int,
85 primary key (parent_pk),
86 unique(parent_uk)
87 ) engine=ndb;
88
89 create table child (child_pk int,
90 child_uk int,
91 child_ord int,
92 data int,
93 primary key(child_pk),
94 unique(child_uk),
95 key(child_ord),
96 constraint pkpk foreign key (child_pk) references parent (parent_pk), # PK->PK
97 constraint pkuk foreign key (child_pk) references parent (parent_uk), # PK->UK
98 constraint ukpk foreign key (child_uk) references parent (parent_pk), # UK->PK
99 constraint ukuk foreign key (child_uk) references parent (parent_uk), # UK->UK
100 constraint ordpk foreign key (child_ord) references parent (parent_pk), # ORD -> PK
101 constraint orduk foreign key (child_ord) references parent (parent_uk) # ORD -> UK
102 ) engine=ndb;
103
104 insert into parent values
105 (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8), (9,9,9), (10,10,10),
106 (11,100,11),
107 (200,12,200);
108
109 --echo Succesful inserts
110 insert into child values
111 (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5), (6,6,6,6), (7,7,7,7), (8,8,8,8), (9,9,9,9);
112
113 --echo FK checks working
114 --error 1452
115 insert into child values (20, 10, 10, 10); # PK -> PK
116 --error 1452
117 insert into child values (11, 11, 10, 10); # PK -> UK
118 --error 1452
119 insert into child values (10, 12, 10, 10); # UK -> PK
120 --error 1452
121 insert into child values (10, 200, 10, 10); # UK -> UK
122 --error 1452
123 insert into child values (10, 10, 12, 10); # ORD -> PK
124 --error 1452
125 insert into child values (10, 10, 200, 10); # ORD -> UK
126
127 --echo Capture initial schema versions for parent
128 --echo and child tables and unique indices involved
129 --echo in foreign keys
130
131 --disable_query_log
132 # Parent table
133 select version & 0xffffff,
134 version >> 24
135 into @vpmaj, @vpmin
136 from ndbinfo.dict_obj_info
137 where fq_name like 'test/def/parent';
138
139 # Parent unique
140 select version & 0xffffff,
141 version >> 24
142 into @vpumaj, @vpumin
143 from ndbinfo.dict_obj_info
144 where fq_name like 'sys/def/%parent_uk$unique';
145
146 # Child table
147 select version & 0xffffff,
148 version >> 24
149 into @vcmaj, @vcmin
150 from ndbinfo.dict_obj_info
151 where fq_name like 'test/def/child';
152
153 # Child unique
154 select version & 0xffffff,
155 version >> 24
156 into @vcumaj, @vcumin
157 from ndbinfo.dict_obj_info
158 where fq_name like 'sys/def/%child_uk$unique';
159
160 # Child ordered
161 select version & 0xffffff,
162 version >> 24
163 into @vcomaj, @vcomin
164 from ndbinfo.dict_obj_info
165 where fq_name like 'sys/def/%child_ord';
166 --enable_query_log
167
168 --echo Now alter the tables and indices inplace
169 --echo Existing FKs should be unaffected.
170
171 --echo Alter parent
172
173 --echo Rename table x 2
174 rename table parent to guardian;
175 rename table guardian to parent;
176
177 --echo Parent metadata version diff
178 --disable_query_log
179 --echo Parent table version diff
180 select (version & 0xffffff) - @vpmaj as major_diff,
181 (version >> 24) - @vpmin as minor_diff
182 from ndbinfo.dict_obj_info
183 where fq_name like 'test/def/parent';
184
185 --echo Parent unique version diff
186 select (version & 0xffffff) - @vpumaj as major_diff,
187 (version >> 24) - @vpumin as minor_diff
188 from ndbinfo.dict_obj_info
189 where fq_name like 'sys/def/%parent_uk$unique';
190
191 # Following fails with
192 # ERROR 1846 (0A000): ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.
193
194 #--echo Rename a column
195 #alter table parent rename column b to bee;
196 #
197 #--echo Parent metadata version diff
198 #--echo Parent table version diff
199 #select (version & 0xffffff) - @vpmaj as major_diff,
200 # (version >> 24) - @vpmin as minor_diff
201 # from ndbinfo.dict_obj_info
202 # where fq_name like 'test/def/parent';
203 #
204 #--echo Parent unique version diff
205 #select (version & 0xffffff) - @vpumaj as major_diff,
206 # (version >> 24) - @vpumin as minor_diff
207 # from ndbinfo.dict_obj_info
208 # where fq_name like 'sys/def/%parent_uk$unique';
209 --enable_query_log
210
211 --echo Add a column
212 alter table parent add column extra int;
213
214 --echo Parent metadata version diff
215 --disable_query_log
216 --echo Parent table version diff
217 select (version & 0xffffff) - @vpmaj as major_diff,
218 (version >> 24) - @vpmin as minor_diff
219 from ndbinfo.dict_obj_info
220 where fq_name like 'test/def/parent';
221
222 --echo Parent unique version diff
223 select (version & 0xffffff) - @vpumaj as major_diff,
224 (version >> 24) - @vpumin as minor_diff
225 from ndbinfo.dict_obj_info
226 where fq_name like 'sys/def/%parent_uk$unique';
227
228 --enable_query_log
229
230 --echo Fiddle with READ_BACKUP
231 alter table parent comment='NDB_TABLE=READ_BACKUP=0';
232 alter table parent comment='NDB_TABLE=READ_BACKUP=1';
233
234 --echo Parent metadata version diff
235 --disable_query_log
236 --echo Parent table version diff
237 select (version & 0xffffff) - @vpmaj as major_diff,
238 (version >> 24) - @vpmin as minor_diff
239 from ndbinfo.dict_obj_info
240 where fq_name like 'test/def/parent';
241
242 --echo Parent unique version diff
243 select (version & 0xffffff) - @vpumaj as major_diff,
244 (version >> 24) - @vpumin as minor_diff
245 from ndbinfo.dict_obj_info
246 where fq_name like 'sys/def/%parent_uk$unique';
247
248 --enable_query_log
249
250 --echo Alter child
251
252 --echo Rename table
253 # Note that renaming a child table which has
254 # fk constraints with auto-generated names results
255 # in the fks all being replaced with fresh fks
256 # In this test the constraints have specific names to
257 # avoid this behaviour.
258 #
259 rename table child to junior;
260 rename table junior to child;
261
262 --echo Child metadata diff
263 --disable_query_log
264 --echo Child table version diff
265 select (version & 0xffffff) - @vcmaj as major_diff,
266 (version >> 24) - @vcmin as minor_diff
267 from ndbinfo.dict_obj_info
268 where fq_name like 'test/def/child';
269
270 --echo Child unique version diff
271 select (version & 0xffffff) - @vcumaj as major_diff,
272 (version >> 24) - @vcumin as minor_diff
273 from ndbinfo.dict_obj_info
274 where fq_name like 'sys/def/%child_uk$unique';
275
276 --echo Child ordered version diff
277 select (version & 0xffffff) - @vcomaj as major_diff,
278 (version >> 24) - @vcomin as minor_diff
279 from ndbinfo.dict_obj_info
280 where fq_name like 'sys/def/%child_ord';
281
282 --enable_query_log
283
284 --echo Rename a column
285 alter table child rename column data to datum;
286
287 --echo Child metadata diff
288 --disable_query_log
289 --echo Child table version diff
290 select (version & 0xffffff) - @vcmaj as major_diff,
291 (version >> 24) - @vcmin as minor_diff
292 from ndbinfo.dict_obj_info
293 where fq_name like 'test/def/child';
294
295 --echo Child unique version diff
296 select (version & 0xffffff) - @vcumaj as major_diff,
297 (version >> 24) - @vcumin as minor_diff
298 from ndbinfo.dict_obj_info
299 where fq_name like 'sys/def/%child_uk$unique';
300
301 --echo Child ordered version diff
302 select (version & 0xffffff) - @vcomaj as major_diff,
303 (version >> 24) - @vcomin as minor_diff
304 from ndbinfo.dict_obj_info
305 where fq_name like 'sys/def/%child_ord';
306
307 --enable_query_log
308
309 --echo Add a column
310 alter table child add column extra int;
311
312 --echo Child metadata diff
313 --disable_query_log
314
315 --echo Child table version diff
316 select (version & 0xffffff) - @vcmaj as major_diff,
317 (version >> 24) - @vcmin as minor_diff
318 from ndbinfo.dict_obj_info
319 where fq_name like 'test/def/child';
320
321 --echo Child unique version diff
322 select (version & 0xffffff) - @vcumaj as major_diff,
323 (version >> 24) - @vcumin as minor_diff
324 from ndbinfo.dict_obj_info
325 where fq_name like 'sys/def/%child_uk$unique';
326
327 --echo Child ordered version diff
328 select (version & 0xffffff) - @vcomaj as major_diff,
329 (version >> 24) - @vcomin as minor_diff
330 from ndbinfo.dict_obj_info
331 where fq_name like 'sys/def/%child_ord';
332
333 --enable_query_log
334
335 --echo Fiddle with readbackup
336
337 alter table child comment='NDB_TABLE=READ_BACKUP=0';
338 alter table child comment='NDB_TABLE=READ_BACKUP=1';
339
340 --echo Child metadata diff
341 --disable_query_log
342 --echo Child table version diff
343 select (version & 0xffffff) - @vcmaj as major_diff,
344 (version >> 24) - @vcmin as minor_diff
345 from ndbinfo.dict_obj_info
346 where fq_name like 'test/def/child';
347
348 --echo Child unique version diff
349 select (version & 0xffffff) - @vcumaj as major_diff,
350 (version >> 24) - @vcumin as minor_diff
351 from ndbinfo.dict_obj_info
352 where fq_name like 'sys/def/%child_uk$unique';
353
354 --echo Child ordered version diff
355 select (version & 0xffffff) - @vcomaj as major_diff,
356 (version >> 24) - @vcomin as minor_diff
357 from ndbinfo.dict_obj_info
358 where fq_name like 'sys/def/%child_ord';
359
360 --enable_query_log
361
362 --echo -- Normal NR
363 --exec $_mgm_cmd -e "1 RESTART -n" >> $NDB_TOOLS_OUTPUT
364
365 --echo --- Wait for node to stop
366 --exec $_waiter_cmd --not-started -w1 >> $NDB_TOOLS_OUTPUT
367
368 --echo --- Show FKs working
369 insert into test.c1(id) select id from p1;
370 delete from test.c1;
371 insert into test.c1(id) select id from p1;
372 delete from test.c1;
373 delete from test.p1;
374 insert into p1(parent_id) select a from `rows`;
375 insert into test.c1(id) select id from p1;
376 delete from test.c1;
377
378 --echo FK checks working
379 --error 1452
380 insert into child values (20, 10, 10, 10, 10); # PK -> PK
381 --error 1452
382 insert into child values (11, 11, 10, 10, 10); # PK -> UK
383 --error 1452
384 insert into child values (10, 12, 10, 10, 10); # UK -> PK
385 --error 1452
386 insert into child values (10, 200, 10, 10, 10); # UK -> UK
387 --error 1452
388 insert into child values (10, 10, 12, 10, 10); # ORD -> PK
389 --error 1452
390 insert into child values (10, 10, 200, 10, 10); # ORD -> UK
391
392
393 --echo --- Startup node again
394 --exec $_mgm_cmd -e "ALL START" >> $NDB_TOOLS_OUTPUT
395
396 --echo --- Wait for node to recover
397 --exec $_waiter_cmd >> $NDB_TOOLS_OUTPUT
398
399 --echo --- Show FKs working
400 insert into test.c1(id) select id from p1;
401 delete from test.c1;
402 insert into test.c1(id) select id from p1;
403 delete from test.c1;
404 delete from test.p1;
405 insert into p1(parent_id) select a from `rows`;
406 insert into test.c1(id) select id from p1;
407 delete from test.c1;
408
409 --echo FK checks working
410 --error 1452
411 insert into child values (20, 10, 10, 10, 10); # PK -> PK
412 --error 1452
413 insert into child values (11, 11, 10, 10, 10); # PK -> UK
414 --error 1452
415 insert into child values (10, 12, 10, 10, 10); # UK -> PK
416 --error 1452
417 insert into child values (10, 200, 10, 10, 10); # UK -> UK
418 --error 1452
419 insert into child values (10, 10, 12, 10, 10); # ORD -> PK
420 --error 1452
421 insert into child values (10, 10, 200, 10, 10); # ORD -> UK
422
423
424
425 --echo -- Initial NR
426
427 --exec $_mgm_cmd -e "2 RESTART -n -i" >> $NDB_TOOLS_OUTPUT
428
429 --echo --- Wait for node to stop
430 --exec $_waiter_cmd --not-started -w2 >> $NDB_TOOLS_OUTPUT
431
432 --echo --- Show FKs working
433 insert into test.c1(id) select id from p1;
434 delete from test.c1;
435 insert into test.c1(id) select id from p1;
436 delete from test.c1;
437 delete from test.p1;
438 insert into p1(parent_id) select a from `rows`;
439 insert into test.c1(id) select id from p1;
440 delete from test.c1;
441
442 --echo FK checks working
443 --error 1452
444 insert into child values (20, 10, 10, 10, 10); # PK -> PK
445 --error 1452
446 insert into child values (11, 11, 10, 10, 10); # PK -> UK
447 --error 1452
448 insert into child values (10, 12, 10, 10, 10); # UK -> PK
449 --error 1452
450 insert into child values (10, 200, 10, 10, 10); # UK -> UK
451 --error 1452
452 insert into child values (10, 10, 12, 10, 10); # ORD -> PK
453 --error 1452
454 insert into child values (10, 10, 200, 10, 10); # ORD -> UK
455
456
457 --echo --- Startup node again
458 --exec $_mgm_cmd -e "ALL START" >> $NDB_TOOLS_OUTPUT
459
460 --echo --- Wait for node to recover
461 --exec $_waiter_cmd >> $NDB_TOOLS_OUTPUT
462
463 --echo --- Show FKs working
464 insert into test.c1(id) select id from p1;
465 delete from test.c1;
466 insert into test.c1(id) select id from p1;
467 delete from test.c1;
468 delete from test.p1;
469 insert into p1(parent_id) select a from `rows`;
470 insert into test.c1(id) select id from p1;
471 delete from test.c1;
472
473 --echo FK checks working
474 --error 1452
475 insert into child values (20, 10, 10, 10, 10); # PK -> PK
476 --error 1452
477 insert into child values (11, 11, 10, 10, 10); # PK -> UK
478 --error 1452
479 insert into child values (10, 12, 10, 10, 10); # UK -> PK
480 --error 1452
481 insert into child values (10, 200, 10, 10, 10); # UK -> UK
482 --error 1452
483 insert into child values (10, 10, 12, 10, 10); # ORD -> PK
484 --error 1452
485 insert into child values (10, 10, 200, 10, 10); # ORD -> UK
486
487
488 --echo -- System restart
489 --exec $_mgm_cmd -e "ALL RESTART -n" >> $NDB_TOOLS_OUTPUT
490
491 --echo --- Wait for node to stop
492 --exec $_waiter_cmd --not-started >> $NDB_TOOLS_OUTPUT
493
494 --echo --- Startup nodes again
495 --exec $_mgm_cmd -e "ALL START" >> $NDB_TOOLS_OUTPUT
496
497 --echo --- Wait for nodes to recover
498 --exec $_waiter_cmd >> $NDB_TOOLS_OUTPUT
499
500 --echo --- Wait to establish connection
501 --source include/ndb_not_readonly.inc
502
503 --echo --- Show FKs working
504 insert into test.c1(id) select id from p1;
505 delete from test.c1;
506 insert into test.c1(id) select id from p1;
507 delete from test.c1;
508 delete from test.p1;
509 insert into p1(parent_id) select a from `rows`;
510 insert into test.c1(id) select id from p1;
511 delete from test.c1;
512
513 --echo FK checks working
514 --error 1452
515 insert into child values (20, 10, 10, 10, 10); # PK -> PK
516 --error 1452
517 insert into child values (11, 11, 10, 10, 10); # PK -> UK
518 --error 1452
519 insert into child values (10, 12, 10, 10, 10); # UK -> PK
520 --error 1452
521 insert into child values (10, 200, 10, 10, 10); # UK -> UK
522 --error 1452
523 insert into child values (10, 10, 12, 10, 10); # ORD -> PK
524 --error 1452
525 insert into child values (10, 10, 200, 10, 10); # ORD -> UK
526
527
528 delete from test.p1;
529
530 drop table test.c2;
531 drop table test.c1;
532 drop table test.p1;
533 drop table test.`rows`;
534
535 drop table test.child;
536 drop table test.parent;
537 --remove_file $NDB_TOOLS_OUTPUT