Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2 -- source suite/ndb/include/backup_restore_setup.inc
3
4 # bug#16285826 - NDB_RESTORE -M DOES NOT RESTORE FK CONSTRAINTS
5 -- echo # bug#16285826
6
7 create table t1 (
8 a1 int primary key
9 ) engine ndb;
10
11 insert into t1 (a1) values
12 (1),(2),(3),(4),(5),(6),(7),(8),(9);
13
14 create table t2 (
15 a2 int primary key auto_increment,
16 b2 int not null,
17 key xb2 (b2),
18 constraint fkb2a1 foreign key (b2) references t1 (a1)
19 ) engine ndb;
20
21 -- echo # TEST: basic meta
22
23 -- source ndb_fk_restore.inc
24
25 -- echo # TEST: basic data
26
27 insert into t2 (b2) select x.a1 from t1 x,t1 y;
28 -- error ER_NO_REFERENCED_ROW_2
29 insert into t2 (b2) values (10);
30 -- error ER_ROW_IS_REFERENCED_2
31 delete from t1 where a1 = 1;
32
33 # t1 fragment may be restored before t2 fragment but in MTR
34 # there are two fragments so likely FK disable is required
35
36 -- source ndb_fk_restore.inc
37
38 -- error ER_NO_REFERENCED_ROW_2
39 insert into t2 (b2) values (10);
40 -- error ER_ROW_IS_REFERENCED_2
41 delete from t1 where a1 = 1;
42
43 -- echo # TEST: child part key
44
45 drop table t2;
46
47 create table t2 (
48 a2 int primary key auto_increment,
49 b2 int not null,
50 c2 int not null,
51 key xb2c2 (b2,c2),
52 constraint fkb2a1 foreign key (b2) references t1 (a1)
53 ) engine ndb;
54
55 insert into t2 (b2,c2) select x.a1,99 from t1 x,t1 y;
56 -- error ER_NO_REFERENCED_ROW_2
57 insert into t2 (b2,c2) values (10,99);
58 -- error ER_ROW_IS_REFERENCED_2
59 delete from t1 where a1 = 1;
60
61 -- source ndb_fk_restore.inc
62
63 -- error ER_NO_REFERENCED_ROW_2
64 insert into t2 (b2,c2) values (10,99);
65 -- error ER_ROW_IS_REFERENCED_2
66 delete from t1 where a1 = 1;
67
68 -- echo # TEST: column order
69
70 drop table t2,t1;
71
72 create table t1 (
73 a1 int primary key,
74 b1 int not null,
75 c1 int not null,
76 unique key (b1,c1) using hash
77 ) engine ndb;
78
79 insert into t1 (a1,b1,c1) values
80 (1,11,12),(2,21,22),(3,31,32),(4,41,42),(5,51,52),(6,61,62);
81
82 create table t2 (
83 a2 int primary key auto_increment,
84 b2 int not null,
85 c2 int not null,
86 key xb2c2 (b2,c2),
87 constraint fkb2c2c1b1 foreign key (b2,c2) references t1 (c1,b1)
88 ) engine ndb;
89
90 insert into t2 (b2,c2) select x.c1,x.b1 from t1 x,t1 y;
91 -- error ER_NO_REFERENCED_ROW_2
92 insert into t2 (b2,c2) values (61,62);
93 -- error ER_ROW_IS_REFERENCED_2
94 delete from t1 where b1=61 and c1=62;
95
96 -- source ndb_fk_restore.inc
97
98 -- error ER_NO_REFERENCED_ROW_2
99 insert into t2 (b2,c2) values (61,62);
100 -- error ER_ROW_IS_REFERENCED_2
101 delete from t1 where b1=61 and c1=62;
102
103 -- echo # TEST: disable indexes
104
105 -- let $disable_indexes=1
106 -- source ndb_fk_restore.inc
107
108 -- echo # TEST: rebuild indexes meta
109
110 # previous tables are unusable via mysql
111 drop table t2, t1;
112
113 create table t1 (
114 a1 int primary key,
115 b1 int not null,
116 c1 int not null,
117 unique key (b1,c1) using hash
118 ) engine ndb;
119
120 create table t2 (
121 a2 int primary key auto_increment,
122 b2 int not null,
123 c2 int not null,
124 key xb2c2 (b2,c2),
125 constraint fkb2c2c1b1 foreign key (b2,c2) references t1 (c1,b1)
126 ) engine ndb;
127
128 -- let $rebuild_indexes=1
129 -- source ndb_fk_restore.inc
130
131 -- echo # TEST: rebuild indexes data
132
133 # there may be a bug with add FK and reversed column order
134
135 alter table t2 algorithm=inplace,
136 drop foreign key fkb2c2c1b1;
137 alter table t2 algorithm=inplace,
138 add constraint fkb2c2b1c1 foreign key (b2,c2) references t1 (b1,c1);
139
140 insert into t1 (a1,b1,c1) values
141 (1,11,12),(2,21,22),(3,31,32),(4,41,42),(5,51,52),(6,61,62);
142
143 insert into t2 (b2,c2) select x.b1,x.c1 from t1 x,t1 y;
144 -- error ER_NO_REFERENCED_ROW_2
145 insert into t2 (b2,c2) values (62,61);
146 -- error ER_ROW_IS_REFERENCED_2
147 delete from t1 where b1=61 and c1=62;
148
149 -- let $rebuild_indexes=1
150 -- source ndb_fk_restore.inc
151
152 -- error ER_NO_REFERENCED_ROW_2
153 insert into t2 (b2,c2) values (62,61);
154 -- error ER_ROW_IS_REFERENCED_2
155 delete from t1 where b1=61 and c1=62;
156
157 drop table t2, t1;
158
159 -- echo #
160 -- echo # BUG#18560951 NDB_RESTORE INCLUDES FK FOR ALL DATABASES WHEN USING --INCLUDE-* OR --EXCLUDE
161 -- echo #
162
163 -- echo # create database db1 with tables t1,t2
164 create database db1;
165
166 create table db1.t1 (
167 a1 int primary key
168 ) engine ndb;
169
170 create table db1.t2 (
171 a1 int,
172 foreign key fk1(a1) references t1(a1)
173 ) engine ndb;
174
175 -- echo # create database db2 with table tb1
176
177 create database db2;
178
179 create table db2.t1 (
180 a1 int primary key
181 ) engine ndb;
182
183 -- echo # take backup and drop the tables
184 --source include/ndb_backup.inc
185 drop table db1.t2, db1.t1, db2.t1;
186
187 -- echo # now try restoring only the database db2
188 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --include-databases=db2 --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
189
190 -- echo # verify the tables present
191 --disable_warnings
192 show create table db2.t1;
193 --enable_warnings
194 drop table db2.t1;
195
196 -- echo # restore particular table and check for fk problem
197 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --include-tables=db1.t1,db2.t1 --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
198
199 -- echo # verify the tables present
200 --disable_warnings
201 show create table db1.t1;
202 show create table db2.t1;
203 --enable_warnings
204 drop table db1.t1, db2.t1;
205
206 --echo # verify that restoring table with FK whose parent doesn't exist, fails
207 --error 1
208 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --include-tables=db1.t2 --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
209
210 --echo # cleanup
211 drop database db1;
212 drop database db2;
213
214 --echo # Bug#26974491 FIXES DATA RESTORE WITH DISABLE INDEXES
215 create database db3;
216 use db3;
217 create table t4 (
218 id int primary key auto_increment
219 ) engine=ndb;
220 create table t5 (
221 id int primary key auto_increment,
222 val int,
223 constraint fkt3t2 foreign key (val) references t4 (id)
224 )engine=ndb;
225 insert into t4 values (1), (2), (3), (4), (5);
226 insert into t5 values (1,1), (2,2), (3,3), (4,4), (5,5);
227
228 -- echo # take backup and drop the tables
229 -- source include/ndb_backup.inc
230 drop table db3.t5, db3.t4;
231
232 -- echo # Create more tables before restore in order to verify that ndb_restore restores foreign keys
233 -- echo # appropriately even though the table ids of parent and child tables which make up the name
234 -- echo # of the foreign key in the format, <parent_table_id>/<child_table_id>/fk_name are different
235 -- echo # in backup files than the table ids restored.
236 create table db3.t6 (
237 id int primary key auto_increment
238 ) engine=ndb;
239 create table db3.t7 (
240 id int primary key auto_increment
241 ) engine=ndb;
242 create table db3.t8 (
243 id int primary key auto_increment
244 ) engine=ndb;
245 create table db3.t9 (
246 id int primary key auto_increment
247 ) engine=ndb;
248
249 -- echo # Use case #1: Meta restore done with --disable-indexes (recommended)
250 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --restore-meta --print_meta --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
251 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --restore-data $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
252 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 2 --restore-data $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
253 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 2 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
254
255 -- echo # Drop the tables to retore them again with a different use case
256 drop table db3.t5, db3.t4;
257
258 -- echo # Use case #2: Separate --disable-indexes step after metadata restore
259 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --restore-meta --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
260 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
261 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --restore-data --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
262 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 2 --restore-data --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
263 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 2 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
264
265 -- echo # Drop the tables to retore them again with a different use case
266 drop table db3.t5, db3.t4;
267
268 -- echo # Use case #3: Data restore with --disable-indexes
269 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --restore-meta --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
270 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --restore-data --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
271 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 2 --restore-data --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
272 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 2 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
273
274 -- echo # Drop the tables to retore them again with a different use case
275 drop table db3.t5, db3.t4;
276
277 -- echo # Use case #4: Data restore with --disable-indexes and Data restore with --rebuild-indexes
278 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --restore-meta --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
279 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --restore-data --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
280 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 2 --restore-data --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
281
282 -- echo # Drop the tables to retore them again with a different use case
283 drop table db3.t5, db3.t4;
284
285 -- echo # Use case #5: Both metadata and data restore with both --disable-indexes and --rebuild-indexes
286 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 1 --restore-meta --restore-data --disable-indexes --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
287 --exec $NDB_RESTORE --show-part-id -b $the_backup_id -n 2 --restore-data $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
288
289 --echo # cleanup
290 drop table db3.t6, db3.t7, db3.t8, db3.t9;
291 drop database db3;
292
293 -- echo #
294 -- echo # Bug#36345882 Fix column ordering in Dbdict::buildFK_prepare
295 -- echo #
296
297 # Check ndb_restore rebuild indexes for multi column foreign key constraints.
298
299 use test;
300
301 let $using_hash=using hash;
302
303 eval create table t1 (
304 b1 int as (b) stored not null,
305 c1 int as (c) stored not null,
306 a1 int as (a) stored not null,
307 b2 int as (b) stored not null,
308 c2 int as (c) stored not null,
309 a2 int as (a) stored not null,
310 a int default 0,
311 b int default 0,
312 c int default 0,
313 primary key pk1 (a1,b1,c1) $using_hash,
314 unique key uk2 (a2,b2,c2) $using_hash
315 ) engine=ndbcluster;
316
317 eval create table t2 (
318 a int default 0,
319 b int default 0,
320 c int default 0,
321 d int default 0,
322 e int default 0,
323 f int default 0,
324 b1 int as (b) stored not null,
325 c1 int as (c) stored not null,
326 a1 int as (a) stored not null,
327 b2 int as (b) stored not null,
328 c2 int as (c) stored not null,
329 a2 int as (a) stored not null,
330 b3 int as (b) stored not null,
331 c3 int as (c) stored not null,
332 a3 int as (a) stored not null,
333 primary key pk1 (a1,b1,c1) $using_hash,
334 unique key uk2 (a2,b2,c2) $using_hash,
335 key ok3 (a3,b3,c3),
336 key ok3def (c3,b3,a3,d,e,f),
337 constraint fk11 foreign key (c1,b1,a1) references t1 (c1,b1,a1),
338 constraint fk21 foreign key (c2,b2,a2) references t1 (c1,b1,a1),
339 constraint fk31 foreign key (c3,b3,a3) references t1 (c1,b1,a1),
340 constraint fk12 foreign key (c1,b1,a1) references t1 (c2,b2,a2),
341 constraint fk22 foreign key (c2,b2,a2) references t1 (c2,b2,a2),
342 constraint fk32 foreign key (c3,b3,a3) references t1 (c2,b2,a2)
343 ) engine=ndbcluster;
344
345 # ok3 index could not be used for fk31 and fk32 since column order did not
346 # match. Instead ok3def is used since it has a matching prefix.
347 alter table t2 drop index ok3;
348 # pk1 is used for constraint fk12 instead of implicitly created fk12.
349 alter table t2 drop index fk12;
350 # uk2 is used for constraint fk22 instead of implicitly created fk22.
351 alter table t2 drop index fk22;
352
353 let $ndb_desc_opts= -d test t1 t2;
354 source suite/ndb/include/ndb_desc_print.inc;
355
356 # Add parent row, use different values for the different columns.
357 insert into t1 (a, b, c) values (11, 12, 13);
358 # Add valid child row.
359 insert into t2 (a, b, c) values (11, 12, 13);
360
361 --error ER_NO_REFERENCED_ROW_2
362 insert into t2 (a,b,c) values (101, 102, 103);
363
364 let $rebuild_indexes= 1;
365 source ndb_fk_restore.inc;
366
367 select * from t1;
368 select * from t2;
369
370 drop table t2,t1;
371
372 --echo Show effect of --skip-fk-checks ndb_restore option
373
374 create table parent (
375 a int primary key,
376 b int) engine=ndb;
377
378 create table child (
379 x int primary key,
380 b int,
381 c int,
382 foreign key(b) references parent(a)) engine=ndb;
383
384 insert into parent values (1,1), (2,2), (3,3), (4,4);
385 insert into child values (1,1,1), (2,2,2), (3,3,3), (4,4,4);
386
387 --echo Go to the dark side of MySQL
388 set foreign_key_checks=0;
389 insert into child values (5,5,5);
390 delete from parent where a=4;
391 set foreign_key_checks=1;
392
393 select * from parent order by a;
394 select * from child order by x;
395
396 -- echo # take backup and drop the tables
397 --source include/ndb_backup.inc
398 drop table child;
399 drop table parent;
400
401 --echo # Now try restoring
402 --echo # Restore schema, with indexes disabled
403 --exec $NDB_RESTORE -b $the_backup_id -n 1 --restore-meta --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
404
405 --echo # Restore data - should succeed
406 --exec $NDB_RESTORE -b $the_backup_id -n 1 --restore-data $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
407 --exec $NDB_RESTORE -b $the_backup_id -n 2 --restore-data $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
408
409 --echo # Rebuild indexes + constraints - will fail
410 --error 1
411 --exec $NDB_RESTORE -b $the_backup_id -n 1 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
412
413 --echo # Show that we can get round this by skipping the whole child table
414 --exec $NDB_RESTORE -b $the_backup_id -n 1 --rebuild-indexes --exclude-tables=test.child $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
415
416 # Restore succeeded, but now we are missing indexes etc
417 # Could be used manually to fix things
418
419 --echo # Disable so that we can try again
420 --exec $NDB_RESTORE -b $the_backup_id -n 1 --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
421
422 --echo # Try again with skip-fk-checks
423 --exec $NDB_RESTORE -b $the_backup_id -n 1 --rebuild-indexes --skip-fk-checks $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
424
425 # Restore succeeded, and FK constraints
426 # are in place
427
428 --echo Check data
429 select * from parent order by a;
430 select * from child order by x;
431
432 --echo Show constraint active
433 --error ER_NO_REFERENCED_ROW_2
434 insert into child values (10,10,10);
435 --error ER_ROW_IS_REFERENCED_2
436 delete from parent where a=1;
437
438 --echo Try adding duplicate FK - will fail due
439 --echo to inconsistencies
440 --error ER_CANNOT_ADD_FOREIGN
441 ALTER TABLE child ADD FOREIGN KEY(b) references parent(a);
442
443 --echo Fix data
444 insert into parent values (4,4), (5,5);
445
446 select * from parent order by a;
447 select * from child order by x;
448
449 --echo Try adding duplicate FK again
450 ALTER TABLE child ADD FOREIGN KEY(b) references parent(a);
451
452 --echo Cleanup
453 drop table child;
454 drop table parent;
455
456 --echo Show that --restore-meta with --skip-fk-checks behaves the same as --restore-meta
457 --exec $NDB_RESTORE -b $the_backup_id -n 1 --restore-meta --skip-fk-checks $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
458
459 select * from parent;
460 select * from child;
461
462 insert into parent values (1,1);
463 insert into child values (1,1,1);
464
465 --echo Show constraint active
466 --error ER_NO_REFERENCED_ROW_2
467 insert into child values (10,10,10);
468 --error ER_ROW_IS_REFERENCED_2
469 delete from parent where a=1;
470
471 --echo Cleanup
472 delete from child;
473 delete from parent;
474
475 drop table child;
476 drop table parent;
477
478 --source suite/ndb/include/backup_restore_cleanup.inc
479 --remove_file $NDB_TOOLS_OUTPUT