Debian Codegraph @

mysql-test/suite/ndb/r/ndb_fk_restore.result

raw
1 # bug#16285826
2 create table t1 (
3 a1 int primary key
4 ) engine ndb;
5 insert into t1 (a1) values
6 (1),(2),(3),(4),(5),(6),(7),(8),(9);
7 create table t2 (
8 a2 int primary key auto_increment,
9 b2 int not null,
10 key xb2 (b2),
11 constraint fkb2a1 foreign key (b2) references t1 (a1)
12 ) engine ndb;
13 # TEST: basic meta
14 # show meta
15 show create table t2;
16 Table Create Table
17 t2 CREATE TABLE `t2` (
18 `a2` int NOT NULL AUTO_INCREMENT,
19 `b2` int NOT NULL,
20 PRIMARY KEY (`a2`),
21 KEY `xb2` (`b2`),
22 CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`)
23 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
24 select type,name from ndb_show_tables_results
25 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
26 type name
27 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
28 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
29 'ForeignKey' 'XX/XX/fkb2a1'
30 # show counts
31 select count(*) from t1;
32 count(*)
33 9
34 select count(*) from t2;
35 count(*)
36 0
37 # run backup
38 # drop tables
39 drop table t2, t1;
40 # restore meta
41 # restore data
42 # show meta
43 show create table t2;
44 Table Create Table
45 t2 CREATE TABLE `t2` (
46 `a2` int NOT NULL AUTO_INCREMENT,
47 `b2` int NOT NULL,
48 PRIMARY KEY (`a2`),
49 KEY `xb2` (`b2`),
50 CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`)
51 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
52 select type,name from ndb_show_tables_results
53 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
54 type name
55 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
56 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
57 'ForeignKey' 'XX/XX/fkb2a1'
58 # show counts
59 select count(*) from t1;
60 count(*)
61 9
62 select count(*) from t2;
63 count(*)
64 0
65 # TEST: basic data
66 insert into t2 (b2) select x.a1 from t1 x,t1 y;
67 insert into t2 (b2) values (10);
68 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
69 delete from t1 where a1 = 1;
70 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
71 # show meta
72 show create table t2;
73 Table Create Table
74 t2 CREATE TABLE `t2` (
75 `a2` int NOT NULL AUTO_INCREMENT,
76 `b2` int NOT NULL,
77 PRIMARY KEY (`a2`),
78 KEY `xb2` (`b2`),
79 CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`)
80 ) ENGINE=ndbcluster AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
81 select type,name from ndb_show_tables_results
82 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
83 type name
84 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
85 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
86 'ForeignKey' 'XX/XX/fkb2a1'
87 # show counts
88 select count(*) from t1;
89 count(*)
90 9
91 select count(*) from t2;
92 count(*)
93 81
94 # run backup
95 # drop tables
96 drop table t2, t1;
97 # restore meta
98 # restore data
99 # show meta
100 show create table t2;
101 Table Create Table
102 t2 CREATE TABLE `t2` (
103 `a2` int NOT NULL AUTO_INCREMENT,
104 `b2` int NOT NULL,
105 PRIMARY KEY (`a2`),
106 KEY `xb2` (`b2`),
107 CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`)
108 ) ENGINE=ndbcluster AUTO_INCREMENT=513 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
109 select type,name from ndb_show_tables_results
110 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
111 type name
112 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
113 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
114 'ForeignKey' 'XX/XX/fkb2a1'
115 # show counts
116 select count(*) from t1;
117 count(*)
118 9
119 select count(*) from t2;
120 count(*)
121 81
122 insert into t2 (b2) values (10);
123 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
124 delete from t1 where a1 = 1;
125 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
126 # TEST: child part key
127 drop table t2;
128 create table t2 (
129 a2 int primary key auto_increment,
130 b2 int not null,
131 c2 int not null,
132 key xb2c2 (b2,c2),
133 constraint fkb2a1 foreign key (b2) references t1 (a1)
134 ) engine ndb;
135 insert into t2 (b2,c2) select x.a1,99 from t1 x,t1 y;
136 insert into t2 (b2,c2) values (10,99);
137 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
138 delete from t1 where a1 = 1;
139 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
140 # show meta
141 show create table t2;
142 Table Create Table
143 t2 CREATE TABLE `t2` (
144 `a2` int NOT NULL AUTO_INCREMENT,
145 `b2` int NOT NULL,
146 `c2` int NOT NULL,
147 PRIMARY KEY (`a2`),
148 KEY `xb2c2` (`b2`,`c2`),
149 CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`)
150 ) ENGINE=ndbcluster AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
151 select type,name from ndb_show_tables_results
152 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
153 type name
154 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
155 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
156 'ForeignKey' 'XX/XX/fkb2a1'
157 # show counts
158 select count(*) from t1;
159 count(*)
160 9
161 select count(*) from t2;
162 count(*)
163 81
164 # run backup
165 # drop tables
166 drop table t2, t1;
167 # restore meta
168 # restore data
169 # show meta
170 show create table t2;
171 Table Create Table
172 t2 CREATE TABLE `t2` (
173 `a2` int NOT NULL AUTO_INCREMENT,
174 `b2` int NOT NULL,
175 `c2` int NOT NULL,
176 PRIMARY KEY (`a2`),
177 KEY `xb2c2` (`b2`,`c2`),
178 CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`)
179 ) ENGINE=ndbcluster AUTO_INCREMENT=513 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
180 select type,name from ndb_show_tables_results
181 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
182 type name
183 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
184 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
185 'ForeignKey' 'XX/XX/fkb2a1'
186 # show counts
187 select count(*) from t1;
188 count(*)
189 9
190 select count(*) from t2;
191 count(*)
192 81
193 insert into t2 (b2,c2) values (10,99);
194 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
195 delete from t1 where a1 = 1;
196 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2a1` FOREIGN KEY (`b2`) REFERENCES `t1` (`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
197 # TEST: column order
198 drop table t2,t1;
199 create table t1 (
200 a1 int primary key,
201 b1 int not null,
202 c1 int not null,
203 unique key (b1,c1) using hash
204 ) engine ndb;
205 insert into t1 (a1,b1,c1) values
206 (1,11,12),(2,21,22),(3,31,32),(4,41,42),(5,51,52),(6,61,62);
207 create table t2 (
208 a2 int primary key auto_increment,
209 b2 int not null,
210 c2 int not null,
211 key xb2c2 (b2,c2),
212 constraint fkb2c2c1b1 foreign key (b2,c2) references t1 (c1,b1)
213 ) engine ndb;
214 insert into t2 (b2,c2) select x.c1,x.b1 from t1 x,t1 y;
215 insert into t2 (b2,c2) values (61,62);
216 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
217 delete from t1 where b1=61 and c1=62;
218 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
219 # show meta
220 show create table t2;
221 Table Create Table
222 t2 CREATE TABLE `t2` (
223 `a2` int NOT NULL AUTO_INCREMENT,
224 `b2` int NOT NULL,
225 `c2` int NOT NULL,
226 PRIMARY KEY (`a2`),
227 KEY `xb2c2` (`b2`,`c2`),
228 CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`)
229 ) ENGINE=ndbcluster AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
230 select type,name from ndb_show_tables_results
231 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
232 type name
233 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
234 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
235 'ForeignKey' 'XX/XX/fkb2c2c1b1'
236 # show counts
237 select count(*) from t1;
238 count(*)
239 6
240 select count(*) from t2;
241 count(*)
242 36
243 # run backup
244 # drop tables
245 drop table t2, t1;
246 # restore meta
247 # restore data
248 # show meta
249 show create table t2;
250 Table Create Table
251 t2 CREATE TABLE `t2` (
252 `a2` int NOT NULL AUTO_INCREMENT,
253 `b2` int NOT NULL,
254 `c2` int NOT NULL,
255 PRIMARY KEY (`a2`),
256 KEY `xb2c2` (`b2`,`c2`),
257 CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`)
258 ) ENGINE=ndbcluster AUTO_INCREMENT=513 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
259 select type,name from ndb_show_tables_results
260 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
261 type name
262 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
263 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
264 'ForeignKey' 'XX/XX/fkb2c2c1b1'
265 # show counts
266 select count(*) from t1;
267 count(*)
268 6
269 select count(*) from t2;
270 count(*)
271 36
272 insert into t2 (b2,c2) values (61,62);
273 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
274 delete from t1 where b1=61 and c1=62;
275 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`c1`,`b1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
276 # TEST: disable indexes
277 # show meta
278 show create table t2;
279 Table Create Table
280 t2 CREATE TABLE `t2` (
281 `a2` int NOT NULL AUTO_INCREMENT,
282 `b2` int NOT NULL,
283 `c2` int NOT NULL,
284 PRIMARY KEY (`a2`),
285 KEY `xb2c2` (`b2`,`c2`),
286 CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`)
287 ) ENGINE=ndbcluster AUTO_INCREMENT=514 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
288 select type,name from ndb_show_tables_results
289 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
290 type name
291 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
292 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
293 'ForeignKey' 'XX/XX/fkb2c2c1b1'
294 # show counts
295 select count(*) from t1;
296 count(*)
297 6
298 select count(*) from t2;
299 count(*)
300 36
301 # run backup
302 # drop tables
303 drop table t2, t1;
304 # restore meta (disable indexes)
305 # restore data (disable indexes)
306 # show meta
307 select type,name from ndb_show_tables_results
308 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
309 type name
310 # show counts
311 # TEST: rebuild indexes meta
312 drop table t2, t1;
313 create table t1 (
314 a1 int primary key,
315 b1 int not null,
316 c1 int not null,
317 unique key (b1,c1) using hash
318 ) engine ndb;
319 create table t2 (
320 a2 int primary key auto_increment,
321 b2 int not null,
322 c2 int not null,
323 key xb2c2 (b2,c2),
324 constraint fkb2c2c1b1 foreign key (b2,c2) references t1 (c1,b1)
325 ) engine ndb;
326 # show meta
327 show create table t2;
328 Table Create Table
329 t2 CREATE TABLE `t2` (
330 `a2` int NOT NULL AUTO_INCREMENT,
331 `b2` int NOT NULL,
332 `c2` int NOT NULL,
333 PRIMARY KEY (`a2`),
334 KEY `xb2c2` (`b2`,`c2`),
335 CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`)
336 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
337 select type,name from ndb_show_tables_results
338 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
339 type name
340 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
341 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
342 'ForeignKey' 'XX/XX/fkb2c2c1b1'
343 # show counts
344 select count(*) from t1;
345 count(*)
346 0
347 select count(*) from t2;
348 count(*)
349 0
350 # run backup
351 # drop tables
352 drop table t2, t1;
353 # restore meta (disable indexes)
354 # restore data (disable indexes)
355 # rebuild indexes
356 # show meta
357 show create table t2;
358 Table Create Table
359 t2 CREATE TABLE `t2` (
360 `a2` int NOT NULL AUTO_INCREMENT,
361 `b2` int NOT NULL,
362 `c2` int NOT NULL,
363 PRIMARY KEY (`a2`),
364 KEY `xb2c2` (`b2`,`c2`),
365 CONSTRAINT `fkb2c2c1b1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`c1`, `b1`)
366 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
367 select type,name from ndb_show_tables_results
368 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
369 type name
370 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
371 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
372 'ForeignKey' 'XX/XX/fkb2c2c1b1'
373 # show counts
374 select count(*) from t1;
375 count(*)
376 0
377 select count(*) from t2;
378 count(*)
379 0
380 # TEST: rebuild indexes data
381 alter table t2 algorithm=inplace,
382 drop foreign key fkb2c2c1b1;
383 alter table t2 algorithm=inplace,
384 add constraint fkb2c2b1c1 foreign key (b2,c2) references t1 (b1,c1);
385 insert into t1 (a1,b1,c1) values
386 (1,11,12),(2,21,22),(3,31,32),(4,41,42),(5,51,52),(6,61,62);
387 insert into t2 (b2,c2) select x.b1,x.c1 from t1 x,t1 y;
388 insert into t2 (b2,c2) values (62,61);
389 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
390 delete from t1 where b1=61 and c1=62;
391 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
392 # show meta
393 show create table t2;
394 Table Create Table
395 t2 CREATE TABLE `t2` (
396 `a2` int NOT NULL AUTO_INCREMENT,
397 `b2` int NOT NULL,
398 `c2` int NOT NULL,
399 PRIMARY KEY (`a2`),
400 KEY `xb2c2` (`b2`,`c2`),
401 CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`b1`, `c1`)
402 ) ENGINE=ndbcluster AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
403 select type,name from ndb_show_tables_results
404 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
405 type name
406 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
407 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
408 'ForeignKey' 'XX/XX/fkb2c2b1c1'
409 # show counts
410 select count(*) from t1;
411 count(*)
412 6
413 select count(*) from t2;
414 count(*)
415 36
416 # run backup
417 # drop tables
418 drop table t2, t1;
419 # restore meta (disable indexes)
420 # restore data (disable indexes)
421 # rebuild indexes
422 # show meta
423 show create table t2;
424 Table Create Table
425 t2 CREATE TABLE `t2` (
426 `a2` int NOT NULL AUTO_INCREMENT,
427 `b2` int NOT NULL,
428 `c2` int NOT NULL,
429 PRIMARY KEY (`a2`),
430 KEY `xb2c2` (`b2`,`c2`),
431 CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`, `c2`) REFERENCES `t1` (`b1`, `c1`)
432 ) ENGINE=ndbcluster AUTO_INCREMENT=513 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
433 select type,name from ndb_show_tables_results
434 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
435 type name
436 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
437 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
438 'ForeignKey' 'XX/XX/fkb2c2b1c1'
439 # show counts
440 select count(*) from t1;
441 count(*)
442 6
443 select count(*) from t2;
444 count(*)
445 36
446 insert into t2 (b2,c2) values (62,61);
447 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
448 delete from t1 where b1=61 and c1=62;
449 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fkb2c2b1c1` FOREIGN KEY (`b2`,`c2`) REFERENCES `t1` (`b1`,`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
450 drop table t2, t1;
451 #
452 # BUG#18560951 NDB_RESTORE INCLUDES FK FOR ALL DATABASES WHEN USING --INCLUDE-* OR --EXCLUDE
453 #
454 # create database db1 with tables t1,t2
455 create database db1;
456 create table db1.t1 (
457 a1 int primary key
458 ) engine ndb;
459 create table db1.t2 (
460 a1 int,
461 foreign key fk1(a1) references t1(a1)
462 ) engine ndb;
463 # create database db2 with table tb1
464 create database db2;
465 create table db2.t1 (
466 a1 int primary key
467 ) engine ndb;
468 # take backup and drop the tables
469 drop table db1.t2, db1.t1, db2.t1;
470 # now try restoring only the database db2
471 # verify the tables present
472 show create table db2.t1;
473 Table Create Table
474 t1 CREATE TABLE `t1` (
475 `a1` int NOT NULL,
476 PRIMARY KEY (`a1`)
477 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
478 drop table db2.t1;
479 # restore particular table and check for fk problem
480 # verify the tables present
481 show create table db1.t1;
482 Table Create Table
483 t1 CREATE TABLE `t1` (
484 `a1` int NOT NULL,
485 PRIMARY KEY (`a1`)
486 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
487 show create table db2.t1;
488 Table Create Table
489 t1 CREATE TABLE `t1` (
490 `a1` int NOT NULL,
491 PRIMARY KEY (`a1`)
492 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
493 drop table db1.t1, db2.t1;
494 # verify that restoring table with FK whose parent doesn't exist, fails
495 # cleanup
496 drop database db1;
497 drop database db2;
498 # Bug#26974491 FIXES DATA RESTORE WITH DISABLE INDEXES
499 create database db3;
500 use db3;
501 create table t4 (
502 id int primary key auto_increment
503 ) engine=ndb;
504 create table t5 (
505 id int primary key auto_increment,
506 val int,
507 constraint fkt3t2 foreign key (val) references t4 (id)
508 )engine=ndb;
509 insert into t4 values (1), (2), (3), (4), (5);
510 insert into t5 values (1,1), (2,2), (3,3), (4,4), (5,5);
511 # take backup and drop the tables
512 drop table db3.t5, db3.t4;
513 # Create more tables before restore in order to verify that ndb_restore restores foreign keys
514 # appropriately even though the table ids of parent and child tables which make up the name
515 # of the foreign key in the format, <parent_table_id>/<child_table_id>/fk_name are different
516 # in backup files than the table ids restored.
517 create table db3.t6 (
518 id int primary key auto_increment
519 ) engine=ndb;
520 create table db3.t7 (
521 id int primary key auto_increment
522 ) engine=ndb;
523 create table db3.t8 (
524 id int primary key auto_increment
525 ) engine=ndb;
526 create table db3.t9 (
527 id int primary key auto_increment
528 ) engine=ndb;
529 # Use case #1: Meta restore done with --disable-indexes (recommended)
530 # Drop the tables to retore them again with a different use case
531 drop table db3.t5, db3.t4;
532 # Use case #2: Separate --disable-indexes step after metadata restore
533 # Drop the tables to retore them again with a different use case
534 drop table db3.t5, db3.t4;
535 # Use case #3: Data restore with --disable-indexes
536 # Drop the tables to retore them again with a different use case
537 drop table db3.t5, db3.t4;
538 # Use case #4: Data restore with --disable-indexes and Data restore with --rebuild-indexes
539 # Drop the tables to retore them again with a different use case
540 drop table db3.t5, db3.t4;
541 # Use case #5: Both metadata and data restore with both --disable-indexes and --rebuild-indexes
542 # cleanup
543 drop table db3.t6, db3.t7, db3.t8, db3.t9;
544 drop database db3;
545 #
546 # Bug#36345882 Fix column ordering in Dbdict::buildFK_prepare
547 #
548 use test;
549 create table t1 (
550 b1 int as (b) stored not null,
551 c1 int as (c) stored not null,
552 a1 int as (a) stored not null,
553 b2 int as (b) stored not null,
554 c2 int as (c) stored not null,
555 a2 int as (a) stored not null,
556 a int default 0,
557 b int default 0,
558 c int default 0,
559 primary key pk1 (a1,b1,c1) using hash,
560 unique key uk2 (a2,b2,c2) using hash
561 ) engine=ndbcluster;
562 create table t2 (
563 a int default 0,
564 b int default 0,
565 c int default 0,
566 d int default 0,
567 e int default 0,
568 f int default 0,
569 b1 int as (b) stored not null,
570 c1 int as (c) stored not null,
571 a1 int as (a) stored not null,
572 b2 int as (b) stored not null,
573 c2 int as (c) stored not null,
574 a2 int as (a) stored not null,
575 b3 int as (b) stored not null,
576 c3 int as (c) stored not null,
577 a3 int as (a) stored not null,
578 primary key pk1 (a1,b1,c1) using hash,
579 unique key uk2 (a2,b2,c2) using hash,
580 key ok3 (a3,b3,c3),
581 key ok3def (c3,b3,a3,d,e,f),
582 constraint fk11 foreign key (c1,b1,a1) references t1 (c1,b1,a1),
583 constraint fk21 foreign key (c2,b2,a2) references t1 (c1,b1,a1),
584 constraint fk31 foreign key (c3,b3,a3) references t1 (c1,b1,a1),
585 constraint fk12 foreign key (c1,b1,a1) references t1 (c2,b2,a2),
586 constraint fk22 foreign key (c2,b2,a2) references t1 (c2,b2,a2),
587 constraint fk32 foreign key (c3,b3,a3) references t1 (c2,b2,a2)
588 ) engine=ndbcluster;
589 alter table t2 drop index ok3;
590 alter table t2 drop index fk12;
591 alter table t2 drop index fk22;
592 -- t1 --
593 Version: Any
594 Fragment type: HashMapPartition
595 K Value: 6
596 Min load factor: 78
597 Max load factor: 80
598 Temporary table: no
599 Number of attributes: 9
600 Number of primary keys: 3
601 Length of frm data: XXX
602 Max Rows: 0
603 Row Checksum: 1
604 Row GCI: 1
605 SingleUserMode: 0
606 ForceVarPart: 1
607 PartitionCount: 8
608 FragmentCount: 8
609 PartitionBalance: FOR_RP_BY_LDM
610 ExtraRowGciBits: 0
611 ExtraRowAuthorBits: 0
612 TableStatus: Retrieved
613 Table options: readbackup
614 HashMap: DEFAULT-HASHMAP-3840-8
615 -- Attributes --
616 b1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
617 c1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
618 a1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
619 b2 Int NOT NULL AT=FIXED ST=MEMORY
620 c2 Int NOT NULL AT=FIXED ST=MEMORY
621 a2 Int NOT NULL AT=FIXED ST=MEMORY
622 a Int NULL AT=FIXED ST=MEMORY DEFAULT 0
623 b Int NULL AT=FIXED ST=MEMORY DEFAULT 0
624 c Int NULL AT=FIXED ST=MEMORY DEFAULT 0
625 -- Indexes --
626 PRIMARY KEY(b1, c1, a1) - UniqueHashIndex
627 uk2$unique(b2, c2, a2) - UniqueHashIndex
628 -- t2 --
629 Version: Any
630 Fragment type: HashMapPartition
631 K Value: 6
632 Min load factor: 78
633 Max load factor: 80
634 Temporary table: no
635 Number of attributes: 15
636 Number of primary keys: 3
637 Length of frm data: XXX
638 Max Rows: 0
639 Row Checksum: 1
640 Row GCI: 1
641 SingleUserMode: 0
642 ForceVarPart: 1
643 PartitionCount: 8
644 FragmentCount: 8
645 PartitionBalance: FOR_RP_BY_LDM
646 ExtraRowGciBits: 0
647 ExtraRowAuthorBits: 0
648 TableStatus: Retrieved
649 Table options: readbackup
650 HashMap: DEFAULT-HASHMAP-3840-8
651 -- Attributes --
652 a Int NULL AT=FIXED ST=MEMORY DEFAULT 0
653 b Int NULL AT=FIXED ST=MEMORY DEFAULT 0
654 c Int NULL AT=FIXED ST=MEMORY DEFAULT 0
655 d Int NULL AT=FIXED ST=MEMORY DEFAULT 0
656 e Int NULL AT=FIXED ST=MEMORY DEFAULT 0
657 f Int NULL AT=FIXED ST=MEMORY DEFAULT 0
658 b1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
659 c1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
660 a1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
661 b2 Int NOT NULL AT=FIXED ST=MEMORY
662 c2 Int NOT NULL AT=FIXED ST=MEMORY
663 a2 Int NOT NULL AT=FIXED ST=MEMORY
664 b3 Int NOT NULL AT=FIXED ST=MEMORY
665 c3 Int NOT NULL AT=FIXED ST=MEMORY
666 a3 Int NOT NULL AT=FIXED ST=MEMORY
667 -- Indexes --
668 PRIMARY KEY(b1, c1, a1) - UniqueHashIndex
669 uk2$unique(b2, c2, a2) - UniqueHashIndex
670 ok3def(c3, b3, a3, d, e, f) - OrderedIndex
671 -- ForeignKeys --
672 XX/XX/fk11 PRIMARY KEY (c1, b1, a1) REFERENCES test.t1/PRIMARY KEY () on update noaction on delete noaction
673 XX/XX/fk21 uk2$unique (c2, b2, a2) REFERENCES test.t1/PRIMARY KEY () on update noaction on delete noaction
674 XX/XX/fk31 ok3def (c3, b3, a3) REFERENCES test.t1/PRIMARY KEY () on update noaction on delete noaction
675 XX/XX/fk12 PRIMARY KEY (c1, b1, a1) REFERENCES test.t1/uk2$unique () on update noaction on delete noaction
676 XX/XX/fk22 uk2$unique (c2, b2, a2) REFERENCES test.t1/uk2$unique () on update noaction on delete noaction
677 XX/XX/fk32 ok3def (c3, b3, a3) REFERENCES test.t1/uk2$unique () on update noaction on delete noaction
678 insert into t1 (a, b, c) values (11, 12, 13);
679 insert into t2 (a, b, c) values (11, 12, 13);
680 insert into t2 (a,b,c) values (101, 102, 103);
681 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk11` FOREIGN KEY (`c1`,`b1`,`a1`) REFERENCES `t1` (`c1`,`b1`,`a1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
682 # show meta
683 show create table t2;
684 Table Create Table
685 t2 CREATE TABLE `t2` (
686 `a` int DEFAULT '0',
687 `b` int DEFAULT '0',
688 `c` int DEFAULT '0',
689 `d` int DEFAULT '0',
690 `e` int DEFAULT '0',
691 `f` int DEFAULT '0',
692 `b1` int GENERATED ALWAYS AS (`b`) STORED NOT NULL,
693 `c1` int GENERATED ALWAYS AS (`c`) STORED NOT NULL,
694 `a1` int GENERATED ALWAYS AS (`a`) STORED NOT NULL,
695 `b2` int GENERATED ALWAYS AS (`b`) STORED NOT NULL,
696 `c2` int GENERATED ALWAYS AS (`c`) STORED NOT NULL,
697 `a2` int GENERATED ALWAYS AS (`a`) STORED NOT NULL,
698 `b3` int GENERATED ALWAYS AS (`b`) STORED NOT NULL,
699 `c3` int GENERATED ALWAYS AS (`c`) STORED NOT NULL,
700 `a3` int GENERATED ALWAYS AS (`a`) STORED NOT NULL,
701 PRIMARY KEY (`a1`,`b1`,`c1`) USING HASH,
702 UNIQUE KEY `uk2` (`a2`,`b2`,`c2`) USING HASH,
703 KEY `ok3def` (`c3`,`b3`,`a3`,`d`,`e`,`f`),
704 CONSTRAINT `fk11` FOREIGN KEY (`c1`, `b1`, `a1`) REFERENCES `t1` (`c1`, `b1`, `a1`),
705 CONSTRAINT `fk12` FOREIGN KEY (`c1`, `b1`, `a1`) REFERENCES `t1` (`c2`, `b2`, `a2`),
706 CONSTRAINT `fk21` FOREIGN KEY (`c2`, `b2`, `a2`) REFERENCES `t1` (`c1`, `b1`, `a1`),
707 CONSTRAINT `fk22` FOREIGN KEY (`c2`, `b2`, `a2`) REFERENCES `t1` (`c2`, `b2`, `a2`),
708 CONSTRAINT `fk31` FOREIGN KEY (`c3`, `b3`, `a3`) REFERENCES `t1` (`c1`, `b1`, `a1`),
709 CONSTRAINT `fk32` FOREIGN KEY (`c3`, `b3`, `a3`) REFERENCES `t1` (`c2`, `b2`, `a2`)
710 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
711 select type,name from ndb_show_tables_results
712 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
713 type name
714 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
715 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
716 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
717 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
718 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
719 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
720 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
721 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
722 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
723 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
724 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
725 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
726 'ForeignKey' 'XX/XX/fk11'
727 'ForeignKey' 'XX/XX/fk12'
728 'ForeignKey' 'XX/XX/fk21'
729 'ForeignKey' 'XX/XX/fk22'
730 'ForeignKey' 'XX/XX/fk31'
731 'ForeignKey' 'XX/XX/fk32'
732 # show counts
733 select count(*) from t1;
734 count(*)
735 1
736 select count(*) from t2;
737 count(*)
738 1
739 # run backup
740 # drop tables
741 drop table t2, t1;
742 # restore meta (disable indexes)
743 # restore data (disable indexes)
744 # rebuild indexes
745 # show meta
746 show create table t2;
747 Table Create Table
748 t2 CREATE TABLE `t2` (
749 `a` int DEFAULT '0',
750 `b` int DEFAULT '0',
751 `c` int DEFAULT '0',
752 `d` int DEFAULT '0',
753 `e` int DEFAULT '0',
754 `f` int DEFAULT '0',
755 `b1` int GENERATED ALWAYS AS (`b`) STORED NOT NULL,
756 `c1` int GENERATED ALWAYS AS (`c`) STORED NOT NULL,
757 `a1` int GENERATED ALWAYS AS (`a`) STORED NOT NULL,
758 `b2` int GENERATED ALWAYS AS (`b`) STORED NOT NULL,
759 `c2` int GENERATED ALWAYS AS (`c`) STORED NOT NULL,
760 `a2` int GENERATED ALWAYS AS (`a`) STORED NOT NULL,
761 `b3` int GENERATED ALWAYS AS (`b`) STORED NOT NULL,
762 `c3` int GENERATED ALWAYS AS (`c`) STORED NOT NULL,
763 `a3` int GENERATED ALWAYS AS (`a`) STORED NOT NULL,
764 PRIMARY KEY (`a1`,`b1`,`c1`) USING HASH,
765 UNIQUE KEY `uk2` (`a2`,`b2`,`c2`) USING HASH,
766 KEY `ok3def` (`c3`,`b3`,`a3`,`d`,`e`,`f`),
767 CONSTRAINT `fk11` FOREIGN KEY (`c1`, `b1`, `a1`) REFERENCES `t1` (`c1`, `b1`, `a1`),
768 CONSTRAINT `fk12` FOREIGN KEY (`c1`, `b1`, `a1`) REFERENCES `t1` (`c2`, `b2`, `a2`),
769 CONSTRAINT `fk21` FOREIGN KEY (`c2`, `b2`, `a2`) REFERENCES `t1` (`c1`, `b1`, `a1`),
770 CONSTRAINT `fk22` FOREIGN KEY (`c2`, `b2`, `a2`) REFERENCES `t1` (`c2`, `b2`, `a2`),
771 CONSTRAINT `fk31` FOREIGN KEY (`c3`, `b3`, `a3`) REFERENCES `t1` (`c1`, `b1`, `a1`),
772 CONSTRAINT `fk32` FOREIGN KEY (`c3`, `b3`, `a3`) REFERENCES `t1` (`c2`, `b2`, `a2`)
773 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
774 select type,name from ndb_show_tables_results
775 where type in ('''ForeignKey''','''FKParentTrigger''','''FKChildTrigger''');
776 type name
777 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
778 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
779 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
780 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
781 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
782 'FKChildTrigger' 'NDB$FK_XX_CHILD_XX'
783 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
784 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
785 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
786 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
787 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
788 'FKParentTrigger' 'NDB$FK_XX_PARENT_XX'
789 'ForeignKey' 'XX/XX/fk11'
790 'ForeignKey' 'XX/XX/fk12'
791 'ForeignKey' 'XX/XX/fk21'
792 'ForeignKey' 'XX/XX/fk22'
793 'ForeignKey' 'XX/XX/fk31'
794 'ForeignKey' 'XX/XX/fk32'
795 # show counts
796 select count(*) from t1;
797 count(*)
798 1
799 select count(*) from t2;
800 count(*)
801 1
802 select * from t1;
803 b1 c1 a1 b2 c2 a2 a b c
804 12 13 11 12 13 11 11 12 13
805 select * from t2;
806 a b c d e f b1 c1 a1 b2 c2 a2 b3 c3 a3
807 11 12 13 0 0 0 12 13 11 12 13 11 12 13 11
808 drop table t2,t1;
809 Show effect of --skip-fk-checks ndb_restore option
810 create table parent (
811 a int primary key,
812 b int) engine=ndb;
813 create table child (
814 x int primary key,
815 b int,
816 c int,
817 foreign key(b) references parent(a)) engine=ndb;
818 insert into parent values (1,1), (2,2), (3,3), (4,4);
819 insert into child values (1,1,1), (2,2,2), (3,3,3), (4,4,4);
820 Go to the dark side of MySQL
821 set foreign_key_checks=0;
822 insert into child values (5,5,5);
823 delete from parent where a=4;
824 set foreign_key_checks=1;
825 select * from parent order by a;
826 a b
827 1 1
828 2 2
829 3 3
830 select * from child order by x;
831 x b c
832 1 1 1
833 2 2 2
834 3 3 3
835 4 4 4
836 5 5 5
837 # take backup and drop the tables
838 drop table child;
839 drop table parent;
840 # Now try restoring
841 # Restore schema, with indexes disabled
842 # Restore data - should succeed
843 # Rebuild indexes + constraints - will fail
844 # Show that we can get round this by skipping the whole child table
845 # Disable so that we can try again
846 # Try again with skip-fk-checks
847 Check data
848 select * from parent order by a;
849 a b
850 1 1
851 2 2
852 3 3
853 select * from child order by x;
854 x b c
855 1 1 1
856 2 2 2
857 3 3 3
858 4 4 4
859 5 5 5
860 Show constraint active
861 insert into child values (10,10,10);
862 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_fk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
863 delete from parent where a=1;
864 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_fk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
865 Try adding duplicate FK - will fail due
866 to inconsistencies
867 ALTER TABLE child ADD FOREIGN KEY(b) references parent(a);
868 ERROR HY000: Cannot add foreign key constraint
869 Fix data
870 insert into parent values (4,4), (5,5);
871 select * from parent order by a;
872 a b
873 1 1
874 2 2
875 3 3
876 4 4
877 5 5
878 select * from child order by x;
879 x b c
880 1 1 1
881 2 2 2
882 3 3 3
883 4 4 4
884 5 5 5
885 Try adding duplicate FK again
886 ALTER TABLE child ADD FOREIGN KEY(b) references parent(a);
887 Cleanup
888 drop table child;
889 drop table parent;
890 Show that --restore-meta with --skip-fk-checks behaves the same as --restore-meta
891 select * from parent;
892 a b
893 select * from child;
894 x b c
895 insert into parent values (1,1);
896 insert into child values (1,1,1);
897 Show constraint active
898 insert into child values (10,10,10);
899 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_fk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
900 delete from parent where a=1;
901 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_fk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
902 Cleanup
903 delete from child;
904 delete from parent;
905 drop table child;
906 drop table parent;