Debian Codegraph @

mysql-test/suite/versioning/t/foreign.test

raw
1 --source suite/versioning/key_type.inc
2 --source suite/versioning/common.inc
3
4 --echo #################
5 --echo # Test RESTRICT #
6 --echo #################
7
8 --replace_result "$KEY_TYPE" KEY_TYPE
9 eval create table parent(
10 id int,
11 $KEY_TYPE (id)
12 ) engine innodb;
13
14 --replace_result $sys_datatype_expl SYS_DATATYPE
15 eval create table child(
16 parent_id int,
17 sys_start $sys_datatype_expl as row start invisible,
18 sys_end $sys_datatype_expl as row end invisible,
19 period for system_time(sys_start, sys_end),
20 foreign key(parent_id) references parent(id)
21 on delete restrict
22 on update restrict
23 ) engine innodb with system versioning;
24
25 insert into parent values(1);
26 insert into child values(1);
27
28 -- error ER_ROW_IS_REFERENCED_2
29 delete from parent where id = 1;
30 delete from child where parent_id = 1;
31 delete from parent where id = 1;
32
33 insert into parent values(1);
34 insert into child values(1);
35 -- error ER_ROW_IS_REFERENCED_2
36 update parent set id=id+1;
37 delete from child;
38 update parent set id=id+1;
39 select * from child for system_time all;
40
41 drop table child;
42 drop table parent;
43
44 --echo ##############################################
45 --echo # Test when clustered index is a foreign key #
46 --echo ##############################################
47
48 --replace_result "$KEY_TYPE" KEY_TYPE
49 eval create table parent(
50 id int(10) unsigned,
51 $KEY_TYPE (id)
52 ) engine innodb;
53
54 --replace_result $sys_datatype_expl SYS_DATATYPE
55 eval create table child(
56 parent_id int(10) unsigned primary key,
57 sys_start $sys_datatype_expl as row start invisible,
58 sys_end $sys_datatype_expl as row end invisible,
59 period for system_time(sys_start, sys_end),
60 foreign key(parent_id) references parent(id)
61 ) engine innodb with system versioning;
62
63 insert into parent values(1);
64 insert into child values(1);
65
66 -- error ER_ROW_IS_REFERENCED_2
67 delete from parent where id = 1;
68
69 drop table child;
70 drop table parent;
71
72 --echo ################
73 --echo # Test CASCADE #
74 --echo ################
75
76 --replace_result "$KEY_TYPE" KEY_TYPE
77 eval create table parent(
78 id int,
79 $KEY_TYPE (id)
80 ) engine innodb;
81
82 --replace_result $sys_datatype_expl SYS_DATATYPE
83 eval create table child(
84 parent_id int,
85 sys_start $sys_datatype_expl as row start invisible,
86 sys_end $sys_datatype_expl as row end invisible,
87 period for system_time(sys_start, sys_end),
88 foreign key(parent_id) references parent(id)
89 on delete cascade
90 on update cascade
91 ) engine innodb with system versioning;
92
93 insert into parent values(1);
94 insert into child values(1);
95
96 delete from parent where id = 1;
97 select * from child;
98 select * from child for system_time all;
99
100 insert into parent values(1);
101 insert into child values(1);
102 update parent set id = id + 1;
103 select * from child;
104 select * from child for system_time all;
105
106 drop table child;
107 drop table parent;
108
109 --replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE
110 eval create or replace table parent (
111 id int,
112 $KEY_TYPE(id),
113 sys_start $sys_datatype_expl as row start invisible,
114 sys_end $sys_datatype_expl as row end invisible,
115 period for system_time(sys_start, sys_end)
116 ) with system versioning
117 engine innodb;
118
119 create or replace table child (
120 x int,
121 parent_id int not null,
122 constraint `parent-fk`
123 foreign key (parent_id) references parent (id)
124 on delete cascade
125 on update restrict
126 )
127 engine innodb;
128
129 insert into parent (id) values (2);
130 insert into child (x, parent_id) values (2, 2);
131 delete from parent;
132 select * from child;
133
134 drop table child;
135 drop table parent;
136
137 --replace_result "$KEY_TYPE" KEY_TYPE
138 eval create or replace table parent (
139 id int,
140 $KEY_TYPE(id)
141 )
142 engine innodb;
143
144 --replace_result $sys_datatype_expl SYS_DATATYPE
145 eval create or replace table child (
146 id int primary key,
147 parent_id int not null,
148 row_start $sys_datatype_expl as row start invisible,
149 row_end $sys_datatype_expl as row end invisible,
150 period for system_time(row_start, row_end),
151 constraint `parent-fk`
152 foreign key (parent_id) references parent (id)
153 on delete cascade
154 on update restrict
155 ) with system versioning
156 engine innodb;
157
158 insert into parent (id) values (3);
159 insert into child (id, parent_id) values (3, 3);
160 delete from parent;
161 select * from child;
162 select *, check_row(row_start, row_end) from child for system_time all;
163
164 drop table child;
165 drop table parent;
166
167 --echo #################
168 --echo # Test SET NULL #
169 --echo #################
170
171 --replace_result "$KEY_TYPE" KEY_TYPE
172 eval create table parent(
173 id int,
174 $KEY_TYPE (id)
175 ) engine innodb;
176
177 --replace_result $sys_datatype_expl SYS_DATATYPE
178 eval create or replace table child(
179 parent_id int,
180 sys_start $sys_datatype_expl as row start invisible,
181 sys_end $sys_datatype_expl as row end invisible,
182 period for system_time(sys_start, sys_end),
183 foreign key(parent_id) references parent(id)
184 on delete set null
185 on update set null
186 ) engine innodb with system versioning;
187
188 insert into parent values(1);
189 insert into child values(1);
190 delete from child;
191 insert into child values(1);
192
193 delete from parent where id = 1;
194 select * from child;
195 select *, current_row(sys_end) as current_row from child for system_time all order by sys_end;
196 delete from child;
197
198 insert into parent values(1);
199 insert into child values(1);
200 update parent set id= id + 1;
201 select * from child;
202 select *, current_row(sys_end) as current_row from child for system_time all order by sys_end;
203
204 drop table child;
205 drop table parent;
206
207 --echo ###########################
208 --echo # Parent table is foreign #
209 --echo ###########################
210
211 --replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE
212 eval create or replace table parent(
213 id int,
214 $KEY_TYPE (id),
215 sys_start $sys_datatype_expl as row start invisible,
216 sys_end $sys_datatype_expl as row end invisible,
217 period for system_time(sys_start, sys_end)
218 ) engine innodb with system versioning;
219
220 create or replace table child(
221 parent_id int,
222 foreign key(parent_id) references parent(id)
223 ) engine innodb;
224
225 insert into parent values(1);
226 insert into child values(1);
227
228 -- error ER_ROW_IS_REFERENCED_2
229 delete from parent;
230 -- error ER_ROW_IS_REFERENCED_2
231 update parent set id=2;
232
233 delete from child;
234 delete from parent;
235
236 -- error ER_NO_REFERENCED_ROW_2
237 insert into child values(1);
238
239 insert into parent values(1);
240 insert into child values(1);
241 -- error ER_ROW_IS_REFERENCED_2
242 delete from parent;
243 -- error ER_ROW_IS_REFERENCED_2
244 update parent set id=2;
245
246 drop table child;
247 drop table parent;
248
249 --echo ###################
250 --echo # crash on DELETE #
251 --echo ###################
252
253 --replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE
254 eval create or replace table a (
255 cola int(10),
256 $KEY_TYPE (cola),
257 v_cola int(10) as (cola mod 10) virtual,
258 sys_start $sys_datatype_expl as row start invisible,
259 sys_end $sys_datatype_expl as row end invisible,
260 period for system_time(sys_start, sys_end)
261 ) engine=innodb with system versioning;
262
263 create index v_cola on a (v_cola);
264
265 --replace_result $sys_datatype_expl SYS_DATATYPE
266 eval create or replace table b(
267 cola int(10),
268 v_cola int(10),
269 sys_start $sys_datatype_expl as row start invisible,
270 sys_end $sys_datatype_expl as row end invisible,
271 period for system_time(sys_start, sys_end)
272 ) engine=innodb with system versioning;
273
274 alter table b add constraint `v_cola_fk`
275 foreign key (v_cola) references a (v_cola);
276
277 insert into a(cola) values (12);
278 insert into b(cola, v_cola) values (10,2);
279 --error ER_ROW_IS_REFERENCED_2
280 delete from a;
281
282 drop table b, a;
283
284 --echo ###############################################
285 --echo # CASCADE UPDATE foreign not system versioned #
286 --echo ###############################################
287 create or replace table parent (
288 id smallint unsigned not null auto_increment,
289 value int unsigned not null,
290 primary key (id, value)
291 ) engine = innodb;
292
293 --replace_result $sys_datatype_expl SYS_DATATYPE
294 eval create or replace table child (
295 id mediumint unsigned not null auto_increment primary key,
296 parent_id smallint unsigned not null,
297 parent_value int unsigned not null,
298 sys_start $sys_datatype_expl as row start invisible,
299 sys_end $sys_datatype_expl as row end invisible,
300 period for system_time(sys_start, sys_end),
301 constraint `fk_child_parent`
302 foreign key (parent_id, parent_value) references parent (id, value)
303 on delete cascade
304 on update cascade
305 ) engine = innodb with system versioning;
306
307 create or replace table subchild (
308 id int not null auto_increment primary key,
309 parent_id smallint unsigned not null,
310 parent_value int unsigned not null,
311 constraint `fk_subchild_child_parent`
312 foreign key (parent_id, parent_value) references child (parent_id, parent_value)
313 on delete cascade
314 on update cascade
315 ) engine=innodb;
316
317 insert into parent (value) values (23);
318 --disable_cursor_protocol
319 --enable_prepare_warnings
320 select id, value from parent into @id, @value;
321 --disable_prepare_warnings
322 --enable_cursor_protocol
323 insert into child values (default, @id, @value);
324 insert into subchild values (default, @id, @value);
325
326 select parent_id from subchild;
327 update parent set id = 11, value = value + 1;
328 select parent_id from subchild;
329 select * from child;
330
331 delete from parent;
332 select count(*) from child;
333 select * from child for system_time all;
334 select count(*) from subchild;
335
336 drop table subchild, child, parent;
337
338 --echo #
339 --echo # MDEV-18057 Assertion `(node->state == 5) || (node->state == 6)' failed in row_upd_sec_step upon DELETE after UPDATE failed due to FK violation
340 --echo #
341 create or replace table t1 (f1 int, key(f1)) engine=innodb;
342 create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning;
343
344 set foreign_key_checks= off;
345 insert ignore into t2 values (1);
346
347 set foreign_key_checks= on;
348 --error ER_NO_REFERENCED_ROW_2
349 update t2 set f2= 2;
350 delete from t2;
351
352 drop table t2, t1;
353
354 --echo #
355 --echo # MDEV-18879 Corrupted record inserted by FOREIGN KEY operation
356 --echo #
357 SET timestamp = 1;
358 SET time_zone='+02:00';
359 SELECT now();
360 CREATE TABLE t1 (
361 pk INT UNSIGNED PRIMARY KEY,
362 f1 varchar(255) CHARACTER SET ucs2,
363 f2 longtext CHARACTER SET ucs2,
364 f3 varchar(255),
365 f4 char(255),
366 f5 longtext CHARACTER SET ucs2,
367 f6 INT UNSIGNED,
368 f7 INT UNSIGNED,
369 f8 INT UNSIGNED,
370 f9 INT UNSIGNED,
371 f10 INT UNSIGNED,
372 f11 INT UNSIGNED,
373 f12 varchar(255) CHARACTER SET ucs2,
374 f13 char(255) CHARACTER SET ucs2,
375 f14 char(255) CHARACTER SET ucs2,
376 f15 varchar(255),
377 f16 longtext,
378 f17 char(255)
379 ) ENGINE=InnoDB WITH SYSTEM VERSIONING;
380
381 INSERT INTO t1 VALUES
382 (1, 'a', 'e', 'f', 'a', 'generate', 1, 2, 3, 4, 5, 6, 'main', 'against', 'b', 'u', 'explode', 'tomorrow'),
383 (2, REPEAT('a',127), 'f', 'k', 'game', 'g', 2, 3, 4, 5, 6, 7, REPEAT('o',222), 'oven', 'flower', REPEAT('r',120), 'l', 'g'),
384 (3, 'weekly', 'x', 'v', 'r', 'c', 3, 4, 5, 6, 7, 8, 'validity', 'y', 'h', 'oxygen', 'venture', 'uncertainty'),
385 (4, 'r', 't', REPEAT('b',153), 'modern', 'h', 4, 5, 6, 7, 8, 9, REPEAT('g',128), 'a', 'w', 'f', 'b', 'b'),
386 (5, 'h', 'y', REPEAT('v',107), 'knife', 'profession', 5, 6, 7, 8, 9, 0, 'infection', 'u', 'likelihood', REPEAT('n',149), 'folk', 'd'),
387 (6, 'g', 'violent', REPEAT('o',28), 'capital', 'p', 6, 7, 8, 9, 0, 1, 'w', 'patron', 'd', 'y', 'originally', 'k'),
388 (7, 'k', 'uncomfortable', REPEAT('v',248), 'y', 'link', 7, 8, 9, 0, 1, 2, REPEAT('j',204), 'j', 'statute', 'emphasis', 'u', 'water'),
389 (8, 'preparation', 'water', 'suck', 'silver', 'a', 8, 9, 0, 1, 2, 3, 'h', 'q', 'o', 't', 'k', 'y'),
390 (9, 'y', 'f', 'e', 'a', 'dawn', 9, 0, 1, 2, 3, 4, 'peak', 'parking', 'b', 't', 'timber', 'c'),
391 (10, REPEAT('h',78), 'apologize', 'direct', 'u', 'frankly', 0, 1, 2, 3, 4, 5, 'h', 'exhibit', 'f', 'd', 'effective', 'c'),
392 (11, 'i', 'h', 'a', 'y', 'u', 1, 2, 3, 4, 5, 6, 'l', 'b', 'm', 'respond', 'ideological', 'credibility');
393
394 CREATE TABLE t2 (
395 pk int primary key,
396 f char(255) CHARACTER SET ucs2,
397 key(f)
398 ) ENGINE=InnoDB;
399
400 INSERT INTO t2 VALUES (1,'against'),(2,'q');
401
402 SET SQL_MODE= '';
403 SET timestamp = 2;
404 SELECT * INTO OUTFILE 't1.data' FROM t1;
405 SET timestamp = 3;
406 UPDATE t1 SET f13 = 'q';
407 SET timestamp = 4;
408 LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
409 SELECT * INTO OUTFILE 't1.data.2' FROM t1;
410 SET timestamp = 5;
411 LOAD DATA INFILE 't1.data.2' REPLACE INTO TABLE t1;
412 SELECT * INTO OUTFILE 't2.data' FROM t2;
413 SET timestamp = 6;
414 LOAD DATA INFILE 't2.data' REPLACE INTO TABLE t2;
415 SET FOREIGN_KEY_CHECKS = OFF;
416 ALTER TABLE t1 ADD FOREIGN KEY (f13) REFERENCES t2 (f) ON DELETE SET NULL;
417 SET timestamp = 7;
418 LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
419 SET FOREIGN_KEY_CHECKS = ON;
420
421 SET SESSION SQL_MODE= 'NO_BACKSLASH_ESCAPES';
422 SET timestamp = 8;
423 LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
424 SET timestamp = 9;
425 REPLACE INTO t2 SELECT * FROM t2;
426
427 # Cleanup
428 DROP TABLE t1, t2;
429 set timestamp= default;
430 set time_zone='+00:00';
431 --let $datadir= `select @@datadir`
432 --remove_file $datadir/test/t1.data
433 --remove_file $datadir/test/t1.data.2
434 --remove_file $datadir/test/t2.data
435
436 --echo #
437 --echo # MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation
438 --echo #
439 create or replace table t1 (a int, key(a)) engine innodb with system versioning;
440 create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb;
441 insert into t1 values (1),(2);
442 insert into t2 values (1);
443 --echo # DELETE from referenced table is not allowed
444 --error ER_ROW_IS_REFERENCED_2
445 delete from t1 where a = 1;
446 drop tables t2, t1;
447
448 --echo #
449 --echo # MDEV-20812 Unexpected ER_ROW_IS_REFERENCED_2 or server crash in row_ins_foreign_report_err upon DELETE from versioned table with FK
450 --echo #
451 create or replace table t1 (x int primary key) engine innodb;
452 create or replace table t2 (x int, foreign key (x) references t1(x)) engine innodb with system versioning;
453 set foreign_key_checks= off;
454 insert into t2 values (1), (1);
455 set foreign_key_checks= on;
456 --echo # DELETE from foreign table is allowed
457 delete from t2;
458 drop tables t2, t1;
459
460 create or replace table t1 (a int, key(a)) engine innodb;
461 insert into t1 values (1);
462 create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb with system versioning;
463 insert into t2 values (1), (1);
464 --echo # DELETE from foreign table is allowed
465 delete from t2;
466 drop tables t2, t1;
467
468 --echo #
469 --echo # MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table
470 --echo #
471 create table t1 (pk int primary key, f1 int,f2 int, f3 text,
472 key(f1), fulltext(f3), key(f3(10)),
473 foreign key (f2) references t1 (f1) on delete set null
474 ) engine=innodb with system versioning;
475
476 insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071));
477
478 delete from t1;
479 select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk;
480
481 # cleanup
482 drop table t1;
483
484 --echo # Shorter case for clustered index (MDEV-25004)
485 create table t1 (
486 y int primary key, r int, f int, key (r),
487 foreign key (f) references t1 (r) on delete set null)
488 with system versioning engine innodb;
489
490 insert into t1 values (1, 6, 6), (2, 6, 6);
491 delete from t1;
492 select *, check_row_ts(row_start, row_end) from t1 for system_time all;
493 drop tables t1;
494
495 --echo # Secondary unique index
496 create table t1 (
497 y int unique null, r int, f int, key (r),
498 foreign key (f) references t1 (r) on delete set null)
499 with system versioning engine innodb;
500
501 insert into t1 values (1, 6, 6), (2, 6, 6);
502 delete from t1;
503 select *, check_row_ts(row_start, row_end) from t1 for system_time all;
504 drop tables t1;
505
506 --echo # Non-unique index cannot be fixed because it does not trigger duplicate error
507 create table t1 (
508 y int, r int, f int, key (y), key (r),
509 foreign key (f) references t1 (r) on delete set null)
510 with system versioning engine innodb;
511
512 insert into t1 values (1, 6, 6), (2, 6, 6);
513 delete from t1;
514 select *, check_row_ts(row_start, row_end) from t1 for system_time all;
515 drop tables t1;
516
517 --echo #
518 --echo # MDEV-21555 Assertion secondary index is out of sync on delete from versioned table
519 --echo #
520 create table t1 (a int, b int as (a + 1) virtual, key(a)) engine=innodb with system versioning;
521
522 set foreign_key_checks= off;
523 insert into t1 (a) values (1), (2);
524 alter table t1 add foreign key (b) references t1 (a), algorithm=copy;
525 update t1 set a= null where a = 1;
526 delete from t1 where a is null;
527 set foreign_key_checks= on;
528
529 delete history from t1;
530 delete from t1;
531
532 # cleanup
533 drop table t1;
534
535 --echo #
536 --echo # MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT
537 --echo # constraint
538 --echo #
539 create table t0 (pk integer primary key) with system versioning engine=innodb;
540 create table t1 (pk integer primary key,
541 foreign key(pk) references t0(pk)
542 on delete restrict on update cascade) engine=innodb;
543 create table t2 (pk integer);
544
545 insert into t0 (pk) values (1);
546 insert into t1 (pk) values (1);
547 insert into t2 (pk) values (1);
548
549 --error ER_ROW_IS_REFERENCED_2
550 delete from t0;
551
552 --error ER_ROW_IS_REFERENCED_2
553 replace t0 values (1);
554
555 select * into outfile 'load_t0' from t0 ;
556 --error ER_ROW_IS_REFERENCED_2
557 load data infile 'load_t0' replace into table t0;
558
559 --error ER_ROW_IS_REFERENCED_2
560 delete t0, t2 from t0 join t2;
561
562 select pk from t0;
563
564 --echo # Cleanup
565 drop table t1, t0, t2;
566 --let $datadir= `select @@datadir`
567 --remove_file $datadir/test/load_t0
568
569
570 --echo # create_select for a temporary table didn't set up pos_in_locked_tables.
571 create table t (a int unique) engine=innodb
572 replace select 1 as a, 2 as b union select 1 as a, 3 as c;
573 select * from t;
574 drop table t;
575
576 create temporary table t (a int unique) engine=innodb
577 replace select 1 as a, 2 as b union select 1 as a, 3 as c;
578 select * from t;
579 drop table t;
580
581 --echo #
582 --echo # MDEV-20729 Fix REFERENCES constraint in column definition
583 --echo #
584 create table t1(id int);
585 --echo # system fields can't be foreign keys:
586 --replace_result $sys_datatype_expl SYS_DATATYPE
587 --error ER_PARSE_ERROR,ER_PARSE_ERROR
588 eval create or replace table t2(
589 x int,
590 sys_start $sys_datatype_expl as row start references t1(id),
591 sys_end $sys_datatype_expl as row end,
592 period for system_time(sys_start, sys_end)
593 ) engine innodb with system versioning;
594 --replace_result $sys_datatype_expl SYS_DATATYPE
595 --error ER_PARSE_ERROR,ER_PARSE_ERROR
596 eval create or replace table t2(
597 x int,
598 sys_start $sys_datatype_expl as row start,
599 sys_end $sys_datatype_expl as row end references t1(id),
600 period for system_time(sys_start, sys_end)
601 ) engine innodb with system versioning;
602 --replace_result $sys_datatype_expl SYS_DATATYPE
603 --error ER_CANT_CREATE_TABLE
604 eval create or replace table t2(
605 x int,
606 sys_start $sys_datatype_expl as row start,
607 sys_end $sys_datatype_expl as row end,
608 period for system_time(sys_start, sys_end),
609 foreign key (sys_start) references t1(id)
610 ) engine innodb with system versioning;
611 --replace_result $sys_datatype_expl SYS_DATATYPE
612 --error ER_CANT_CREATE_TABLE
613 eval create or replace table t2(
614 x int,
615 sys_start $sys_datatype_expl as row start,
616 sys_end $sys_datatype_expl as row end,
617 period for system_time(sys_start, sys_end),
618 foreign key (sys_end) references t1(id)
619 ) engine innodb with system versioning;
620 drop table t1;
621
622 --echo # End of 10.5 tests
623
624 --source suite/versioning/common_finish.inc