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 9eval create table parent( 10 id int, 11 $KEY_TYPE (id) 12) engine innodb; 13 14--replace_result $sys_datatype_expl SYS_DATATYPE 15eval 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 25insert into parent values(1); 26insert into child values(1); 27 28-- error ER_ROW_IS_REFERENCED_2 29delete from parent where id = 1; 30delete from child where parent_id = 1; 31delete from parent where id = 1; 32 33insert into parent values(1); 34insert into child values(1); 35-- error ER_ROW_IS_REFERENCED_2 36update parent set id=id+1; 37delete from child; 38update parent set id=id+1; 39select * from child for system_time all; 40 41drop table child; 42drop 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 49eval create table parent( 50 id int(10) unsigned, 51 $KEY_TYPE (id) 52) engine innodb; 53 54--replace_result $sys_datatype_expl SYS_DATATYPE 55eval 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 63insert into parent values(1); 64insert into child values(1); 65 66-- error ER_ROW_IS_REFERENCED_2 67delete from parent where id = 1; 68 69drop table child; 70drop table parent; 71 72--echo ################ 73--echo # Test CASCADE # 74--echo ################ 75 76--replace_result "$KEY_TYPE" KEY_TYPE 77eval create table parent( 78 id int, 79 $KEY_TYPE (id) 80) engine innodb; 81 82--replace_result $sys_datatype_expl SYS_DATATYPE 83eval 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 93insert into parent values(1); 94insert into child values(1); 95 96delete from parent where id = 1; 97select * from child; 98select * from child for system_time all; 99 100insert into parent values(1); 101insert into child values(1); 102update parent set id = id + 1; 103select * from child; 104select * from child for system_time all; 105 106drop table child; 107drop table parent; 108 109--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE 110eval 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 117engine innodb; 118 119create 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) 127engine innodb; 128 129insert into parent (id) values (2); 130insert into child (x, parent_id) values (2, 2); 131delete from parent; 132select * from child; 133 134drop table child; 135drop table parent; 136 137--replace_result "$KEY_TYPE" KEY_TYPE 138eval create or replace table parent ( 139 id int, 140 $KEY_TYPE(id) 141) 142engine innodb; 143 144--replace_result $sys_datatype_expl SYS_DATATYPE 145eval 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 156engine innodb; 157 158insert into parent (id) values (3); 159insert into child (id, parent_id) values (3, 3); 160delete from parent; 161select * from child; 162select *, check_row(row_start, row_end) from child for system_time all; 163 164drop table child; 165drop table parent; 166 167--echo ################# 168--echo # Test SET NULL # 169--echo ################# 170 171--replace_result "$KEY_TYPE" KEY_TYPE 172eval create table parent( 173 id int, 174 $KEY_TYPE (id) 175) engine innodb; 176 177--replace_result $sys_datatype_expl SYS_DATATYPE 178eval 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 188insert into parent values(1); 189insert into child values(1); 190delete from child; 191insert into child values(1); 192 193delete from parent where id = 1; 194select * from child; 195select *, current_row(sys_end) as current_row from child for system_time all order by sys_end; 196delete from child; 197 198insert into parent values(1); 199insert into child values(1); 200update parent set id= id + 1; 201select * from child; 202select *, current_row(sys_end) as current_row from child for system_time all order by sys_end; 203 204drop table child; 205drop 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 212eval 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 220create or replace table child( 221 parent_id int, 222 foreign key(parent_id) references parent(id) 223) engine innodb; 224 225insert into parent values(1); 226insert into child values(1); 227-- error ER_ROW_IS_REFERENCED_2 228delete from parent; 229-- error ER_ROW_IS_REFERENCED_2 230update parent set id=2; 231 232delete from child; 233delete from parent; 234 235-- error ER_NO_REFERENCED_ROW_2 236insert into child values(1); 237 238insert into parent values(1); 239insert into child values(1); 240-- error ER_ROW_IS_REFERENCED_2 241delete from parent; 242-- error ER_ROW_IS_REFERENCED_2 243update parent set id=2; 244 245drop table child; 246drop table parent; 247 248--echo ################### 249--echo # crash on DELETE # 250--echo ################### 251 252--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE 253eval create or replace table a ( 254 cola int(10), 255 $KEY_TYPE (cola), 256 v_cola int(10) as (cola mod 10) virtual, 257 sys_start $sys_datatype_expl as row start invisible, 258 sys_end $sys_datatype_expl as row end invisible, 259 period for system_time(sys_start, sys_end) 260) engine=innodb with system versioning; 261 262create index v_cola on a (v_cola); 263 264--replace_result $sys_datatype_expl SYS_DATATYPE 265eval create or replace table b( 266 cola int(10), 267 v_cola int(10), 268 sys_start $sys_datatype_expl as row start invisible, 269 sys_end $sys_datatype_expl as row end invisible, 270 period for system_time(sys_start, sys_end) 271) engine=innodb with system versioning; 272 273alter table b add constraint `v_cola_fk` 274foreign key (v_cola) references a (v_cola); 275 276insert into a(cola) values (12); 277insert into b(cola, v_cola) values (10,2); 278--error ER_ROW_IS_REFERENCED_2 279delete from a; 280 281drop table b, a; 282 283--echo ############################################### 284--echo # CASCADE UPDATE foreign not system versioned # 285--echo ############################################### 286create or replace table parent ( 287 id smallint unsigned not null auto_increment, 288 value int unsigned not null, 289 primary key (id, value) 290) engine = innodb; 291 292--replace_result $sys_datatype_expl SYS_DATATYPE 293eval create or replace table child ( 294 id mediumint unsigned not null auto_increment primary key, 295 parent_id smallint unsigned not null, 296 parent_value int unsigned not null, 297 sys_start $sys_datatype_expl as row start invisible, 298 sys_end $sys_datatype_expl as row end invisible, 299 period for system_time(sys_start, sys_end), 300 constraint `fk_child_parent` 301 foreign key (parent_id, parent_value) references parent (id, value) 302 on delete cascade 303 on update cascade 304) engine = innodb with system versioning; 305 306create or replace table subchild ( 307 id int not null auto_increment primary key, 308 parent_id smallint unsigned not null, 309 parent_value int unsigned not null, 310 constraint `fk_subchild_child_parent` 311 foreign key (parent_id, parent_value) references child (parent_id, parent_value) 312 on delete cascade 313 on update cascade 314) engine=innodb; 315 316insert into parent (value) values (23); 317select id, value from parent into @id, @value; 318insert into child values (default, @id, @value); 319insert into subchild values (default, @id, @value); 320 321select parent_id from subchild; 322update parent set id = 11, value = value + 1; 323select parent_id from subchild; 324select * from child; 325 326delete from parent; 327select count(*) from child; 328select * from child for system_time all; 329select count(*) from subchild; 330 331drop table subchild, child, parent; 332 333--echo # 334--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 335--echo # 336create or replace table t1 (f1 int, key(f1)) engine=innodb; 337create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning; 338 339set foreign_key_checks= off; 340insert ignore into t2 values (1); 341 342set foreign_key_checks= on; 343--error ER_NO_REFERENCED_ROW_2 344update t2 set f2= 2; 345delete from t2; 346 347drop table t2, t1; 348 349--echo # 350--echo # MDEV-18879 Corrupted record inserted by FOREIGN KEY operation 351--echo # 352SET timestamp = 1; 353SET time_zone='+02:00'; 354SELECT now(); 355CREATE TABLE t1 ( 356 pk INT UNSIGNED PRIMARY KEY, 357 f1 varchar(255) CHARACTER SET ucs2, 358 f2 longtext CHARACTER SET ucs2, 359 f3 varchar(255), 360 f4 char(255), 361 f5 longtext CHARACTER SET ucs2, 362 f6 INT UNSIGNED, 363 f7 INT UNSIGNED, 364 f8 INT UNSIGNED, 365 f9 INT UNSIGNED, 366 f10 INT UNSIGNED, 367 f11 INT UNSIGNED, 368 f12 varchar(255) CHARACTER SET ucs2, 369 f13 char(255) CHARACTER SET ucs2, 370 f14 char(255) CHARACTER SET ucs2, 371 f15 varchar(255), 372 f16 longtext, 373 f17 char(255) 374) ENGINE=InnoDB WITH SYSTEM VERSIONING; 375 376INSERT INTO t1 VALUES 377(1, 'a', 'e', 'f', 'a', 'generate', 1, 2, 3, 4, 5, 6, 'main', 'against', 'b', 'u', 'explode', 'tomorrow'), 378(2, REPEAT('a',127), 'f', 'k', 'game', 'g', 2, 3, 4, 5, 6, 7, REPEAT('o',222), 'oven', 'flower', REPEAT('r',120), 'l', 'g'), 379(3, 'weekly', 'x', 'v', 'r', 'c', 3, 4, 5, 6, 7, 8, 'validity', 'y', 'h', 'oxygen', 'venture', 'uncertainty'), 380(4, 'r', 't', REPEAT('b',153), 'modern', 'h', 4, 5, 6, 7, 8, 9, REPEAT('g',128), 'a', 'w', 'f', 'b', 'b'), 381(5, 'h', 'y', REPEAT('v',107), 'knife', 'profession', 5, 6, 7, 8, 9, 0, 'infection', 'u', 'likelihood', REPEAT('n',149), 'folk', 'd'), 382(6, 'g', 'violent', REPEAT('o',28), 'capital', 'p', 6, 7, 8, 9, 0, 1, 'w', 'patron', 'd', 'y', 'originally', 'k'), 383(7, 'k', 'uncomfortable', REPEAT('v',248), 'y', 'link', 7, 8, 9, 0, 1, 2, REPEAT('j',204), 'j', 'statute', 'emphasis', 'u', 'water'), 384(8, 'preparation', 'water', 'suck', 'silver', 'a', 8, 9, 0, 1, 2, 3, 'h', 'q', 'o', 't', 'k', 'y'), 385(9, 'y', 'f', 'e', 'a', 'dawn', 9, 0, 1, 2, 3, 4, 'peak', 'parking', 'b', 't', 'timber', 'c'), 386(10, REPEAT('h',78), 'apologize', 'direct', 'u', 'frankly', 0, 1, 2, 3, 4, 5, 'h', 'exhibit', 'f', 'd', 'effective', 'c'), 387(11, 'i', 'h', 'a', 'y', 'u', 1, 2, 3, 4, 5, 6, 'l', 'b', 'm', 'respond', 'ideological', 'credibility'); 388 389CREATE TABLE t2 ( 390 pk int primary key, 391 f char(255) CHARACTER SET ucs2, 392 key(f) 393) ENGINE=InnoDB; 394 395INSERT INTO t2 VALUES (1,'against'),(2,'q'); 396 397SET SQL_MODE= ''; 398SET timestamp = 2; 399SELECT * INTO OUTFILE 't1.data' FROM t1; 400SET timestamp = 3; 401UPDATE t1 SET f13 = 'q'; 402SET timestamp = 4; 403LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; 404SELECT * INTO OUTFILE 't1.data.2' FROM t1; 405SET timestamp = 5; 406LOAD DATA INFILE 't1.data.2' REPLACE INTO TABLE t1; 407SELECT * INTO OUTFILE 't2.data' FROM t2; 408SET timestamp = 6; 409LOAD DATA INFILE 't2.data' REPLACE INTO TABLE t2; 410SET FOREIGN_KEY_CHECKS = OFF; 411ALTER TABLE t1 ADD FOREIGN KEY (f13) REFERENCES t2 (f) ON DELETE SET NULL; 412SET timestamp = 7; 413LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; 414SET FOREIGN_KEY_CHECKS = ON; 415 416SET SESSION SQL_MODE= 'NO_BACKSLASH_ESCAPES'; 417SET timestamp = 8; 418LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; 419SET timestamp = 9; 420REPLACE INTO t2 SELECT * FROM t2; 421 422# Cleanup 423DROP TABLE t1, t2; 424set timestamp= default; 425set time_zone='+00:00'; 426--let $datadir= `select @@datadir` 427--remove_file $datadir/test/t1.data 428--remove_file $datadir/test/t1.data.2 429--remove_file $datadir/test/t2.data 430 431--echo # 432--echo # MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation 433--echo # 434create or replace table t1 (a int, key(a)) engine innodb with system versioning; 435create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb; 436insert into t1 values (1),(2); 437insert into t2 values (1); 438--echo # DELETE from referenced table is not allowed 439--error ER_ROW_IS_REFERENCED_2 440delete from t1 where a = 1; 441drop tables t2, t1; 442 443--echo # 444--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 445--echo # 446create or replace table t1 (x int primary key) engine innodb; 447create or replace table t2 (x int, foreign key (x) references t1(x)) engine innodb with system versioning; 448set foreign_key_checks= off; 449insert into t2 values (1), (1); 450set foreign_key_checks= on; 451--echo # DELETE from foreign table is allowed 452delete from t2; 453drop tables t2, t1; 454 455create or replace table t1 (a int, key(a)) engine innodb; 456insert into t1 values (1); 457create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb with system versioning; 458insert into t2 values (1), (1); 459--echo # DELETE from foreign table is allowed 460delete from t2; 461drop tables t2, t1; 462 463--echo # 464--echo # MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table 465--echo # 466create table t1 (pk int primary key, f1 int,f2 int, f3 text, 467 key(f1), fulltext(f3), key(f3(10)), 468 foreign key (f2) references t1 (f1) on delete set null 469) engine=innodb with system versioning; 470 471insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071)); 472 473delete from t1; 474select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk; 475 476# cleanup 477drop table t1; 478 479--echo # 480--echo # MDEV-21555 Assertion secondary index is out of sync on delete from versioned table 481--echo # 482create table t1 (a int, b int as (a + 1) virtual, key(a)) engine=innodb with system versioning; 483 484set foreign_key_checks= off; 485insert into t1 (a) values (1), (2); 486alter table t1 add foreign key (b) references t1 (a), algorithm=copy; 487update t1 set a= null where a = 1; 488delete from t1 where a is null; 489set foreign_key_checks= on; 490 491delete history from t1; 492delete from t1; 493 494# cleanup 495drop table t1; 496 497--source suite/versioning/common_finish.inc 498