1# 2# Test syntax of foreign keys 3# 4 5--disable_query_log 6call mtr.add_suppression(" In RENAME TABLE table `test`.`parent` is referenced in foreign key constraints which are not compatible with the new table definition."); 7--enable_query_log 8SET @saved_binlog_format= @@SESSION.binlog_format; 9 10# 11# Bug#34455 (Ambiguous foreign keys syntax is accepted) 12# 13 14--disable_warnings 15drop table if exists t_34455; 16--enable_warnings 17 18# 2 match clauses, illegal 19--error ER_PARSE_ERROR 20create table t_34455 ( 21 a int not null, 22 foreign key (a) references t3 (a) match full match partial); 23 24# match after on delete, illegal 25--error ER_PARSE_ERROR 26create table t_34455 ( 27 a int not null, 28 foreign key (a) references t3 (a) on delete set default match full); 29 30# match after on update, illegal 31--error ER_PARSE_ERROR 32create table t_34455 ( 33 a int not null, 34 foreign key (a) references t3 (a) on update set default match full); 35 36# 2 on delete clauses, illegal 37--error ER_PARSE_ERROR 38create table t_34455 ( 39 a int not null, 40 foreign key (a) references t3 (a) 41 on delete set default on delete set default); 42 43# 2 on update clauses, illegal 44--error ER_PARSE_ERROR 45create table t_34455 ( 46 a int not null, 47 foreign key (a) references t3 (a) 48 on update set default on update set default); 49 50create table t_34455 (a int not null); 51 52# 2 match clauses, illegal 53--error ER_PARSE_ERROR 54alter table t_34455 55 add foreign key (a) references t3 (a) match full match partial); 56 57# match after on delete, illegal 58--error ER_PARSE_ERROR 59alter table t_34455 60 add foreign key (a) references t3 (a) on delete set default match full); 61 62# match after on update, illegal 63--error ER_PARSE_ERROR 64alter table t_34455 65 add foreign key (a) references t3 (a) on update set default match full); 66 67# 2 on delete clauses, illegal 68--error ER_PARSE_ERROR 69alter table t_34455 70 add foreign key (a) references t3 (a) 71 on delete set default on delete set default); 72 73# 2 on update clauses, illegal 74--error ER_PARSE_ERROR 75alter table t_34455 76 add foreign key (a) references t3 (a) 77 on update set default on update set default); 78 79drop table t_34455; 80 81--echo # 82--echo # WL#6929: Move FOREIGN KEY constraints to the global data dictionary 83--echo # 84 85--echo # Extra coverage of @@foreign_key_checks 86--echo # 87 88SET @@foreign_key_checks= 0; 89CREATE TABLE t1(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES non(a)); 90ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES non(a); 91DROP TABLE t1; 92 93CREATE TABLE t1(a INT PRIMARY KEY); 94CREATE TABLE t2(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES t1(a)); 95DROP TABLE t1; 96DROP TABLE t2; 97 98SET @@foreign_key_checks= 1; 99--error ER_FK_CANNOT_OPEN_PARENT 100CREATE TABLE t1(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES non(a)); 101# Works, but no FK created 102CREATE TABLE t1(a INT PRIMARY KEY, b INT REFERENCES non(a)); 103SHOW CREATE TABLE t1; 104--error ER_FK_CANNOT_OPEN_PARENT 105ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES non(a); 106DROP TABLE t1; 107 108CREATE TABLE t1(a INT PRIMARY KEY); 109CREATE TABLE t2(a INT PRIMARY KEY, b INT, FOREIGN KEY (b) REFERENCES t1(a)); 110--error ER_FK_CANNOT_DROP_PARENT 111DROP TABLE t1; 112DROP TABLE t2, t1; 113 114SET @@foreign_key_checks= DEFAULT; 115 116--echo # Test coverage of identifier length related to foreign keys. 117--echo # 118 119CREATE TABLE t1(a INT PRIMARY KEY); 120 121# Exactly 64 chars 122CREATE TABLE t2(a INT PRIMARY KEY, b INT); 123ALTER TABLE t2 ADD CONSTRAINT 124name567890123456789012345678901234567890123456789012345678901234 125FOREIGN KEY 126name567890123456789012345678901234567890123456789012345678901234 127(b) REFERENCES t1(a); 128SHOW CREATE TABLE t2; 129SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 't1'; 130DROP TABLE t2; 131 132# 65 chars - too long 133CREATE TABLE t2(a INT PRIMARY KEY, b INT); 134--error ER_TOO_LONG_IDENT 135ALTER TABLE t2 ADD FOREIGN KEY 136name5678901234567890123456789012345678901234567890123456789012345 137(b) REFERENCES t1(a); 138--error ER_TOO_LONG_IDENT 139ALTER TABLE t2 ADD CONSTRAINT 140name5678901234567890123456789012345678901234567890123456789012345 141FOREIGN KEY (b) REFERENCES t1(a); 142DROP TABLE t2; 143 144# 65 chars - too long, now with pre-existing index on b 145CREATE TABLE t2(a INT PRIMARY KEY, b INT UNIQUE); 146--error ER_TOO_LONG_IDENT 147ALTER TABLE t2 ADD FOREIGN KEY 148name5678901234567890123456789012345678901234567890123456789012345 149(b) REFERENCES t1(a); 150--error ER_TOO_LONG_IDENT 151ALTER TABLE t2 ADD CONSTRAINT 152name5678901234567890123456789012345678901234567890123456789012345 153FOREIGN KEY (b) REFERENCES t1(a); 154DROP TABLE t2; 155 156DROP TABLE t1; 157 158--error ER_TOO_LONG_IDENT 159CREATE TABLE t1(a INT PRIMARY KEY, b INT, 160FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345.t2(a)); 161--error ER_WRONG_TABLE_NAME 162CREATE TABLE t1(a INT PRIMARY KEY, b INT, 163FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345(a)); 164--error ER_WRONG_COLUMN_NAME 165CREATE TABLE t1(a INT PRIMARY KEY, b INT, 166FOREIGN KEY(b) REFERENCES t2(name5678901234567890123456789012345678901234567890123456789012345)); 167 168SET @@foreign_key_checks= 0; 169 170--error ER_TOO_LONG_IDENT 171CREATE TABLE t1(a INT PRIMARY KEY, b INT, 172FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345.t2(a)); 173--error ER_WRONG_TABLE_NAME 174CREATE TABLE t1(a INT PRIMARY KEY, b INT, 175FOREIGN KEY(b) REFERENCES name5678901234567890123456789012345678901234567890123456789012345(a)); 176--error ER_WRONG_COLUMN_NAME 177CREATE TABLE t1(a INT PRIMARY KEY, b INT, 178FOREIGN KEY(b) REFERENCES t2(name5678901234567890123456789012345678901234567890123456789012345)); 179 180SET @@foreign_key_checks= DEFAULT; 181 182--echo # 183--echo # Bug#24666169: I_S.TABLE_CONSTRAINTS.CONSTRAINT_NAME IS NOT UPDATED 184--echo # AFTER RENAME TABLE 185--echo # 186 187SET @@foreign_key_checks= 1; 188 189--echo # 190--echo # Tests for FK name behavior. 191 192CREATE TABLE t1(c1 INT PRIMARY KEY); 193CREATE TABLE t2(c1 INT, FOREIGN KEY (c1) REFERENCES t1(c1)); 194 195ALTER TABLE t2 RENAME TO t3; 196SHOW CREATE TABLE t3; 197--error ER_NO_REFERENCED_ROW_2 198INSERT INTO t3 VALUES(1); 199 200ALTER TABLE t3 RENAME TO t4, ALGORITHM= INPLACE; 201SHOW CREATE TABLE t4; 202--error ER_NO_REFERENCED_ROW_2 203INSERT INTO t4 VALUES(1); 204 205# TODO: COPY does not work properly, see Bug#25467454 206ALTER TABLE t4 RENAME TO t5; #, ALGORITHM= COPY; 207SHOW CREATE TABLE t5; 208--error ER_NO_REFERENCED_ROW_2 209INSERT INTO t5 VALUES(1); 210 211RENAME TABLE t5 to t6; 212SHOW CREATE TABLE t6; 213--error ER_NO_REFERENCED_ROW_2 214INSERT INTO t6 VALUES(1); 215 216DROP TABLE t6, t1; 217 218--echo # 219--echo # Tests of FK name generation 220 221CREATE TABLE t1(a INT PRIMARY KEY); 222CREATE TABLE t2(a INT, b INT, FOREIGN KEY(a) REFERENCES t1(a)); 223SELECT constraint_name FROM information_schema.referential_constraints 224 WHERE table_name = 't2' ORDER BY constraint_name; 225SELECT constraint_name FROM information_schema.table_constraints 226 WHERE table_name = 't2' ORDER BY constraint_name; 227 228--echo # Add FK 229ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a); 230SELECT constraint_name FROM information_schema.referential_constraints 231 WHERE table_name = 't2' ORDER BY constraint_name; 232SELECT constraint_name FROM information_schema.table_constraints 233 WHERE table_name = 't2' ORDER BY constraint_name; 234 235--echo # Remove first FK and add a new FK. 236ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1; 237ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a); 238SELECT constraint_name FROM information_schema.referential_constraints 239 WHERE table_name = 't2' ORDER BY constraint_name; 240SELECT constraint_name FROM information_schema.table_constraints 241 WHERE table_name = 't2' ORDER BY constraint_name; 242 243--echo # Rename table in different ways. 244ALTER TABLE t2 RENAME TO t3; 245SELECT constraint_name FROM information_schema.referential_constraints 246 WHERE table_name = 't3' ORDER BY constraint_name; 247SELECT constraint_name FROM information_schema.table_constraints 248 WHERE table_name = 't3' ORDER BY constraint_name; 249ALTER TABLE t3 RENAME TO t4, ALGORITHM= INPLACE; 250SELECT constraint_name FROM information_schema.referential_constraints 251 WHERE table_name = 't4' ORDER BY constraint_name; 252SELECT constraint_name FROM information_schema.table_constraints 253 WHERE table_name = 't4' ORDER BY constraint_name; 254# COPY does not work properly, see Bug#25467454 255ALTER TABLE t4 RENAME TO t5; #, ALGORITHM= COPY; 256SELECT constraint_name FROM information_schema.referential_constraints 257 WHERE table_name = 't5' ORDER BY constraint_name; 258SELECT constraint_name FROM information_schema.table_constraints 259 WHERE table_name = 't5' ORDER BY constraint_name; 260RENAME TABLE t5 TO t6; 261SELECT constraint_name FROM information_schema.referential_constraints 262 WHERE table_name = 't6' ORDER BY constraint_name; 263SELECT constraint_name FROM information_schema.table_constraints 264 WHERE table_name = 't6' ORDER BY constraint_name; 265 266--echo # Simulate dump+restore and test rename 267DROP TABLE t6; 268CREATE TABLE `t6` ( 269 `a` int(11) DEFAULT NULL, 270 `b` int(11) DEFAULT NULL, 271 KEY `b` (`b`), 272 KEY `a` (`a`), 273 CONSTRAINT `t6_ibfk_2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`), 274 CONSTRAINT `t6_ibfk_3` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) 275) ENGINE=InnoDB DEFAULT CHARSET=latin1; 276SELECT constraint_name FROM information_schema.referential_constraints 277 WHERE table_name = 't6' ORDER BY constraint_name; 278SELECT constraint_name FROM information_schema.table_constraints 279 WHERE table_name = 't6' ORDER BY constraint_name; 280RENAME TABLE t6 TO t2; 281SELECT constraint_name FROM information_schema.referential_constraints 282 WHERE table_name = 't2' ORDER BY constraint_name; 283SELECT constraint_name FROM information_schema.table_constraints 284 WHERE table_name = 't2' ORDER BY constraint_name; 285 286--echo # Remove all FKs and add one back 287ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_2, DROP FOREIGN KEY t2_ibfk_3; 288ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a); 289SELECT constraint_name FROM information_schema.referential_constraints 290 WHERE table_name = 't2' ORDER BY constraint_name; 291SELECT constraint_name FROM information_schema.table_constraints 292 WHERE table_name = 't2' ORDER BY constraint_name; 293 294--echo # Add a foreign key with close to generated name 295ALTER TABLE t2 ADD CONSTRAINT t3_ibfk_2 FOREIGN KEY(b) REFERENCES t1(a); 296SELECT constraint_name FROM information_schema.referential_constraints 297 WHERE table_name = 't2' ORDER BY constraint_name; 298SELECT constraint_name FROM information_schema.table_constraints 299 WHERE table_name = 't2' ORDER BY constraint_name; 300 301--echo # Then rename so that the given name now matches a generated name 302RENAME TABLE t2 TO t3; 303SELECT constraint_name FROM information_schema.referential_constraints 304 WHERE table_name = 't3' ORDER BY constraint_name; 305SELECT constraint_name FROM information_schema.table_constraints 306 WHERE table_name = 't3' ORDER BY constraint_name; 307 308--echo # Finally rename it again. The given name is now seen as generated and renamed. 309RENAME TABLE t3 TO t4; 310SELECT constraint_name FROM information_schema.referential_constraints 311 WHERE table_name = 't4' ORDER BY constraint_name; 312SELECT constraint_name FROM information_schema.table_constraints 313 WHERE table_name = 't4' ORDER BY constraint_name; 314DROP TABLE t4; 315 316--echo # Make a foreign key with given name matching a generated name 317CREATE TABLE t2(a INT, b INT); 318ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_1 FOREIGN KEY(a) REFERENCES t1(a); 319# Then add a new FK with generated name 320ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a); 321SELECT constraint_name FROM information_schema.referential_constraints 322 WHERE table_name = 't2' ORDER BY constraint_name; 323SELECT constraint_name FROM information_schema.table_constraints 324 WHERE table_name = 't2' ORDER BY constraint_name; 325DROP TABLE t2; 326 327--echo # Test FK name case sensitivity 328CREATE TABLE t2(a INT, b INT); 329ALTER TABLE t2 ADD CONSTRAINT FK FOREIGN KEY(a) REFERENCES t1(a); 330SELECT constraint_name FROM information_schema.referential_constraints 331 WHERE table_name = 't2' ORDER BY constraint_name; 332SELECT constraint_name FROM information_schema.table_constraints 333 WHERE table_name = 't2' ORDER BY constraint_name; 334 335--error ER_DUP_KEYNAME 336ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a); 337ALTER TABLE t2 DROP FOREIGN KEY FK; 338 339--echo # Name matching generated name, but different case. 340ALTER TABLE t2 ADD CONSTRAINT T2_IBFK_1 FOREIGN KEY(a) REFERENCES t1(a); 341--error ER_FK_DUP_NAME 342ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a); 343ALTER TABLE t2 DROP FOREIGN KEY T2_IBFK_1; 344DROP TABLE t2; 345 346--echo # Check long FK generated names due to long table names. 347CREATE TABLE t2 (a INT, FOREIGN KEY (a) REFERENCES t1(a)); 348--error ER_TOO_LONG_IDENT 349RENAME TABLE t2 TO t123456789012345678901234567890123456789012345678901234567; 350RENAME TABLE t2 TO t12345678901234567890123456789012345678901234567890123456; 351SELECT constraint_name FROM information_schema.referential_constraints 352 WHERE table_name = 't12345678901234567890123456789012345678901234567890123456' 353 ORDER BY constraint_name; 354SELECT constraint_name FROM information_schema.table_constraints 355 WHERE table_name = 't12345678901234567890123456789012345678901234567890123456' 356 ORDER BY constraint_name; 357DROP TABLE t12345678901234567890123456789012345678901234567890123456; 358--error ER_TOO_LONG_IDENT 359CREATE TABLE t123456789012345678901234567890123456789012345678901234567( 360 a INT, FOREIGN KEY (a) REFERENCES t1(a)); 361CREATE TABLE t123456789012345678901234567890123456789012345678901234567890123( 362 a INT, CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1(a)); 363DROP TABLE t123456789012345678901234567890123456789012345678901234567890123; 364 365DROP TABLE t1; 366 367--echo # FK Referencing virtual column 368CREATE TABLE t1(a INT PRIMARY KEY, 369 b INT GENERATED ALWAYS AS (a+1) VIRTUAL UNIQUE); 370--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 371CREATE TABLE t2(a INT, FOREIGN KEY (a) REFERENCES t1(b)); 372CREATE TABLE t2(a INT); 373--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 374ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(b); 375DROP TABLE t1, t2; 376 377--echo # FK on generated stored column 378CREATE TABLE t1(a INT PRIMARY KEY); 379CREATE TABLE t2(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE); 380 381CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE, 382 FOREIGN KEY (b) REFERENCES t1(a)); 383ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a); 384ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1; 385DROP TABLE t3; 386 387--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 388CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE, 389 FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE); 390--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 391ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a) ON UPDATE CASCADE; 392 393--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 394CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE, 395 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL); 396--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 397ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a) ON DELETE SET NULL; 398 399--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 400CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE, 401 FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE SET NULL); 402--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 403ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a) ON UPDATE SET NULL; 404 405--echo # FK on Base column of generated stored column. 406CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE, 407 FOREIGN KEY (a) REFERENCES t1(a)); 408ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a); 409ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1; 410DROP TABLE t3; 411 412--error ER_CANNOT_ADD_FOREIGN 413CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE, 414 FOREIGN KEY (a) REFERENCES t1(a) ON UPDATE CASCADE); 415--error ER_CANNOT_ADD_FOREIGN 416ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a) ON UPDATE CASCADE; 417 418--error ER_CANNOT_ADD_FOREIGN 419CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE, 420 FOREIGN KEY (a) REFERENCES t1(a) ON DELETE SET NULL); 421--error ER_CANNOT_ADD_FOREIGN 422ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a) ON DELETE SET NULL; 423 424--error ER_CANNOT_ADD_FOREIGN 425CREATE TABLE t3(a INT, b INT GENERATED ALWAYS AS (a+1) STORED UNIQUE, 426 FOREIGN KEY (a) REFERENCES t1(a) ON UPDATE SET NULL); 427--error ER_CANNOT_ADD_FOREIGN 428ALTER TABLE t2 ADD FOREIGN KEY(a) REFERENCES t1(a) ON UPDATE SET NULL; 429 430DROP TABLE t2, t1; 431 432--echo # FK on virtual column not supported. 433CREATE TABLE t1(a INT PRIMARY KEY); 434 435--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 436CREATE TABLE t2(a INT, b INT GENERATED ALWAYS AS (a+1) VIRTUAL UNIQUE, 437 FOREIGN KEY(b) REFERENCES t1(a)); 438 439CREATE TABLE t2(a INT, b INT GENERATED ALWAYS AS (a+1) VIRTUAL UNIQUE); 440--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 441ALTER TABLE t2 ADD FOREIGN KEY (b) REFERENCES t1(a); 442DROP TABLE t2; 443 444CREATE TABLE t2(a INT, b INT, FOREIGN KEY(b) REFERENCES t1(a)); 445--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN 446ALTER TABLE t2 MODIFY COLUMN b INT GENERATED ALWAYS AS (a+1) VIRTUAL; 447 448DROP TABLE t2, t1; 449 450--echo # Trying to drop columns used in multi-column FKs. 451CREATE TABLE t1(a INT PRIMARY KEY, b INT, INDEX(a, b)); 452 453CREATE TABLE t2(a INT, b INT, FOREIGN KEY(a, b) REFERENCES t1(a, b)); 454--error ER_FK_COLUMN_CANNOT_DROP 455ALTER TABLE t2 DROP COLUMN a; 456--error ER_FK_COLUMN_CANNOT_DROP 457ALTER TABLE t2 DROP COLUMN b; 458DROP TABLE t2; 459 460--echo # Use explicitly named index to check where index name is != column name. 461CREATE TABLE t2(a INT, b INT, INDEX idx(a, b), 462 FOREIGN KEY(a, b) REFERENCES t1(a, b)); 463--error ER_FK_COLUMN_CANNOT_DROP 464ALTER TABLE t2 DROP COLUMN a; 465--error ER_FK_COLUMN_CANNOT_DROP 466ALTER TABLE t2 DROP COLUMN b; 467DROP TABLE t2, t1; 468 469--echo # Index with prefix cannot be used for supporting FK. 470CREATE TABLE t1 (PK VARCHAR(100) PRIMARY KEY); 471CREATE TABLE t2 (FK VARCHAR(100), FOREIGN KEY(FK) REFERENCES t1 (PK), KEY(FK)); 472--error ER_DROP_INDEX_FK 473ALTER TABLE t2 DROP INDEX FK, ADD INDEX FK2(FK(10)); 474DROP TABLE t2, t1; 475 476 477--echo # Bug#25817660: Combination of virtual index, foreign key and trigger 478--echo # result in assert failure 479 480CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); 481CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=0; 482CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY(fld2), 483 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE); 484 485INSERT INTO t1 VALUES(1); 486INSERT INTO t2 VALUES(1, DEFAULT); 487 488UPDATE t1 SET fld1= 2; 489 490SELECT * FROM t1; 491SELECT * FROM t2; 492 493--echo # The same test but with server restart before UPDATE. 494--echo # Triggers different code path in InnoDB which was not 495--echo # covered by original fix for the bug. 496 497--source include/restart_mysqld.inc 498UPDATE t1 SET fld1= 3; 499 500SELECT * FROM t1; 501SELECT * FROM t2; 502 503DROP TABLE t2; 504DROP TABLE t1; 505 506 507--echo # 508--echo # Bug#20021917: WORK AROUND FOR CHARSET CONVERSION WITH FKS CAN 509--echo # RESULT IN WRONG DATA 510--echo # 511 512CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) UNIQUE); 513 514CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), fname VARCHAR(100), FOREIGN KEY (fname) REFERENCES t1 (name) ON UPDATE CASCADE ON DELETE CASCADE); 515 516SHOW CREATE TABLE t1; 517SHOW CREATE TABLE t2; 518 519--error ER_FK_INCOMPATIBLE_COLUMNS 520ALTER TABLE t2 CONVERT TO CHARACTER SET latin1; 521 522SET foreign_key_checks= OFF; 523 524ALTER TABLE t2 CONVERT TO CHARACTER SET latin1; 525 526SET foreign_key_checks= ON; 527SHOW CREATE TABLE t1; 528SHOW CREATE TABLE t2; 529 530INSERT INTO t1(name) VALUES ('test1'); 531INSERT INTO t2(name, fname) VALUES ('test1', 'test1'); 532UPDATE t1 SET name=CONCAT('St', UNHEX('C3A5') ,'le') WHERE name = 'test1'; 533 534--echo # Should not get any rows here 535SELECT t1.name, t2.fname FROM t1, t2 WHERE t1.name <> t2.fname; 536 537DROP TABLE t2; 538DROP TABLE t1; 539 540 541--echo # 542--echo # WL#6049: Meta-data locking for FOREIGN KEY tables 543--echo # 544--echo # Test case to check MDL on tables related by FK constraints. 545--echo # 546 547--enable_connect_log 548CREATE TABLE grandparent (gpf1 INT PRIMARY KEY, gpf2 INT); 549 550INSERT INTO grandparent VALUES (1,10), (2,20); 551 552CREATE TABLE parent ( 553 pf1 INT PRIMARY KEY, pf2 INT, sleep_dummy INT, 554 CONSTRAINT pc1 FOREIGN KEY (pf2) REFERENCES grandparent (gpf1) 555 ON DELETE NO ACTION ON UPDATE NO ACTION); 556 557INSERT INTO parent VALUES (1,1,0), (2,2,0); 558 559CREATE TABLE child ( 560 cf1 INT PRIMARY KEY, cf2 INT, 561 CONSTRAINT cc1 FOREIGN KEY (cf2) REFERENCES parent (pf1) 562 ON DELETE NO ACTION ON UPDATE NO ACTION); 563 564INSERT INTO child VALUES (1,1), (2,2); 565 566--connect (con_A,localhost,root,,test) 567--let $conA=`SELECT CONNECTION_ID()` 568SET @@session.lock_wait_timeout= 1; 569--send UPDATE parent SET pf2= 2, sleep_dummy= SLEEP(2) 570 571--connection default 572--replace_result $conA <conA> 573--eval set @conA_id = $conA 574let $wait_condition= 575 SELECT COUNT(*) = 1 576 FROM information_schema.processlist 577 WHERE id = @conA_id AND state LIKE 'user sleep'; 578--echo # Waiting for connection A to start udpate 579--source include/wait_condition.inc 580 581SET @@session.lock_wait_timeout= 1; 582 583--echo # Updates not invoving the FK related column should succeed 584UPDATE grandparent SET gpf2= 4; 585UPDATE grandparent SET gpf2= 100 * gpf1; 586 587--echo # DDL on child will have conflicting locks. 588--error ER_LOCK_WAIT_TIMEOUT 589ALTER TABLE child ADD COLUMN (i INT); 590SET @@session.lock_wait_timeout= DEFAULT; 591 592--connection con_A 593--reap 594--disconnect con_A 595--source include/wait_until_disconnected.inc 596 597--connection default 598--disable_connect_log 599 600DROP TABLE child; 601DROP TABLE parent; 602DROP TABLE grandparent; 603 604 605--echo # 606--echo # Systemic test coverage for metadata locks related to foreign 607--echo # keys acquired by various DDL statements. 608--echo # 609--echo # Also provides coverage for data-dictionary cache invalidation 610--echo # and cases when we fail to acquire necessary locks. 611 612--enable_connect_log 613SET @old_lock_wait_timeout= @@lock_wait_timeout; 614connect (con1, localhost, root,,); 615connect (con2, localhost, root,,); 616SET @old_lock_wait_timeout= @@lock_wait_timeout; 617connection default; 618 619--echo # 620--echo # 1) CREATE TABLE 621--echo # 622 623--echo # 1.1) CREATE TABLE must acquire X lock on parent table 624--echo # (if table doesn't exist). 625CREATE TABLE parent (pk INT PRIMARY KEY); 626 627connection con1; 628BEGIN; 629SELECT * FROM parent; 630 631connection default; 632--send CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 633 634connection con1; 635--echo # Wait until CREATE TABLE is blocked by con1 and then resume it. 636let $wait_condition= 637 SELECT COUNT(*) = 1 FROM information_schema.processlist 638 WHERE state = "Waiting for table metadata lock" AND 639 info LIKE "CREATE TABLE child%"; 640--source include/wait_condition.inc 641COMMIT; 642 643connection default; 644--echo # Reap CREATE TABLE 645--reap 646 647--echo # 1.2) CREATE TABLE for existing table should not acquire X lock 648--echo # parent table 649connection con1; 650BEGIN; 651SELECT * FROM parent; 652 653connection default; 654CREATE TABLE IF NOT EXISTS child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 655 656connection con1; 657COMMIT; 658 659connection default; 660DROP TABLE child; 661 662--echo # 1.3) CREATE TABLE which acquires lock on parent table and fails 663--echo # due to lock timeout. 664connection con1; 665BEGIN; 666SELECT * FROM parent; 667 668connection default; 669SET @@lock_wait_timeout= 1; 670--error ER_LOCK_WAIT_TIMEOUT 671CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 672SET @@lock_wait_timeout= @old_lock_wait_timeout; 673 674connection con1; 675COMMIT; 676 677connection default; 678DROP TABLE parent; 679 680--echo # 1.4) CREATE TABLE which adds parent to orphan child must acquire X 681--echo # on child table. 682 683SET FOREIGN_KEY_CHECKS=0; 684CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 685SET FOREIGN_KEY_CHECKS=1; 686 687connection con1; 688BEGIN; 689SELECT * FROM child; 690 691connection default; 692--send CREATE TABLE parent (pk INT PRIMARY KEY); 693 694connection con1; 695--echo # Wait until CREATE TABLE is blocked by con1 and then resume it. 696let $wait_condition= 697 SELECT COUNT(*) = 1 FROM information_schema.processlist 698 WHERE state = "Waiting for table metadata lock" AND 699 info LIKE "CREATE TABLE parent%"; 700--source include/wait_condition.inc 701COMMIT; 702 703connection default; 704--echo # Reap CREATE TABLE 705--reap 706 707--echo # 1.5) CREATE TABLE for existing table must not acquire X lock 708--echo # on child table 709connection con1; 710BEGIN; 711SELECT * FROM child; 712 713connection default; 714CREATE TABLE IF NOT EXISTS parent (pk INT PRIMARY KEY); 715 716connection con1; 717COMMIT; 718 719connection default; 720SET FOREIGN_KEY_CHECKS=0; 721DROP TABLE parent; 722SET FOREIGN_KEY_CHECKS=1; 723 724--echo # 1.6) CREATE TABLE which acquires lock on child table and fails 725--echo # due to lock timeout. 726connection con1; 727BEGIN; 728SELECT * FROM child; 729 730connection default; 731SET @@lock_wait_timeout= 1; 732--error ER_LOCK_WAIT_TIMEOUT 733CREATE TABLE parent (pk INT PRIMARY KEY); 734SET @@lock_wait_timeout= @old_lock_wait_timeout; 735 736connection con1; 737COMMIT; 738 739connection default; 740DROP TABLE child; 741 742--echo # 1.7) CREATE TABLE which adds FK should invalidate entries 743--echo # in TC/TDC and DD caches for the parent table. 744CREATE TABLE parent (pk INT PRIMARY KEY); 745SELECT * FROM parent; 746 747connection con1; 748CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE); 749 750connection default; 751LOCK TABLE parent WRITE; 752 753connection con1; 754--echo # The above LOCK TABLE should have noticed new table definition 755--echo # and acquired SNRW lock on child table. 756--send SELECT * FROM child; 757 758connection default; 759--echo # Wait until SELECT is blocked by default connection and then resume it. 760let $wait_condition= 761 SELECT COUNT(*) = 1 FROM information_schema.processlist 762 WHERE state = "Waiting for table metadata lock" AND 763 info LIKE "SELECT * FROM child"; 764--source include/wait_condition.inc 765UNLOCK TABLES; 766 767connection con1; 768--echo # Reap SELECT 769--reap 770 771connection default; 772DROP TABLES child, parent; 773 774 775--echo # 776--echo # 2) CREATE TABLE ... LIKE 777--echo # 778 779--echo # 2.1) CREATE TABLE ... LIKE doesn't copy foreign keys 780--echo # so it should not any locks on FK parent tables. 781CREATE TABLE parent (pk INT PRIMARY KEY); 782CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 783 784connection con1; 785LOCK TABLES parent WRITE; 786 787connection default; 788CREATE TABLE child2 LIKE child; 789 790connection con1; 791UNLOCK TABLES; 792 793connection default; 794 795--echo # 2.2) CREATE TABLE LIKE which adds parent to orphan child must 796--echo # acquire X on child table. 797SET FOREIGN_KEY_CHECKS=0; 798DROP TABLES child2, parent; 799SET FOREIGN_KEY_CHECKS=1; 800CREATE TABLE parent_source (pk INT PRIMARY KEY); 801 802connection con1; 803BEGIN; 804SELECT * FROM child; 805 806connection default; 807--send CREATE TABLE parent LIKE parent_source; 808 809connection con1; 810--echo # Wait until CREATE TABLE is blocked by con1 and then resume it. 811let $wait_condition= 812 SELECT COUNT(*) = 1 FROM information_schema.processlist 813 WHERE state = "Waiting for table metadata lock" AND 814 info LIKE "CREATE TABLE parent%"; 815--source include/wait_condition.inc 816COMMIT; 817 818connection default; 819--echo # Reap CREATE TABLE 820--reap 821 822--echo # 2.3) CREATE TABLE LIKE for existing table must not acquire X lock 823--echo # on child table 824connection con1; 825BEGIN; 826SELECT * FROM child; 827 828connection default; 829CREATE TABLE IF NOT EXISTS parent LIKE parent_source; 830 831connection con1; 832COMMIT; 833 834connection default; 835SET FOREIGN_KEY_CHECKS=0; 836DROP TABLE parent; 837SET FOREIGN_KEY_CHECKS=1; 838 839--echo # 2.4) CREATE TABLE LIKE which acquires lock on child table and fails 840--echo # due to lock timeout. 841connection con1; 842BEGIN; 843SELECT * FROM child; 844 845connection default; 846SET @@lock_wait_timeout= 1; 847--error ER_LOCK_WAIT_TIMEOUT 848CREATE TABLE IF NOT EXISTS parent LIKE parent_source; 849SET @@lock_wait_timeout= @old_lock_wait_timeout; 850 851connection con1; 852COMMIT; 853 854connection default; 855DROP TABLE child, parent_source; 856 857 858--echo # 859--echo # 3) CREATE TABLE ... SELECT 860--echo # 861 862--echo # 3.1) CREATE TABLE ... SELECT must start by acquiring SU lock on parent 863--echo # table (if table doesn't exist). 864CREATE TABLE parent (pk INT PRIMARY KEY); 865CREATE TABLE source (fk INT); 866INSERT INTO source VALUES (NULL); 867 868connection con1; 869BEGIN; 870SELECT * FROM source FOR UPDATE; 871 872connection default; 873SET @saved_binlog_format= @@SESSION.binlog_format; 874SET @@SESSION.binlog_format=STATEMENT; 875--send CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT fk FROM source; 876 877connection con1; 878--echo # Wait until CREATE TABLE is blocked by con1 because of row locks. 879let $wait_condition= 880 SELECT COUNT(*) = 1 FROM information_schema.processlist 881 WHERE state = "System lock" AND 882 info LIKE "CREATE TABLE child%"; 883--source include/wait_condition.inc 884 885--echo # Demonstrate that CREATE TABLE holds SU lock on parent 886--echo # which allows DML but not DDL. 887INSERT INTO parent VALUES (1); 888 889connection con2; 890SET @@lock_wait_timeout= 1; 891--error ER_LOCK_WAIT_TIMEOUT 892ALTER TABLE parent ADD COLUMN a INT; 893SET @@lock_wait_timeout= @old_lock_wait_timeout; 894 895connection con1; 896--echo # Resume CREATE TABLE. 897COMMIT; 898 899connection default; 900--echo # Reap CREATE TABLE 901--reap 902SET SESSION binlog_format= @saved_binlog_format; 903 904DROP TABLES child, source; 905 906--echo # 3.2) CREATE TABLE ... SELECT should upgrade SU lock on parent to X 907--echo # before commit (i.e eventually X lock should be acquired). 908connection con1; 909BEGIN; 910SELECT * FROM parent; 911 912connection default; 913SET @@SESSION.binlog_format=STATEMENT; 914--send CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT NULL AS fk; 915 916connection con1; 917--echo # Wait until CREATE TABLE is blocked by con1 and then resume it. 918let $wait_condition= 919 SELECT COUNT(*) = 1 FROM information_schema.processlist 920 WHERE state = "Waiting for table metadata lock" AND 921 info LIKE "CREATE TABLE child%"; 922--source include/wait_condition.inc 923COMMIT; 924 925connection default; 926--echo # Reap CREATE TABLE 927--reap 928SET SESSION binlog_format= @saved_binlog_format; 929 930--echo # 3.3) CREATE TABLE ... SELECT for existing table should not acquire 931--echo # any locks on parent table. 932connection con1; 933CREATE TABLE parent2 (pk INT PRIMARY KEY); 934LOCK TABLE parent2 WRITE; 935 936connection default; 937SET @@SESSION.binlog_format=STATEMENT; 938CREATE TABLE IF NOT EXISTS child (fk INT, FOREIGN KEY (fk) REFERENCES parent2(pk)) SELECT NULL AS fk; 939SET SESSION binlog_format= @saved_binlog_format; 940 941connection con1; 942UNLOCK TABLES; 943 944connection default; 945DROP TABLE child; 946DROP TABLE parent2; 947 948--echo # 3.4) CREATE TABLE ... SELECT which tries to acquire SU lock on parent 949--echo # table and fails due to lock timeout. 950connection con1; 951LOCK TABLE parent WRITE; 952 953connection default; 954SET @@lock_wait_timeout= 1; 955SET @@SESSION.binlog_format=STATEMENT; 956--error ER_LOCK_WAIT_TIMEOUT 957CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT NULL AS fk; 958SET SESSION binlog_format= @saved_binlog_format; 959SET @@lock_wait_timeout= @old_lock_wait_timeout; 960 961connection con1; 962UNLOCK TABLES; 963 964--echo # 3.5) CREATE TABLE ... SELECT which tries to upgrade to X lock on 965--echo # parent table and fails due to lock timeout. 966BEGIN; 967SELECT * FROM parent; 968 969connection default; 970SET @@lock_wait_timeout= 1; 971SET @@SESSION.binlog_format=STATEMENT; 972--error ER_LOCK_WAIT_TIMEOUT 973CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) SELECT NULL AS fk; 974SET SESSION binlog_format= @saved_binlog_format; 975SET @@lock_wait_timeout= @old_lock_wait_timeout; 976 977connection con1; 978COMMIT; 979 980connection default; 981DROP TABLE parent; 982 983--echo # 3.6) CREATE TABLE ... SELECT which adds parent to orphan child must 984--echo # acquire X on child table. 985 986SET FOREIGN_KEY_CHECKS=0; 987SET @@SESSION.binlog_format=STATEMENT; 988CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 989SET SESSION binlog_format= @saved_binlog_format; 990SET FOREIGN_KEY_CHECKS=1; 991 992connection con1; 993BEGIN; 994SELECT * FROM child; 995 996connection default; 997--send CREATE TABLE parent (pk INT PRIMARY KEY) SELECT 1 AS pk; 998 999connection con1; 1000--echo # Wait until CREATE TABLE is blocked by con1 and then resume it. 1001let $wait_condition= 1002 SELECT COUNT(*) = 1 FROM information_schema.processlist 1003 WHERE state = "Waiting for table metadata lock" AND 1004 info LIKE "CREATE TABLE parent%"; 1005--source include/wait_condition.inc 1006COMMIT; 1007 1008connection default; 1009--echo # Reap CREATE TABLE 1010--reap 1011 1012--echo # 3.7) CREATE TABLE ... SELECT for existing table must not 1013--echo # acquire X lock on child table 1014connection con1; 1015BEGIN; 1016SELECT * FROM child; 1017 1018connection default; 1019CREATE TABLE IF NOT EXISTS parent (pk INT PRIMARY KEY) SELECT 1 AS pk; 1020 1021connection con1; 1022COMMIT; 1023 1024connection default; 1025SET FOREIGN_KEY_CHECKS=0; 1026DROP TABLE parent; 1027SET FOREIGN_KEY_CHECKS=1; 1028 1029--echo # 3.8) CREATE TABLE ... SELECT which acquires lock on child table 1030--echo # and fails due to lock timeout. 1031connection con1; 1032BEGIN; 1033SELECT * FROM child; 1034 1035connection default; 1036SET @@lock_wait_timeout= 1; 1037--error ER_LOCK_WAIT_TIMEOUT 1038CREATE TABLE parent (pk INT PRIMARY KEY) SELECT 1 AS pk; 1039SET @@lock_wait_timeout= @old_lock_wait_timeout; 1040 1041connection con1; 1042COMMIT; 1043 1044connection default; 1045DROP TABLE child; 1046 1047--echo # 3.9) CREATE TABLE ... SELECT which adds FK should invalidate entries 1048--echo # in TC/TDC and DD caches for the parent table. 1049CREATE TABLE parent (pk INT PRIMARY KEY); 1050SELECT * FROM parent; 1051 1052connection con1; 1053SET @saved_binlog_format= @@SESSION.binlog_format; 1054SET @@SESSION.binlog_format=STATEMENT; 1055CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE) SELECT NULL AS fk; 1056SET SESSION binlog_format= @saved_binlog_format; 1057 1058connection default; 1059LOCK TABLE parent WRITE; 1060 1061connection con1; 1062--echo # The above LOCK TABLE should have noticed new table definition 1063--echo # and acquired SNRW lock on child table. 1064--send SELECT * FROM child; 1065 1066connection default; 1067--echo # Wait until SELECT is blocked by default connection and then resume it. 1068let $wait_condition= 1069 SELECT COUNT(*) = 1 FROM information_schema.processlist 1070 WHERE state = "Waiting for table metadata lock" AND 1071 info LIKE "SELECT * FROM child"; 1072--source include/wait_condition.inc 1073UNLOCK TABLES; 1074 1075connection con1; 1076--echo # Reap SELECT 1077--reap 1078 1079connection default; 1080DROP TABLES child, parent; 1081 1082 1083--echo # 1084--echo # 4) DROP TABLES 1085--echo # 1086 1087--echo # 4.1) DROP TABLES must acquire X lock on parent table for FKs 1088--echo # when child is dropped. 1089CREATE TABLE parent (pk INT PRIMARY KEY); 1090CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 1091 1092connection con1; 1093BEGIN; 1094SELECT * FROM parent; 1095 1096connection default; 1097--send DROP TABLES child; 1098 1099connection con1; 1100--echo # Wait until DROP TABLES is blocked by con1 and then resume it. 1101let $wait_condition= 1102 SELECT COUNT(*) = 1 FROM information_schema.processlist 1103 WHERE state = "Waiting for table metadata lock" AND 1104 info LIKE "DROP TABLES child"; 1105--source include/wait_condition.inc 1106COMMIT; 1107 1108connection default; 1109--echo # Reap DROP TABLES 1110--reap 1111 1112CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 1113 1114--echo # 4.2) DROP TABLES which acquires lock on parent table and fails 1115--echo # due to lock timeout. 1116connection con1; 1117BEGIN; 1118SELECT * FROM parent; 1119 1120connection default; 1121SET @@lock_wait_timeout= 1; 1122--error ER_LOCK_WAIT_TIMEOUT 1123DROP TABLES child; 1124SET @@lock_wait_timeout= @old_lock_wait_timeout; 1125 1126connection con1; 1127COMMIT; 1128 1129--echo # 4.3) DROP TABLES which tries to remove parent table must acquire X 1130--echo # lock on child table. 1131 1132connection con1; 1133BEGIN; 1134SELECT * FROM child; 1135 1136connection default; 1137--send DROP TABLES parent; 1138 1139connection con1; 1140--echo # Wait until DROP TABLES is blocked by con1 and then resume it. 1141let $wait_condition= 1142 SELECT COUNT(*) = 1 FROM information_schema.processlist 1143 WHERE state = "Waiting for table metadata lock" AND 1144 info LIKE "DROP TABLES parent"; 1145--source include/wait_condition.inc 1146COMMIT; 1147 1148connection default; 1149--echo # Reap DROP TABLES 1150--error ER_FK_CANNOT_DROP_PARENT 1151--reap 1152 1153--echo # 4.4) DROP TABLES which acquires lock on child table and fails 1154--echo # due to lock timeout. 1155connection con1; 1156BEGIN; 1157SELECT * FROM child; 1158 1159connection default; 1160SET @@lock_wait_timeout= 1; 1161--error ER_LOCK_WAIT_TIMEOUT 1162DROP TABLES parent; 1163SET @@lock_wait_timeout= @old_lock_wait_timeout; 1164 1165connection con1; 1166COMMIT; 1167 1168--echo # 4.5) DROP TABLES which deletes child should invalidate entries 1169--echo # in TC/TDC and DD caches for the parent table. 1170DROP TABLES child; 1171CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE); 1172SELECT * FROM parent; 1173 1174connection con1; 1175DROP TABLES child; 1176 1177connection default; 1178LOCK TABLE parent WRITE; 1179 1180connection con1; 1181--echo # The above LOCK TABLE should have noticed new table definition 1182--echo # and avoid acquiring SNRW lock on child table. 1183--error ER_NO_SUCH_TABLE 1184SELECT * FROM child; 1185 1186connection default; 1187UNLOCK TABLES; 1188 1189DROP TABLES parent; 1190 1191 1192--echo # 1193--echo # 5) RENAME TABLES 1194--echo # 1195 1196--echo # 5.1) RENAME TABLES must acquire X lock on parent table for FKs 1197--echo # when child is renamed. 1198CREATE TABLE parent (pk INT PRIMARY KEY); 1199CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 1200 1201connection con1; 1202BEGIN; 1203SELECT * FROM parent; 1204 1205connection default; 1206--send RENAME TABLES child TO child1; 1207 1208connection con1; 1209--echo # Wait until RENAME TABLES is blocked by con1 and then resume it. 1210let $wait_condition= 1211 SELECT COUNT(*) = 1 FROM information_schema.processlist 1212 WHERE state = "Waiting for table metadata lock" AND 1213 info LIKE "RENAME TABLES child%"; 1214--source include/wait_condition.inc 1215COMMIT; 1216 1217connection default; 1218--echo # Reap RENAME TABLES 1219--reap 1220 1221RENAME TABLES child1 TO child; 1222 1223--echo # 5.2) RENAME TABLES which acquires lock on parent table and fails 1224--echo # due to lock timeout. 1225connection con1; 1226BEGIN; 1227SELECT * FROM parent; 1228 1229connection default; 1230SET @@lock_wait_timeout= 1; 1231--error ER_LOCK_WAIT_TIMEOUT 1232RENAME TABLES child TO child1; 1233SET @@lock_wait_timeout= @old_lock_wait_timeout; 1234 1235connection con1; 1236COMMIT; 1237 1238--echo # 5.3) RENAME TABLES which tries to rename parent table must acquire X 1239--echo # lock on child table. 1240 1241connection con1; 1242BEGIN; 1243SELECT * FROM child; 1244 1245connection default; 1246--send RENAME TABLES parent TO parent1; 1247 1248connection con1; 1249--echo # Wait until RENAME TABLES is blocked by con1 and then resume it. 1250let $wait_condition= 1251 SELECT COUNT(*) = 1 FROM information_schema.processlist 1252 WHERE state = "Waiting for table metadata lock" AND 1253 info LIKE "RENAME TABLES parent%"; 1254--source include/wait_condition.inc 1255COMMIT; 1256 1257connection default; 1258--echo # Reap RENAME TABLES 1259--reap 1260 1261RENAME TABLES parent1 TO parent; 1262 1263--echo # 5.4) RENAME TABLES which acquires lock on child table and fails 1264--echo # due to lock timeout. 1265connection con1; 1266BEGIN; 1267SELECT * FROM child; 1268 1269connection default; 1270SET @@lock_wait_timeout= 1; 1271--error ER_LOCK_WAIT_TIMEOUT 1272RENAME TABLES parent TO parent1; 1273SET @@lock_wait_timeout= @old_lock_wait_timeout; 1274 1275connection con1; 1276COMMIT; 1277 1278--echo # 5.5) RENAME TABLES which adds parent table for orphan child 1279--echo # must acquire X lock on this child table. 1280connection default; 1281DROP TABLE child; 1282SET FOREIGN_KEY_CHECKS=0; 1283CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk)); 1284SET FOREIGN_KEY_CHECKS=1; 1285 1286connection con1; 1287BEGIN; 1288SELECT * FROM child; 1289 1290connection default; 1291--send RENAME TABLES parent TO parent1; 1292 1293connection con1; 1294--echo # Wait until RENAME TABLES is blocked by con1 and then resume it. 1295let $wait_condition= 1296 SELECT COUNT(*) = 1 FROM information_schema.processlist 1297 WHERE state = "Waiting for table metadata lock" AND 1298 info LIKE "RENAME TABLES parent%"; 1299--source include/wait_condition.inc 1300COMMIT; 1301 1302connection default; 1303--echo # Reap RENAME TABLES 1304--reap 1305 1306RENAME TABLES parent1 TO parent; 1307 1308--echo # 5.6) RENAME TABLES which acquires lock on orphan child table 1309--echo # and fails due to timeout. 1310DROP TABLE child; 1311SET FOREIGN_KEY_CHECKS=0; 1312CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk)); 1313SET FOREIGN_KEY_CHECKS=1; 1314 1315connection con1; 1316BEGIN; 1317SELECT * FROM child; 1318 1319connection default; 1320SET @@lock_wait_timeout= 1; 1321--error ER_LOCK_WAIT_TIMEOUT 1322RENAME TABLES parent TO parent1; 1323SET @@lock_wait_timeout= @old_lock_wait_timeout; 1324 1325connection con1; 1326COMMIT; 1327 1328connection default; 1329DROP TABLE child; 1330CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE); 1331 1332--echo # 5.7) RENAME TABLES on the child table should invalidate entries 1333--echo # in TC/TDC and DD caches for the parent table. 1334SELECT * FROM parent; 1335 1336connection con1; 1337RENAME TABLES child TO child1; 1338 1339connection default; 1340LOCK TABLE parent WRITE; 1341 1342connection con1; 1343--echo # The above LOCK TABLE should have noticed new table definition 1344--echo # and acquire SNRW lock on new child table name. 1345--send SELECT * FROM child1; 1346 1347connection default; 1348--echo # Wait until SELECT is blocked by default connection and then resume it. 1349let $wait_condition= 1350 SELECT COUNT(*) = 1 FROM information_schema.processlist 1351 WHERE state = "Waiting for table metadata lock" AND 1352 info LIKE "SELECT * FROM child1"; 1353--source include/wait_condition.inc 1354UNLOCK TABLES; 1355 1356connection con1; 1357--echo # Reap SELECT 1358--reap 1359 1360connection default; 1361DROP TABLES child1, parent; 1362 1363 1364--echo # 1365--echo # 6) Simple ALTER TABLE ... RENAME 1366--echo # 1367 1368--echo # 6.1) ALTER TABLE ... RENAME must acquire X lock on parent table 1369--echo # for FKs when child is renamed. 1370CREATE TABLE parent (pk INT PRIMARY KEY); 1371CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 1372 1373connection con1; 1374BEGIN; 1375SELECT * FROM parent; 1376 1377connection default; 1378--send ALTER TABLE child RENAME TO child1; 1379 1380connection con1; 1381--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1382let $wait_condition= 1383 SELECT COUNT(*) = 1 FROM information_schema.processlist 1384 WHERE state = "Waiting for table metadata lock" AND 1385 info LIKE "ALTER TABLE child%"; 1386--source include/wait_condition.inc 1387COMMIT; 1388 1389connection default; 1390--echo # Reap ALTER TABLE 1391--reap 1392 1393RENAME TABLES child1 TO child; 1394 1395--echo # 6.2) ALTER TABLE ... RENAME which acquires lock on parent table and 1396--echo # fails due to lock timeout. 1397connection con1; 1398BEGIN; 1399SELECT * FROM parent; 1400 1401connection default; 1402SET @@lock_wait_timeout= 1; 1403--error ER_LOCK_WAIT_TIMEOUT 1404ALTER TABLE child RENAME TO child1; 1405SET @@lock_wait_timeout= @old_lock_wait_timeout; 1406 1407connection con1; 1408COMMIT; 1409 1410--echo # 6.3) ALTER TABLE ... RENAME which tries to rename parent table must 1411--echo # acquire X lock on child table. 1412 1413connection con1; 1414BEGIN; 1415SELECT * FROM child; 1416 1417connection default; 1418--send ALTER TABLE parent RENAME TO parent1; 1419 1420connection con1; 1421--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1422let $wait_condition= 1423 SELECT COUNT(*) = 1 FROM information_schema.processlist 1424 WHERE state = "Waiting for table metadata lock" AND 1425 info LIKE "ALTER TABLE parent%"; 1426--source include/wait_condition.inc 1427COMMIT; 1428 1429connection default; 1430--echo # Reap ALTER TABLE 1431--reap 1432 1433RENAME TABLES parent1 TO parent; 1434 1435--echo # 6.4) ALTER TABLE ... RENAME which acquires lock on child table and 1436--echo # fails due to lock timeout. 1437connection con1; 1438BEGIN; 1439SELECT * FROM child; 1440 1441connection default; 1442SET @@lock_wait_timeout= 1; 1443--error ER_LOCK_WAIT_TIMEOUT 1444ALTER TABLE parent RENAME TO parent1; 1445SET @@lock_wait_timeout= @old_lock_wait_timeout; 1446 1447connection con1; 1448COMMIT; 1449 1450--echo # 6.5) ALTER TABLE ... RENAME which adds parent table for orphan child 1451--echo # must acquire X lock on this child table. 1452connection default; 1453DROP TABLE child; 1454SET FOREIGN_KEY_CHECKS=0; 1455CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk)); 1456SET FOREIGN_KEY_CHECKS=1; 1457 1458connection con1; 1459BEGIN; 1460SELECT * FROM child; 1461 1462connection default; 1463--send ALTER TABLE parent RENAME TO parent1; 1464 1465connection con1; 1466--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1467let $wait_condition= 1468 SELECT COUNT(*) = 1 FROM information_schema.processlist 1469 WHERE state = "Waiting for table metadata lock" AND 1470 info LIKE "ALTER TABLE parent%"; 1471--source include/wait_condition.inc 1472COMMIT; 1473 1474connection default; 1475--echo # Reap ALTER TABLE 1476--reap 1477 1478RENAME TABLES parent1 TO parent; 1479 1480--echo # 6.6) ALTER TABLE ... RENAME which acquires lock on orphan child table 1481--echo # and fails due to timeout. 1482DROP TABLE child; 1483SET FOREIGN_KEY_CHECKS=0; 1484CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk)); 1485SET FOREIGN_KEY_CHECKS=1; 1486 1487connection con1; 1488BEGIN; 1489SELECT * FROM child; 1490 1491connection default; 1492SET @@lock_wait_timeout= 1; 1493--error ER_LOCK_WAIT_TIMEOUT 1494ALTER TABLE parent RENAME TO parent1; 1495SET @@lock_wait_timeout= @old_lock_wait_timeout; 1496 1497connection con1; 1498COMMIT; 1499 1500connection default; 1501DROP TABLE child; 1502CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE); 1503 1504--echo # 6.7) ALTER TABLE ... RENAME on the child table should invalidate 1505--echo # entries in TC/TDC and DD caches for the parent table. 1506SELECT * FROM parent; 1507 1508connection con1; 1509ALTER TABLE child RENAME TO child1; 1510 1511connection default; 1512LOCK TABLE parent WRITE; 1513 1514connection con1; 1515--echo # The above LOCK TABLE should have noticed new table definition 1516--echo # and acquire SNRW lock on new child table name. 1517--send SELECT * FROM child1; 1518 1519connection default; 1520--echo # Wait until SELECT is blocked by default connection and then resume it. 1521let $wait_condition= 1522 SELECT COUNT(*) = 1 FROM information_schema.processlist 1523 WHERE state = "Waiting for table metadata lock" AND 1524 info LIKE "SELECT * FROM child1"; 1525--source include/wait_condition.inc 1526UNLOCK TABLES; 1527 1528connection con1; 1529--echo # Reap SELECT 1530--reap 1531 1532connection default; 1533DROP TABLES child1, parent; 1534 1535 1536--echo # 1537--echo # 7) ALTER TABLE ... INPLACE 1538--echo # 1539 1540--echo # 7.1) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE must start by 1541--echo # acquiring SU lock on parent table. 1542 1543--echo # 1544--echo # This test uses debug_sync feature so resides in foreign_key_debug.test 1545--echo # 1546 1547--echo # 7.2) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE should upgrade SU 1548--echo # lock on parent to X before commit (i.e eventually X lock should 1549--echo # be acquired). 1550CREATE TABLE parent (pk INT PRIMARY KEY); 1551CREATE TABLE child (fk INT); 1552 1553connection con1; 1554BEGIN; 1555SELECT * FROM parent; 1556 1557connection default; 1558SET FOREIGN_KEY_CHECKS=0; 1559--send ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=INPLACE; 1560 1561connection con1; 1562--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1563let $wait_condition= 1564 SELECT COUNT(*) = 1 FROM information_schema.processlist 1565 WHERE state = "Waiting for table metadata lock" AND 1566 info LIKE "ALTER TABLE child%"; 1567--source include/wait_condition.inc 1568COMMIT; 1569 1570connection default; 1571--echo # Reap ALTER TABLE 1572--reap 1573SET FOREIGN_KEY_CHECKS=1; 1574 1575ALTER TABLE child DROP FOREIGN KEY fk; 1576 1577--echo # 7.3) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE which tries to 1578--echo # acquire SU lock on parent table and fails due to lock timeout. 1579connection con1; 1580LOCK TABLE parent WRITE; 1581 1582connection default; 1583SET @@lock_wait_timeout= 1; 1584--error ER_LOCK_WAIT_TIMEOUT 1585ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=INPLACE; 1586SET @@lock_wait_timeout= @old_lock_wait_timeout; 1587 1588connection con1; 1589UNLOCK TABLES; 1590 1591--echo # 7.4) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE which tries to 1592--echo # upgrade to X lock on parent table and fails due to lock timeout. 1593BEGIN; 1594SELECT * FROM parent; 1595 1596connection default; 1597SET @@lock_wait_timeout= 1; 1598SET FOREIGN_KEY_CHECKS=0; 1599--error ER_LOCK_WAIT_TIMEOUT 1600ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=INPLACE; 1601SET FOREIGN_KEY_CHECKS=1; 1602SET @@lock_wait_timeout= @old_lock_wait_timeout; 1603 1604connection con1; 1605COMMIT; 1606 1607--echo # 7.5) ALTER TABLE ... ADD FOREIGN KEY ... INPLACE should invalidate 1608--echo # entries in TC/TDC and DD caches for the parent table. 1609SELECT * FROM parent; 1610 1611connection con1; 1612SET FOREIGN_KEY_CHECKS=0; 1613ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE, ALGORITHM=INPLACE; 1614SET FOREIGN_KEY_CHECKS=1; 1615 1616connection default; 1617LOCK TABLE parent WRITE; 1618 1619connection con1; 1620--echo # The above LOCK TABLE should have noticed new table definition 1621--echo # and acquired SNRW lock on child table. 1622--send SELECT * FROM child; 1623 1624connection default; 1625--echo # Wait until SELECT is blocked by default connection and then resume it. 1626let $wait_condition= 1627 SELECT COUNT(*) = 1 FROM information_schema.processlist 1628 WHERE state = "Waiting for table metadata lock" AND 1629 info LIKE "SELECT * FROM child"; 1630--source include/wait_condition.inc 1631UNLOCK TABLES; 1632 1633connection con1; 1634--echo # Reap SELECT 1635--reap 1636 1637--echo # 7.6) ALTER TABLE ... DROP FOREIGN KEY ... INPLACE should acquire 1638--echo # lock on parent to X before commit. 1639 1640connection con1; 1641BEGIN; 1642SELECT * FROM parent; 1643 1644connection default; 1645--send ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=INPLACE; 1646 1647connection con1; 1648--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1649let $wait_condition= 1650 SELECT COUNT(*) = 1 FROM information_schema.processlist 1651 WHERE state = "Waiting for table metadata lock" AND 1652 info LIKE "ALTER TABLE child%"; 1653--source include/wait_condition.inc 1654COMMIT; 1655 1656connection default; 1657--echo # Reap ALTER TABLE 1658--reap 1659 1660ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE; 1661 1662--echo # 7.7) ALTER TABLE ... DROP FOREIGN KEY ... INPLACE which tries to 1663--echo # upgrade to X lock on parent table and fails due to lock timeout. 1664connection con1; 1665BEGIN; 1666SELECT * FROM parent; 1667 1668connection default; 1669SET @@lock_wait_timeout= 1; 1670--error ER_LOCK_WAIT_TIMEOUT 1671ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=INPLACE; 1672SET @@lock_wait_timeout= @old_lock_wait_timeout; 1673 1674connection con1; 1675COMMIT; 1676 1677--echo # 7.8) ALTER TABLE ... DROP FOREIGN KEY ... INPLACE should invalidate entries 1678--echo # in TC/TDC and DD caches for the parent table. 1679SELECT * FROM parent; 1680 1681connection con1; 1682ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=INPLACE; 1683 1684connection default; 1685LOCK TABLE parent WRITE; 1686 1687connection con1; 1688--echo # The above LOCK TABLE should have noticed new table definition 1689--echo # and avoid acquiring SNRW lock on child table. 1690SELECT * FROM child; 1691 1692connection default; 1693UNLOCK TABLES; 1694 1695 1696--echo # 7.9) ALTER TABLE ... INPLACE which changes parent key must acquire X 1697--echo # lock on child table. 1698DROP TABLES child, parent; 1699CREATE TABLE parent (pk INT NOT NULL, UNIQUE u(pk)); 1700CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 1701 1702connection con1; 1703BEGIN; 1704SELECT * FROM child; 1705 1706connection default; 1707--send ALTER TABLE parent RENAME KEY u TO u1, ALGORITHM=INPLACE; 1708 1709connection con1; 1710--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1711let $wait_condition= 1712 SELECT COUNT(*) = 1 FROM information_schema.processlist 1713 WHERE state = "Waiting for table metadata lock" AND 1714 info LIKE "ALTER TABLE parent%"; 1715--source include/wait_condition.inc 1716COMMIT; 1717 1718connection default; 1719--echo # Reap ALTER TABLE 1720--reap 1721 1722--echo # 7.10) ALTER TABLE ... INPLACE which changes parent key, so tries to 1723--echo # acquire X lock on child table, but fails due to timeout. 1724connection con1; 1725BEGIN; 1726SELECT * FROM child; 1727 1728connection default; 1729SET @@lock_wait_timeout= 1; 1730--error ER_LOCK_WAIT_TIMEOUT 1731ALTER TABLE parent RENAME KEY u1 TO u, ALGORITHM=INPLACE; 1732SET @@lock_wait_timeout= @old_lock_wait_timeout; 1733 1734connection con1; 1735COMMIT; 1736 1737--echo # 7.11) ALTER TABLE ... RENAME ... INPLACE must acquire X lock on 1738--echo # parent table for FKs when child is renamed. 1739 1740connection con1; 1741BEGIN; 1742SELECT * FROM parent; 1743 1744connection default; 1745--send ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=INPLACE; 1746 1747connection con1; 1748--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1749let $wait_condition= 1750 SELECT COUNT(*) = 1 FROM information_schema.processlist 1751 WHERE state = "Waiting for table metadata lock" AND 1752 info LIKE "ALTER TABLE child%"; 1753--source include/wait_condition.inc 1754COMMIT; 1755 1756connection default; 1757--echo # Reap ALTER TABLE 1758--reap 1759 1760RENAME TABLES child1 TO child; 1761 1762--echo # 7.12) ALTER TABLE ... RENAME ... INPLACE which acquires lock on 1763--echo # parent table and fails due to lock timeout. 1764connection con1; 1765BEGIN; 1766SELECT * FROM parent; 1767 1768connection default; 1769SET @@lock_wait_timeout= 1; 1770--error ER_LOCK_WAIT_TIMEOUT 1771ALTER TABLE child RENAME TO child1, ADD COLUMN b INT, ALGORITHM=INPLACE; 1772SET @@lock_wait_timeout= @old_lock_wait_timeout; 1773 1774connection con1; 1775COMMIT; 1776 1777--echo # 7.13) ALTER TABLE ... RENAME ... INPLACE which tries to rename parent 1778--echo # table must acquire X lock on child table. 1779 1780connection con1; 1781BEGIN; 1782SELECT * FROM child; 1783 1784connection default; 1785--send ALTER TABLE parent RENAME TO parent1, ADD COLUMN a INT, ALGORITHM=INPLACE; 1786 1787connection con1; 1788--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1789let $wait_condition= 1790 SELECT COUNT(*) = 1 FROM information_schema.processlist 1791 WHERE state = "Waiting for table metadata lock" AND 1792 info LIKE "ALTER TABLE parent%"; 1793--source include/wait_condition.inc 1794COMMIT; 1795 1796connection default; 1797--echo # Reap ALTER TABLE 1798--reap 1799 1800RENAME TABLES parent1 TO parent; 1801 1802--echo # 7.14) ALTER TABLE ... RENAME ... INPLACE which acquires lock on child 1803--echo # table and fails due to lock timeout. 1804connection con1; 1805BEGIN; 1806SELECT * FROM child; 1807 1808connection default; 1809SET @@lock_wait_timeout= 1; 1810--error ER_LOCK_WAIT_TIMEOUT 1811ALTER TABLE parent RENAME TO parent1, ADD COLUMN b INT, ALGORITHM=INPLACE; 1812SET @@lock_wait_timeout= @old_lock_wait_timeout; 1813 1814connection con1; 1815COMMIT; 1816 1817--echo # 7.15) ALTER TABLE ... RENAME ... INPLACE which adds parent table for 1818--echo # orphan child must acquire X lock on this child table. 1819connection default; 1820DROP TABLE child; 1821SET FOREIGN_KEY_CHECKS=0; 1822CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk)); 1823SET FOREIGN_KEY_CHECKS=1; 1824 1825connection con1; 1826BEGIN; 1827SELECT * FROM child; 1828 1829connection default; 1830--send ALTER TABLE parent RENAME TO parent1, ADD COLUMN c INT, ALGORITHM=INPLACE; 1831 1832connection con1; 1833--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1834let $wait_condition= 1835 SELECT COUNT(*) = 1 FROM information_schema.processlist 1836 WHERE state = "Waiting for table metadata lock" AND 1837 info LIKE "ALTER TABLE parent%"; 1838--source include/wait_condition.inc 1839COMMIT; 1840 1841connection default; 1842--echo # Reap ALTER TABLE 1843--reap 1844 1845RENAME TABLES parent1 TO parent; 1846 1847--echo # 7.16) ALTER TABLE ... RENAME ... INPLACE which acquires lock on 1848--echo # orphan child table and fails due to timeout. 1849DROP TABLE child; 1850SET FOREIGN_KEY_CHECKS=0; 1851CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk)); 1852SET FOREIGN_KEY_CHECKS=1; 1853 1854connection con1; 1855BEGIN; 1856SELECT * FROM child; 1857 1858connection default; 1859SET @@lock_wait_timeout= 1; 1860--error ER_LOCK_WAIT_TIMEOUT 1861ALTER TABLE parent RENAME TO parent1, ADD COLUMN d INT, ALGORITHM=INPLACE; 1862SET @@lock_wait_timeout= @old_lock_wait_timeout; 1863 1864connection con1; 1865COMMIT; 1866 1867connection default; 1868DROP TABLE child; 1869CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE); 1870 1871--echo # 7.17) ALTER TABLE ... RENAME ... INPLACE on the child table should 1872--echo # invalidate entries in TC/TDC and DD caches for the parent table. 1873SELECT * FROM parent; 1874 1875connection con1; 1876ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=INPLACE; 1877 1878connection default; 1879LOCK TABLE parent WRITE; 1880 1881connection con1; 1882--echo # The above LOCK TABLE should have noticed new table definition 1883--echo # and acquire SNRW lock on new child table name. 1884--send SELECT * FROM child1; 1885 1886connection default; 1887--echo # Wait until SELECT is blocked by default connection and then resume it. 1888let $wait_condition= 1889 SELECT COUNT(*) = 1 FROM information_schema.processlist 1890 WHERE state = "Waiting for table metadata lock" AND 1891 info LIKE "SELECT * FROM child1"; 1892--source include/wait_condition.inc 1893UNLOCK TABLES; 1894 1895connection con1; 1896--echo # Reap SELECT 1897--reap 1898 1899connection default; 1900DROP TABLES child1, parent; 1901 1902 1903--echo # 1904--echo # 8) ALTER TABLE ... COPY 1905--echo # 1906 1907--echo # 8.1) ALTER TABLE ... ADD FOREIGN KEY ... COPY must start by 1908--echo # acquiring SU lock on parent table. 1909 1910--echo # 8.1') ALTER TABLE ... ADD FOREIGN KEY ... COPY due to workaround 1911--echo # must upgrade SU lock on parent table SRO lock. 1912 1913--echo # 1914--echo # These tests use debug_sync feature so reside in foreign_key_debug.test 1915--echo # 1916 1917--echo # 8.2) ALTER TABLE ... ADD FOREIGN KEY ... COPY should upgrade SU (SRO) 1918--echo # lock on parent to X before commit (i.e eventually X lock should 1919--echo # be acquired). 1920CREATE TABLE parent (pk INT PRIMARY KEY); 1921CREATE TABLE child (fk INT); 1922 1923connection con1; 1924BEGIN; 1925SELECT * FROM parent; 1926 1927connection default; 1928--send ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY; 1929 1930connection con1; 1931--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 1932let $wait_condition= 1933 SELECT COUNT(*) = 1 FROM information_schema.processlist 1934 WHERE state = "Waiting for table metadata lock" AND 1935 info LIKE "ALTER TABLE child%"; 1936--source include/wait_condition.inc 1937COMMIT; 1938 1939connection default; 1940--echo # Reap ALTER TABLE 1941--reap 1942 1943ALTER TABLE child DROP FOREIGN KEY fk; 1944 1945--echo # 8.3) ALTER TABLE ... ADD FOREIGN KEY ... COPY which tries to 1946--echo # acquire SU lock on parent table and fails due to lock timeout. 1947connection con1; 1948LOCK TABLE parent WRITE; 1949 1950connection default; 1951SET @@lock_wait_timeout= 1; 1952--error ER_LOCK_WAIT_TIMEOUT 1953ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY; 1954SET @@lock_wait_timeout= @old_lock_wait_timeout; 1955 1956connection con1; 1957UNLOCK TABLES; 1958 1959--echo # 8.3') ALTER TABLE ... ADD FOREIGN KEY ... COPY which due to workaround 1960--echo # tries to upgrade SU lock on parent table to SRO and fails due to 1961--echo # lock timeout. 1962connection con1; 1963BEGIN; 1964DELETE FROM parent; 1965 1966connection default; 1967SET @@lock_wait_timeout= 1; 1968--error ER_LOCK_WAIT_TIMEOUT 1969ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY; 1970SET @@lock_wait_timeout= @old_lock_wait_timeout; 1971 1972connection con1; 1973COMMIT; 1974 1975--echo # 8.4) ALTER TABLE ... ADD FOREIGN KEY ... COPY which tries to 1976--echo # upgrade to X lock on parent table and fails due to lock timeout. 1977BEGIN; 1978SELECT * FROM parent; 1979 1980connection default; 1981SET @@lock_wait_timeout= 1; 1982--error ER_LOCK_WAIT_TIMEOUT 1983ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk), ALGORITHM=COPY; 1984SET @@lock_wait_timeout= @old_lock_wait_timeout; 1985 1986connection con1; 1987COMMIT; 1988 1989--echo # 8.5) ALTER TABLE ... ADD FOREIGN KEY ... COPY should invalidate 1990--echo # entries in TC/TDC and DD caches for the parent table. 1991SELECT * FROM parent; 1992 1993connection con1; 1994ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE, ALGORITHM=COPY; 1995 1996connection default; 1997LOCK TABLE parent WRITE; 1998 1999connection con1; 2000--echo # The above LOCK TABLE should have noticed new table definition 2001--echo # and acquired SNRW lock on child table. 2002--send SELECT * FROM child; 2003 2004connection default; 2005--echo # Wait until SELECT is blocked by default connection and then resume it. 2006let $wait_condition= 2007 SELECT COUNT(*) = 1 FROM information_schema.processlist 2008 WHERE state = "Waiting for table metadata lock" AND 2009 info LIKE "SELECT * FROM child"; 2010--source include/wait_condition.inc 2011UNLOCK TABLES; 2012 2013connection con1; 2014--echo # Reap SELECT 2015--reap 2016 2017--echo # 8.6) ALTER TABLE ... DROP FOREIGN KEY ... COPY should acquire 2018--echo # lock on parent to X before commit. 2019 2020connection con1; 2021BEGIN; 2022SELECT * FROM parent; 2023 2024connection default; 2025--send ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=COPY; 2026 2027connection con1; 2028--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 2029let $wait_condition= 2030 SELECT COUNT(*) = 1 FROM information_schema.processlist 2031 WHERE state = "Waiting for table metadata lock" AND 2032 info LIKE "ALTER TABLE child%"; 2033--source include/wait_condition.inc 2034COMMIT; 2035 2036connection default; 2037--echo # Reap ALTER TABLE 2038--reap 2039 2040ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE; 2041 2042--echo # 8.7) ALTER TABLE ... DROP FOREIGN KEY ... COPY which tries to 2043--echo # upgrade to X lock on parent table and fails due to lock timeout. 2044connection con1; 2045BEGIN; 2046SELECT * FROM parent; 2047 2048connection default; 2049SET @@lock_wait_timeout= 1; 2050--error ER_LOCK_WAIT_TIMEOUT 2051ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=COPY; 2052SET @@lock_wait_timeout= @old_lock_wait_timeout; 2053 2054connection con1; 2055COMMIT; 2056 2057--echo # 8.8) ALTER TABLE ... DROP FOREIGN KEY ... COPY should invalidate entries 2058--echo # in TC/TDC and DD caches for the parent table. 2059SELECT * FROM parent; 2060 2061connection con1; 2062ALTER TABLE child DROP FOREIGN KEY fk, ALGORITHM=COPY; 2063 2064connection default; 2065LOCK TABLE parent WRITE; 2066 2067connection con1; 2068--echo # The above LOCK TABLE should have noticed new table definition 2069--echo # and avoid acquiring SNRW lock on child table. 2070SELECT * FROM child; 2071 2072connection default; 2073UNLOCK TABLES; 2074 2075 2076--echo # 8.9) ALTER TABLE ... COPY which changes parent key must acquire X 2077--echo # lock on child table. 2078DROP TABLES child, parent; 2079CREATE TABLE parent (pk INT NOT NULL, UNIQUE u(pk)); 2080CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 2081 2082connection con1; 2083BEGIN; 2084SELECT * FROM child; 2085 2086connection default; 2087--send ALTER TABLE parent RENAME KEY u TO u1, ALGORITHM=COPY; 2088 2089connection con1; 2090--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 2091let $wait_condition= 2092 SELECT COUNT(*) = 1 FROM information_schema.processlist 2093 WHERE state = "Waiting for table metadata lock" AND 2094 info LIKE "ALTER TABLE parent%"; 2095--source include/wait_condition.inc 2096COMMIT; 2097 2098connection default; 2099--echo # Reap ALTER TABLE 2100--reap 2101 2102--echo # 8.10) ALTER TABLE ... COPY which changes parent key, so tries to 2103--echo # acquire X lock on child table, but fails due to timeout. 2104connection con1; 2105BEGIN; 2106SELECT * FROM child; 2107 2108connection default; 2109SET @@lock_wait_timeout= 1; 2110--error ER_LOCK_WAIT_TIMEOUT 2111ALTER TABLE parent RENAME KEY u1 TO u, ALGORITHM=COPY; 2112SET @@lock_wait_timeout= @old_lock_wait_timeout; 2113 2114connection con1; 2115COMMIT; 2116 2117--echo # 8.11) ALTER TABLE ... RENAME ... COPY must acquire X lock on 2118--echo # parent table for FKs when child is renamed. 2119 2120connection con1; 2121BEGIN; 2122SELECT * FROM parent; 2123 2124connection default; 2125--send ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=COPY; 2126 2127connection con1; 2128--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 2129let $wait_condition= 2130 SELECT COUNT(*) = 1 FROM information_schema.processlist 2131 WHERE state = "Waiting for table metadata lock" AND 2132 info LIKE "ALTER TABLE child%"; 2133--source include/wait_condition.inc 2134COMMIT; 2135 2136connection default; 2137--echo # Reap ALTER TABLE 2138--reap 2139 2140RENAME TABLES child1 TO child; 2141 2142--echo # 8.12) ALTER TABLE ... RENAME ... COPY which acquires lock on 2143--echo # parent table and fails due to lock timeout. 2144connection con1; 2145BEGIN; 2146SELECT * FROM parent; 2147 2148connection default; 2149SET @@lock_wait_timeout= 1; 2150--error ER_LOCK_WAIT_TIMEOUT 2151ALTER TABLE child RENAME TO child1, ADD COLUMN b INT, ALGORITHM=COPY; 2152SET @@lock_wait_timeout= @old_lock_wait_timeout; 2153 2154connection con1; 2155COMMIT; 2156 2157--echo # 8.13) ALTER TABLE ... RENAME ... COPY which tries to rename parent 2158--echo # table must acquire X lock on child table. 2159 2160connection con1; 2161BEGIN; 2162SELECT * FROM child; 2163 2164connection default; 2165--send ALTER TABLE parent RENAME TO parent1, ADD COLUMN a INT, ALGORITHM=COPY; 2166 2167connection con1; 2168--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 2169let $wait_condition= 2170 SELECT COUNT(*) = 1 FROM information_schema.processlist 2171 WHERE state = "Waiting for table metadata lock" AND 2172 info LIKE "ALTER TABLE parent%"; 2173--source include/wait_condition.inc 2174COMMIT; 2175 2176connection default; 2177--echo # Reap ALTER TABLE 2178--reap 2179 2180RENAME TABLES parent1 TO parent; 2181 2182--echo # 8.14) ALTER TABLE ... RENAME ... COPY which acquires lock on child 2183--echo # table and fails due to lock timeout. 2184connection con1; 2185BEGIN; 2186SELECT * FROM child; 2187 2188connection default; 2189SET @@lock_wait_timeout= 1; 2190--error ER_LOCK_WAIT_TIMEOUT 2191ALTER TABLE parent RENAME TO parent1, ADD COLUMN b INT, ALGORITHM=COPY; 2192SET @@lock_wait_timeout= @old_lock_wait_timeout; 2193 2194connection con1; 2195COMMIT; 2196 2197--echo # 8.15) ALTER TABLE ... RENAME ... COPY which adds parent table for 2198--echo # orphan child must acquire X lock on this child table. 2199connection default; 2200DROP TABLE child; 2201SET FOREIGN_KEY_CHECKS=0; 2202CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk)); 2203SET FOREIGN_KEY_CHECKS=1; 2204 2205connection con1; 2206BEGIN; 2207SELECT * FROM child; 2208 2209connection default; 2210--send ALTER TABLE parent RENAME TO parent1, ADD COLUMN c INT, ALGORITHM=COPY; 2211 2212connection con1; 2213--echo # Wait until ALTER TABLE is blocked by con1 and then resume it. 2214let $wait_condition= 2215 SELECT COUNT(*) = 1 FROM information_schema.processlist 2216 WHERE state = "Waiting for table metadata lock" AND 2217 info LIKE "ALTER TABLE parent%"; 2218--source include/wait_condition.inc 2219COMMIT; 2220 2221connection default; 2222--echo # Reap ALTER TABLE 2223--reap 2224 2225RENAME TABLES parent1 TO parent; 2226 2227--echo # 8.16) ALTER TABLE ... RENAME ... COPY which acquires lock on 2228--echo # orphan child table and fails due to timeout. 2229DROP TABLE child; 2230SET FOREIGN_KEY_CHECKS=0; 2231CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent1(pk)); 2232SET FOREIGN_KEY_CHECKS=1; 2233 2234connection con1; 2235BEGIN; 2236SELECT * FROM child; 2237 2238connection default; 2239SET @@lock_wait_timeout= 1; 2240--error ER_LOCK_WAIT_TIMEOUT 2241ALTER TABLE parent RENAME TO parent1, ADD COLUMN d INT, ALGORITHM=COPY; 2242SET @@lock_wait_timeout= @old_lock_wait_timeout; 2243 2244connection con1; 2245COMMIT; 2246 2247connection default; 2248DROP TABLE child; 2249CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE CASCADE); 2250 2251--echo # 8.17) ALTER TABLE ... RENAME ... COPY on the child table should 2252--echo # invalidate entries in TC/TDC and DD caches for the parent table. 2253SELECT * FROM parent; 2254 2255connection con1; 2256ALTER TABLE child RENAME TO child1, ADD COLUMN a INT, ALGORITHM=COPY; 2257 2258connection default; 2259LOCK TABLE parent WRITE; 2260 2261connection con1; 2262--echo # The above LOCK TABLE should have noticed new table definition 2263--echo # and acquire SNRW lock on new child table name. 2264--send SELECT * FROM child1; 2265 2266connection default; 2267--echo # Wait until SELECT is blocked by default connection and then resume it. 2268let $wait_condition= 2269 SELECT COUNT(*) = 1 FROM information_schema.processlist 2270 WHERE state = "Waiting for table metadata lock" AND 2271 info LIKE "SELECT * FROM child1"; 2272--source include/wait_condition.inc 2273UNLOCK TABLES; 2274 2275connection con1; 2276--echo # Reap SELECT 2277--reap 2278 2279connection default; 2280DROP TABLES child1, parent; 2281 2282connection con1; 2283disconnect con1; 2284--source include/wait_until_disconnected.inc 2285connection con2; 2286disconnect con2; 2287--source include/wait_until_disconnected.inc 2288connection default; 2289--disable_connect_log 2290 2291 2292--echo # 2293--echo # Validation of FK and referred column names. 2294--echo # 2295 2296--echo # Too long constraint name. 2297--error ER_TOO_LONG_IDENT 2298CREATE TABLE t (pk INTEGER PRIMARY KEY, fk_i INTEGER, 2299 CONSTRAINT xxxxxxxxx1xxxxxxxxx2xxxxxxxxx3xxxxxxxxx4xxxxxxxxx5xxxxxxxxx6xxxxx 2300 FOREIGN KEY (fk_i) REFERENCES x(x)); 2301 2302--echo # Too long column name. 2303--error ER_WRONG_COLUMN_NAME 2304CREATE TABLE t (pk INTEGER PRIMARY KEY, fk_i INTEGER, 2305 FOREIGN KEY (fk_i) REFERENCES x(xxxxxxxxx1xxxxxxxxx2xxxxxxxxx3xxxxxxxxx4xxxxxxxxx5xxxxxxxxx6xxxxx)); 2306 2307--echo # Column name having trailing space. 2308--error ER_WRONG_COLUMN_NAME 2309CREATE TABLE t (pk INTEGER PRIMARY KEY, fk_i INTEGER, 2310 FOREIGN KEY (fk_i) REFERENCES x(`x `)); 2311 2312--echo # 2313--echo # Test DROP of table with FKs under LOCK TABLES. 2314--echo # 2315 2316CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER, fk_i INTEGER, 2317 UNIQUE KEY parent_i_key(i), 2318 FOREIGN KEY (fk_i) REFERENCES parent(i)); 2319 2320CREATE TABLE child(pk INTEGER PRIMARY KEY, fk_i INTEGER, 2321 FOREIGN KEY (fk_i) REFERENCES parent(i)); 2322 2323SET @@session.foreign_key_checks= 1; 2324--echo # Drop only parent with FKC == 0. 2325LOCK TABLE parent WRITE; 2326--error ER_FK_CANNOT_DROP_PARENT 2327DROP TABLES parent; 2328UNLOCK TABLE; 2329 2330SET @@session.foreign_key_checks= 0; 2331--echo # Drop only parent. 2332LOCK TABLE parent WRITE; 2333DROP TABLES parent; 2334UNLOCK TABLE; 2335SET @@session.foreign_key_checks= 1; 2336 2337CREATE TABLE parent(pk INTEGER PRIMARY KEY, i INTEGER, 2338 UNIQUE KEY parent_i_key(i)); 2339 2340--echo # Drop both child and parent. 2341LOCK TABLES child WRITE, parent WRITE; 2342DROP TABLES child, parent; 2343UNLOCK TABLES; 2344 2345SET @@session.foreign_key_checks= DEFAULT; 2346 2347--echo # 2348--echo # Test ALTER TABLE ... ADD FOREIGN KEY under LOCK TABLES. 2349--echo # 2350CREATE TABLE parent (pk INT PRIMARY KEY); 2351CREATE TABLE child (fk INT); 2352LOCK TABLES child WRITE; 2353--error ER_TABLE_NOT_LOCKED 2354ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk); 2355UNLOCK TABLES; 2356--echo # With parent table locked for read it should be possible to add FK. 2357LOCK TABLES child WRITE, parent READ; 2358ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk); 2359UNLOCK TABLES; 2360 2361--echo # 2362--echo # Test ALTER TABLE ... RENAME with FKs under LOCK TABLES. 2363--echo # 2364--echo # Bug 26647340 "LIFT LIMITATION ON ALTER TABLE RENAME + TABLES WITH 2365--echo # FOREIGN KEYS + LOCK TABLES". 2366--echo # 2367 2368--echo # Renaming of tables participating in FKs is allowed. 2369LOCK TABLES child WRITE; 2370ALTER TABLE child RENAME TO child1; 2371UNLOCK TABLES; 2372LOCK TABLES parent WRITE; 2373ALTER TABLE parent RENAME TO parent1; 2374UNLOCK TABLES; 2375 2376--echo # Check that it doesn't break FK invariants for LOCK TABLES. 2377LOCK TABLES child1 WRITE, parent1 WRITE; 2378ALTER TABLE child1 RENAME TO child; 2379INSERT INTO child VALUES (NULL); 2380DELETE FROM parent1; 2381UNLOCK TABLES; 2382LOCK TABLES child WRITE, parent1 WRITE; 2383ALTER TABLE parent1 RENAME TO parent; 2384INSERT INTO child VALUES (NULL); 2385UNLOCK TABLES; 2386 2387--echo # It is also allowed to add foreign key along with rename. 2388ALTER TABLE child DROP FOREIGN KEY fk; 2389LOCK TABLES child WRITE, parent WRITE; 2390ALTER TABLE child RENAME TO child1, ADD CONSTRAINT fk FOREIGN KEY (fk) REFERENCES parent(pk); 2391--echo # And FK invariants for LOCK TABLES are preserved too. 2392INSERT INTO child1 VALUES (NULL); 2393DELETE FROM parent; 2394UNLOCK TABLES; 2395DROP TABLE child1; 2396 2397--echo # We also allow renames which add parent for previously orphan FKs. 2398--echo # Provided that we have write lock on these children. 2399SET FOREIGN_KEY_CHECKS=0; 2400CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent1(pk) ON DELETE CASCADE); 2401SET FOREIGN_KEY_CHECKS=1; 2402LOCK TABLE parent WRITE; 2403--error ER_TABLE_NOT_LOCKED_FOR_WRITE 2404ALTER TABLE parent RENAME TO parent1; 2405UNLOCK TABLES; 2406LOCK TABLE parent WRITE, child READ; 2407--error ER_TABLE_NOT_LOCKED_FOR_WRITE 2408ALTER TABLE parent RENAME TO parent1; 2409UNLOCK TABLES; 2410LOCK TABLE parent WRITE, child WRITE; 2411ALTER TABLE parent RENAME TO parent1; 2412--echo # Invariants should be fine 2413INSERT INTO child VALUES (NULL); 2414DELETE FROM parent1; 2415UNLOCK TABLES; 2416DROP TABLES child, parent1; 2417 2418 2419--echo # 2420--echo # Bug #25722221 "RENAME COLUMN DID NOT UPDATE FOREIGN_KEY_COLUMN_USAGE 2421--echo # FOR FK CONSTRAINT". 2422--echo # Bug #26659110 "LIFT LIMITATION ON RENAMING PARENT COLUMNS WHICH ARE 2423--echo # REFERENCED BY FOREIGN KEYS". 2424--echo # 2425 2426CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT, 2427 FOREIGN KEY (fk) REFERENCES t1 (pk)); 2428SELECT constraint_name, table_name, column_name, referenced_column_name 2429 FROM information_schema.key_column_usage 2430 WHERE table_schema='test' AND table_name='t1'; 2431ALTER TABLE t1 CHANGE pk id INT; 2432SELECT constraint_name, table_name, column_name, referenced_column_name 2433 FROM information_schema.key_column_usage 2434 WHERE table_schema='test' AND table_name='t1'; 2435--echo # Renaming of parent columns using COPY algorithm is not supported. 2436--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 2437ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=COPY; 2438SELECT constraint_name, table_name, column_name, referenced_column_name 2439 FROM information_schema.key_column_usage 2440 WHERE table_schema='test' AND table_name='t1'; 2441ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=INPLACE; 2442SELECT constraint_name, table_name, column_name, referenced_column_name 2443 FROM information_schema.key_column_usage 2444 WHERE table_schema='test' AND table_name='t1'; 2445ALTER TABLE t1 RENAME COLUMN pk TO id; 2446SELECT constraint_name, table_name, column_name, referenced_column_name 2447 FROM information_schema.key_column_usage 2448 WHERE table_schema='test' AND table_name='t1'; 2449--echo # Renaming of parent columns using COPY algorithm is not supported. 2450--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 2451ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=COPY; 2452SELECT constraint_name, table_name, column_name, referenced_column_name 2453 FROM information_schema.key_column_usage 2454 WHERE table_schema='test' AND table_name='t1'; 2455ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=INPLACE; 2456SELECT constraint_name, table_name, column_name, referenced_column_name 2457 FROM information_schema.key_column_usage 2458 WHERE table_schema='test' AND table_name='t1'; 2459DROP TABLE t1; 2460 2461CREATE TABLE t1 (pk INT PRIMARY KEY); 2462CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk)); 2463SELECT constraint_name, table_name, column_name, referenced_column_name 2464 FROM information_schema.key_column_usage 2465 WHERE table_schema='test' AND table_name='t2'; 2466ALTER TABLE t1 CHANGE pk id INT; 2467SELECT constraint_name, table_name, column_name, referenced_column_name 2468 FROM information_schema.key_column_usage 2469 WHERE table_schema='test' AND table_name='t2'; 2470--echo # Renaming of parent columns using COPY algorithm is not supported. 2471--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 2472ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=COPY; 2473SELECT constraint_name, table_name, column_name, referenced_column_name 2474 FROM information_schema.key_column_usage 2475 WHERE table_schema='test' AND table_name='t2'; 2476ALTER TABLE t1 CHANGE id pk INT, ALGORITHM=INPLACE; 2477SELECT constraint_name, table_name, column_name, referenced_column_name 2478 FROM information_schema.key_column_usage 2479 WHERE table_schema='test' AND table_name='t2'; 2480ALTER TABLE t1 RENAME COLUMN pk TO id; 2481SELECT constraint_name, table_name, column_name, referenced_column_name 2482 FROM information_schema.key_column_usage 2483 WHERE table_schema='test' AND table_name='t2'; 2484--echo # Renaming of parent columns using COPY algorithm is not supported. 2485--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 2486ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=COPY; 2487SELECT constraint_name, table_name, column_name, referenced_column_name 2488 FROM information_schema.key_column_usage 2489 WHERE table_schema='test' AND table_name='t2'; 2490ALTER TABLE t1 RENAME COLUMN id TO pk, ALGORITHM=INPLACE; 2491SELECT constraint_name, table_name, column_name, referenced_column_name 2492 FROM information_schema.key_column_usage 2493 WHERE table_schema='test' AND table_name='t2'; 2494DROP TABLES t2, t1; 2495 2496--echo # Coverage for cases with multiple foreign keys. 2497CREATE TABLE t1 (pk INT PRIMARY KEY, u1 INT, u2 INT, fk1 INT, fk2 INT, 2498 UNIQUE (u1), UNIQUE (u2), 2499 FOREIGN KEY (fk1) REFERENCES t1 (u1), 2500 FOREIGN KEY (fk2) REFERENCES t1 (u2)); 2501SELECT constraint_name, table_name, column_name, referenced_column_name 2502 FROM information_schema.key_column_usage 2503 WHERE table_schema='test' AND table_name='t1'; 2504ALTER TABLE t1 RENAME COLUMN u1 TO u3; 2505SELECT constraint_name, table_name, column_name, referenced_column_name 2506 FROM information_schema.key_column_usage 2507 WHERE table_schema='test' AND table_name='t1'; 2508ALTER TABLE t1 RENAME COLUMN u3 TO u4, RENAME COLUMN u2 TO u5; 2509SELECT constraint_name, table_name, column_name, referenced_column_name 2510 FROM information_schema.key_column_usage 2511 WHERE table_schema='test' AND table_name='t1'; 2512DROP TABLE t1; 2513CREATE TABLE t1 (pk INT PRIMARY KEY, u1 INT, u2 INT, UNIQUE (u1), UNIQUE (u2)); 2514CREATE TABLE t2 (fk1 INT, fk2 INT, 2515 FOREIGN KEY (fk1) REFERENCES t1 (u1), 2516 FOREIGN KEY (fk2) REFERENCES t1 (u2)); 2517SELECT constraint_name, table_name, column_name, referenced_column_name 2518 FROM information_schema.key_column_usage 2519 WHERE table_schema='test' AND table_name='t2'; 2520ALTER TABLE t1 RENAME COLUMN u1 TO u3; 2521SELECT constraint_name, table_name, column_name, referenced_column_name 2522 FROM information_schema.key_column_usage 2523 WHERE table_schema='test' AND table_name='t2'; 2524ALTER TABLE t1 RENAME COLUMN u3 TO u4, RENAME COLUMN u2 TO u5; 2525SELECT constraint_name, table_name, column_name, referenced_column_name 2526 FROM information_schema.key_column_usage 2527 WHERE table_schema='test' AND table_name='t2'; 2528DROP TABLES t2, t1; 2529 2530 2531--echo # 2532--echo # WL#6049, bug#26654674 "TABLE_CACHE_MANAGER::FREE_TABLE(THD*, 2533--echo # ENUM_TDC_REMOVE_TABLE_TYPE, TABLE_SHARE*)". 2534--echo # 2535CREATE TABLE t1 (u INT NOT NULL, UNIQUE u(u)); 2536--echo # First, check that we correctly handle open HANDLERs during 2537--echo # parent invalidation. 2538HANDLER t1 OPEN; 2539CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (u)); 2540HANDLER t1 CLOSE; 2541HANDLER t1 OPEN AS a; 2542HANDLER t1 OPEN AS b; 2543CREATE TABLE t3 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (u)); 2544HANDLER a CLOSE; 2545HANDLER b CLOSE; 2546--echo # Then, check that we correctly handle HANDLERs on child table 2547--echo # during parent definiton change. 2548HANDLER t2 OPEN; 2549HANDLER t3 OPEN AS a; 2550HANDLER t3 OPEN AS b; 2551ALTER TABLE t1 RENAME KEY u TO u1; 2552HANDLER t2 CLOSE; 2553HANDLER a CLOSE; 2554HANDLER b CLOSE; 2555--echo # Now, let us check what happens when parent is renamed. 2556HANDLER t2 OPEN; 2557HANDLER t3 OPEN AS a; 2558HANDLER t3 OPEN AS b; 2559ALTER TABLE t1 RENAME TO t4; 2560HANDLER t2 CLOSE; 2561HANDLER a CLOSE; 2562HANDLER b CLOSE; 2563--echo # Finally, check what happens when parent table is deleted. 2564--echo # Do clean-up as side-effect. 2565HANDLER t2 OPEN; 2566HANDLER t3 OPEN AS a; 2567HANDLER t3 OPEN AS b; 2568SET FOREIGN_KEY_CHECKS=0; 2569DROP TABLE t4; 2570DROP TABLES t2, t3; 2571SET FOREIGN_KEY_CHECKS=1; 2572 2573 2574--echo # 2575--echo # Additional test coverage for foreign keys and prepared statement 2576--echo # validation. 2577--echo # 2578CREATE TABLE t1 (i INT PRIMARY KEY); 2579CREATE TABLE t2 (j INT, FOREIGN KEY (j) REFERENCES t1 (i) ON DELETE CASCADE); 2580CREATE TABLE t3 (k INT); 2581DELIMITER |; 2582CREATE TRIGGER bi_t3 BEFORE INSERT ON t3 FOR EACH ROW 2583BEGIN 2584IF @a = 1234567890 THEN 2585 DELETE FROM t1; 2586END IF; 2587END| 2588DELIMITER ;| 2589PREPARE stmt FROM 'INSERT INTO t3 VALUES (1)'; 2590EXECUTE stmt; 2591DROP TABLES t2, t1; 2592--echo # Statement should succeed even though we won't be able check 2593--echo # prelocked set element for child table. 2594EXECUTE stmt; 2595CREATE TABLE t1 (i INT PRIMARY KEY); 2596CREATE TABLE t2 (j INT, FOREIGN KEY (j) REFERENCES t1 (i) ON DELETE CASCADE); 2597EXECUTE stmt; 2598DROP TABLES t2, t1; 2599CREATE VIEW t2 AS SELECT 1 AS j; 2600--echo # Again statement should succeed, even though we have view instead 2601--echo # of child table. 2602EXECUTE stmt; 2603DEALLOCATE PREPARE stmt; 2604DROP TABLE t3; 2605DROP VIEW t2; 2606 2607 2608--echo # 2609--echo # Test for bug #27041477 "ASSERTION `HAS_PRELOCKING_LIST || 2610--echo # THD->MDL_CONTEXT.OWNS_EQUAL_OR_STRONGER_LOCK". 2611--echo # 2612CREATE TABLE t0 (i INT); 2613CREATE TRIGGER t0_bi BEFORE INSERT ON t0 FOR EACH ROW DELETE FROM t1; 2614CREATE TABLE t1 (pk INT PRIMARY KEY); 2615CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk) ON UPDATE SET NULL); 2616LOCK TABLE t1 READ; 2617--error ER_TABLE_NOT_LOCKED 2618DELETE FROM t1; 2619UNLOCK TABLES; 2620LOCK TABLES t0 WRITE; 2621--error ER_TABLE_NOT_LOCKED_FOR_WRITE 2622UPDATE t1 SET pk = 10; 2623UNLOCK TABLES; 2624DROP TABLES t2, t1, t0; 2625 2626 2627--echo # 2628--echo # Additional coverage for bug #25915132 "INPLACE ALTER TABLE WITH 2629--echo # FOREIGN KEYS CAUSES TABLE DEFINITION MISMATCH". 2630--echo # 2631 2632--echo # 2633--echo # Handling of foreign key names during various RENAME variants. 2634--echo # We check that table definition is updated correctly and that 2635--echo # asserts about MDL on foreign key names do not fail. 2636--echo # 2637CREATE TABLE t1 (pk INT PRIMARY KEY); 2638CREATE TABLE t2 (fk1 INT, fk2 INT, fk3 INT, 2639 CONSTRAINT a FOREIGN KEY (fk1) REFERENCES t1 (pk), 2640 CONSTRAINT t2_ibfk_1 FOREIGN KEY (fk2) REFERENCES t1 (pk)); 2641ALTER TABLE t2 ADD FOREIGN KEY (fk3) REFERENCES t1 (pk); 2642CREATE SCHEMA mysqltest; 2643SHOW CREATE TABLE t2; 2644SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2645 2646--echo # Simple RENAME TABLE 2647RENAME TABLE t2 TO t3; 2648SHOW CREATE TABLE t3; 2649SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2650 2651--echo # Two cross database RENAME TABLE variants 2652RENAME TABLE t3 TO mysqltest.t3; 2653SHOW CREATE TABLE mysqltest.t3; 2654SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest'; 2655 2656RENAME TABLE mysqltest.t3 TO t4; 2657SHOW CREATE TABLE t4; 2658SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2659 2660--echo # Simple ALTER TABLE RENAME variants. 2661ALTER TABLE t4 RENAME TO t5; 2662SHOW CREATE TABLE t5; 2663SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2664 2665ALTER TABLE t5 RENAME TO mysqltest.t5; 2666SHOW CREATE TABLE mysqltest.t5; 2667SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest'; 2668 2669ALTER TABLE mysqltest.t5 RENAME TO t6; 2670SHOW CREATE TABLE t6; 2671SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2672 2673--echo # ALTER TABLE INPLACE with RENAME clause. 2674ALTER TABLE t6 ADD COLUMN i INT, RENAME TO t7, ALGORITHM=INPLACE; 2675SHOW CREATE TABLE t7; 2676SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2677 2678ALTER TABLE t7 ADD COLUMN j INT, RENAME TO mysqltest.t7, ALGORITHM=INPLACE; 2679SHOW CREATE TABLE mysqltest.t7; 2680SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest'; 2681 2682ALTER TABLE mysqltest.t7 ADD COLUMN k INT, RENAME TO t8, ALGORITHM=INPLACE; 2683SHOW CREATE TABLE t8; 2684SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2685 2686--echo # ALTER TABLE COPY with RENAME clause. 2687ALTER TABLE t8 ADD COLUMN l INT, RENAME TO t9, ALGORITHM=COPY; 2688SHOW CREATE TABLE t9; 2689SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2690 2691ALTER TABLE t9 ADD COLUMN m INT, RENAME TO mysqltest.t9, ALGORITHM=COPY; 2692SHOW CREATE TABLE mysqltest.t9; 2693SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest'; 2694 2695ALTER TABLE mysqltest.t9 ADD COLUMN n INT, RENAME TO t10, ALGORITHM=COPY; 2696SHOW CREATE TABLE t10; 2697SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2698 2699--echo # ALTER TABLE INPLACE + ADD FOREIGN KEY with RENAME clause. 2700SET FOREIGN_KEY_CHECKS=0; 2701ALTER TABLE t10 ADD FOREIGN KEY (i) REFERENCES t1 (pk), 2702 ADD CONSTRAINT t10_ibfk_4 FOREIGN KEY (j) REFERENCES t1 (pk), 2703 RENAME TO t11, ALGORITHM=INPLACE; 2704SHOW CREATE TABLE t11; 2705SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2706 2707ALTER TABLE t11 ADD FOREIGN KEY (k) REFERENCES test.t1 (pk), 2708 ADD CONSTRAINT t11_ibfk_6 FOREIGN KEY (l) REFERENCES test.t1 (pk), 2709 RENAME TO mysqltest.t11, ALGORITHM=INPLACE; 2710SHOW CREATE TABLE mysqltest.t11; 2711SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest'; 2712 2713ALTER TABLE mysqltest.t11 ADD FOREIGN KEY (m) REFERENCES test.t1 (pk), 2714 ADD CONSTRAINT t12_ibfk_8 FOREIGN KEY (n) REFERENCES test.t1 (pk), 2715 RENAME TO t12, ALGORITHM=INPLACE; 2716SHOW CREATE TABLE t12; 2717SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2718SET FOREIGN_KEY_CHECKS=1; 2719 2720--echo # ALTER TABLE COPY + ADD FOREIGN KEY with RENAME clause. 2721ALTER TABLE t12 ADD COLUMN o INT, ADD COLUMN p INT, 2722 ADD FOREIGN KEY (o) REFERENCES t1 (pk), 2723 ADD CONSTRAINT t12_ibfk_10 FOREIGN KEY (p) REFERENCES t1 (pk), 2724 RENAME TO t13, ALGORITHM=COPY; 2725SHOW CREATE TABLE t13; 2726SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2727 2728ALTER TABLE t13 ADD COLUMN q INT, ADD COLUMN r INT, 2729 ADD FOREIGN KEY (q) REFERENCES test.t1 (pk), 2730 ADD CONSTRAINT t13_ibfk_12 FOREIGN KEY (r) REFERENCES test.t1 (pk), 2731 RENAME TO mysqltest.t13, ALGORITHM=COPY; 2732SHOW CREATE TABLE mysqltest.t13; 2733SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='mysqltest'; 2734 2735 2736ALTER TABLE mysqltest.t13 ADD COLUMN s INT, ADD COLUMN t INT, 2737 ADD FOREIGN KEY (s) REFERENCES test.t1 (pk), 2738 ADD CONSTRAINT t13_ibfk_14 FOREIGN KEY (t) REFERENCES test.t1 (pk), 2739 RENAME TO t14, ALGORITHM=COPY; 2740SHOW CREATE TABLE t14; 2741SELECT * FROM information_schema.referential_constraints WHERE constraint_schema='test'; 2742 2743DROP TABLE t14; 2744 2745--echo # 2746--echo # Detection of duplicate foreign key names by various forms of ALTER 2747--echo # TABLE. 2748--echo # 2749CREATE TABLE t2 (fk INT, CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk)); 2750CREATE TABLE t3 (pk INT PRIMARY KEY, fk INT, u INT); 2751INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 1); 2752 2753--echo # 2754--echo # ALTER TABLE INPLACE 2755--echo # 2756SET FOREIGN_KEY_CHECKS=0; 2757--echo # Duplicate FK name should be detected before we start addition 2758--echo # of unique key and report its violation. 2759--error ER_FK_DUP_NAME 2760ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk), 2761 ADD UNIQUE KEY (u), ALGORITHM=INPLACE; 2762--echo # Even for generated names. 2763CREATE TABLE t4 (fk INT, CONSTRAINT t3_ibfk_1 FOREIGN KEY (fk) REFERENCES t1 (pk)); 2764--error ER_FK_DUP_NAME 2765ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk), 2766 ADD UNIQUE KEY (u), ALGORITHM=INPLACE; 2767 2768--echo # There should not be conflicting foreign keys before main phase 2769--echo # of ALTER INPLACE even if at the end RENAME clause will remove 2770--echo # conflict. 2771--error ER_FK_DUP_NAME 2772ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk), 2773 ADD UNIQUE KEY (u), RENAME TO mysqltest.t3, 2774 ALGORITHM=INPLACE; 2775--error ER_FK_DUP_NAME 2776ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk), 2777 ADD UNIQUE KEY (u), RENAME TO t5, 2778 ALGORITHM=INPLACE; 2779 2780--echo # Now let us check that we detect potential conflicts resulting 2781--echo # from application of RENAME clause, before ALTER INPLACE main 2782--echo # phase as well. 2783CREATE TABLE mysqltest.t5 (fk INT, 2784 CONSTRAINT d FOREIGN KEY (fk) REFERENCES test.t1 (pk)); 2785CREATE TABLE t6 (fk INT, CONSTRAINT t8_ibfk_1 FOREIGN KEY (fk) REFERENCES test.t1 (pk)); 2786CREATE TABLE mysqltest.t6 (fk INT, 2787 CONSTRAINT t8_ibfk_1 FOREIGN KEY (fk) REFERENCES test.t1 (pk)); 2788DROP TABLE t4; 2789--error ER_FK_DUP_NAME 2790ALTER TABLE t3 ADD CONSTRAINT d FOREIGN KEY (fk) REFERENCES t1 (pk), 2791 ADD UNIQUE KEY (u), RENAME TO mysqltest.t3, 2792 ALGORITHM=INPLACE; 2793--error ER_FK_DUP_NAME 2794ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk), 2795 ADD UNIQUE KEY (u), RENAME TO t8, 2796 ALGORITHM=INPLACE; 2797--error ER_FK_DUP_NAME 2798ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk), 2799 ADD UNIQUE KEY (u), RENAME TO mysqltest.t8, 2800 ALGORITHM=INPLACE; 2801SET FOREIGN_KEY_CHECKS=1; 2802 2803--echo # 2804--echo # ALTER TABLE COPY 2805--echo # 2806--echo # Duplicate FK name should be detected before we start copying 2807--echo # of rows and report unique/FK constraint violation. 2808--error ER_FK_DUP_NAME 2809ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk), 2810 ADD UNIQUE KEY (u), ALGORITHM=COPY; 2811--echo # Even for generated names. 2812CREATE TABLE t4 (fk INT, CONSTRAINT t3_ibfk_1 FOREIGN KEY (fk) REFERENCES t1 (pk)); 2813--error ER_FK_DUP_NAME 2814ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk), 2815 ADD UNIQUE KEY (u), ALGORITHM=COPY; 2816 2817--echo # Unlike in INPLACE case, COPY algorithm creates FKs right 2818--echo # away in schema targeted by RENAME clause. So it doesn't 2819--echo # matter if constraint with the same name exists in the 2820--echo # table's original schema. 2821SET FOREIGN_KEY_CHECKS=0; 2822--error ER_DUP_ENTRY 2823ALTER TABLE t3 ADD CONSTRAINT c FOREIGN KEY (fk) REFERENCES t1 (pk), 2824 ADD UNIQUE KEY (u), RENAME TO mysqltest.t3, 2825 ALGORITHM=COPY; 2826SET FOREIGN_KEY_CHECKS=1; 2827 2828--echo # Updating of generated names still happens at the end of ALTER, 2829--echo # so there should not be conflicting foreign keys for generated 2830--echo # names for both old and new table name. 2831--error ER_FK_DUP_NAME 2832ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES t1 (pk), 2833 ADD UNIQUE KEY (u), RENAME TO t5, 2834 ALGORITHM=COPY; 2835 2836--echo # Check that we detect potential conflicts resulting from 2837--echo # application of RENAME clause early. 2838DROP TABLE t4; 2839--error ER_FK_DUP_NAME 2840ALTER TABLE t3 ADD CONSTRAINT d FOREIGN KEY (fk) REFERENCES test.t1 (pk), 2841 ADD UNIQUE KEY (u), RENAME TO mysqltest.t3, 2842 ALGORITHM=COPY; 2843--error ER_FK_DUP_NAME 2844ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES test.t1 (pk), 2845 ADD UNIQUE KEY (u), RENAME TO t8, 2846 ALGORITHM=COPY; 2847--error ER_FK_DUP_NAME 2848ALTER TABLE t3 ADD FOREIGN KEY (fk) REFERENCES test.t1 (pk), 2849 ADD UNIQUE KEY (u), RENAME TO mysqltest.t8, 2850 ALGORITHM=COPY; 2851 2852DROP SCHEMA mysqltest; 2853DROP TABLES t6, t3, t2, t1; 2854 2855--echo # 2856--echo # Initial version of the fix triggered asserts in the below statements. 2857--echo # 2858CREATE TABLE t1 (pk INT PRIMARY KEY); 2859CREATE TABLE T2 (fk INT); 2860ALTER TABLE T2 ADD FOREIGN KEY (fk) REFERENCES t1 (pk); 2861RENAME TABLE T2 TO T3; 2862DROP TABLES T3, t1; 2863 2864 2865--echo # 2866--echo # Bug #27821060 "NEWDD FK: DROP TABLES/DATABASE SHOULD CHECK FOR FKS". 2867--echo # 2868 2869--echo # 2870--echo # 1) Attempt to DROP TABLE which serves as parent in FK without dropping 2871--echo # child should fail with nice error message. 2872CREATE TABLE t1 (pk INT PRIMARY KEY); 2873CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk)); 2874--error ER_FK_CANNOT_DROP_PARENT 2875DROP TABLE t1; 2876 2877--echo # 2878--echo # 2) However, same should be allowed in FOREIGN_KEY_CHECKS=0 mode. 2879SET FOREIGN_KEY_CHECKS=0; 2880DROP TABLE t1; 2881SET FOREIGN_KEY_CHECKS=1; 2882 2883--echo # 2884--echo # 3) Dropping of parent table along with child table should be allowed. 2885--echo # Even if parent precedes child in table list. 2886CREATE TABLE t1 (pk INT PRIMARY KEY); 2887DROP TABLES t1, t2; 2888 2889--echo # 2890--echo # 4) Even if FKs form circular dependencies. 2891CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT); 2892CREATE TABLE t2 (pk INT PRIMARY KEY, fk INT, 2893 FOREIGN KEY(fk) REFERENCES t1 (pk)); 2894ALTER TABLE t1 ADD FOREIGN KEY (fk) REFERENCES t2 (pk); 2895DROP TABLES t1, t2; 2896 2897--echo # 2898--echo # 5) Attempt to DROP SCHEMA which will remove parent without 2899--echo # removing child should fail with nice error message. 2900CREATE SCHEMA mysqltest; 2901CREATE TABLE mysqltest.t1 (pk INT PRIMARY KEY); 2902CREATE TABLE t2 (fk INT, FOREIGN KEY(fk) REFERENCES mysqltest.t1 (pk)); 2903--error ER_FK_CANNOT_DROP_PARENT 2904DROP SCHEMA mysqltest; 2905 2906--echo # 2907--echo # 6) But the same should be allowed in FOREIGN_KEY_CHECKS=0 mode. 2908SET FOREIGN_KEY_CHECKS=0; 2909DROP SCHEMA mysqltest; 2910SET FOREIGN_KEY_CHECKS=1; 2911DROP TABLE t2; 2912 2913--echo # 2914--echo # 7) Also dropping schema which drops both parent and child 2915--echo # should be OK. 2916CREATE SCHEMA mysqltest; 2917USE mysqltest; 2918CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT); 2919CREATE TABLE t2 (pk INT PRIMARY KEY, fk INT, 2920 FOREIGN KEY(fk) REFERENCES t1 (pk)); 2921ALTER TABLE t1 ADD FOREIGN KEY (fk) REFERENCES t2 (pk); 2922USE test; 2923DROP SCHEMA mysqltest; 2924 2925 2926--echo # 2927--echo # Bug#27506922 "DROPPING OF PARENT KEY FOR FOREIGN KEY IS ALLOWED". 2928--echo # 2929 2930--echo # 2931--echo # 1) CREATE TABLE with FK and no parent key. 2932--echo # 2933CREATE TABLE parent(pk INT PRIMARY KEY, a INT); 2934--error ER_FK_NO_INDEX_PARENT 2935CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(a)); 2936SET FOREIGN_KEY_CHECKS = 0; 2937--error ER_FK_NO_INDEX_PARENT 2938CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(a)); 2939SET FOREIGN_KEY_CHECKS = 1; 2940--error ER_FK_NO_INDEX_PARENT 2941CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk INT, 2942 FOREIGN KEY (fk) REFERENCES self(a)); 2943SET FOREIGN_KEY_CHECKS = 0; 2944--error ER_FK_NO_INDEX_PARENT 2945CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk INT, 2946 FOREIGN KEY (fk) REFERENCES self(a)); 2947--echo # Missing parent table case. 2948SET FOREIGN_KEY_CHECKS = 1; 2949--error ER_FK_CANNOT_OPEN_PARENT, 2950CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES no_such_parent(pk)); 2951--echo # We allow creation of orphan FKs in FOREIGN_KEY_CHECKS = 0 mode. 2952SET FOREIGN_KEY_CHECKS = 0; 2953CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES no_such_parent(pk)); 2954SET FOREIGN_KEY_CHECKS = 1; 2955DROP TABLE child; 2956 2957--echo # 2958--echo # 2) ALTER TABLE which adds FK without parent key. 2959--echo # 2960CREATE TABLE child (fk INT, fk2 INT); 2961--error ER_FK_NO_INDEX_PARENT 2962ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=COPY; 2963--error ER_FK_NO_INDEX_PARENT 2964ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=INPLACE; 2965SET FOREIGN_KEY_CHECKS = 0; 2966--error ER_FK_NO_INDEX_PARENT 2967ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=COPY; 2968--error ER_FK_NO_INDEX_PARENT 2969ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(a), ALGORITHM=INPLACE; 2970SET FOREIGN_KEY_CHECKS = 1; 2971CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk INT); 2972--error ER_FK_NO_INDEX_PARENT 2973ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=COPY; 2974--error ER_FK_NO_INDEX_PARENT 2975ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=INPLACE; 2976SET FOREIGN_KEY_CHECKS = 0; 2977--error ER_FK_NO_INDEX_PARENT 2978ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=COPY; 2979--error ER_FK_NO_INDEX_PARENT 2980ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(a), ALGORITHM=INPLACE; 2981--echo # Missing parent table case. 2982SET FOREIGN_KEY_CHECKS = 1; 2983--error ER_FK_CANNOT_OPEN_PARENT, 2984ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES no_such_parent(pk), ALGORITHM=COPY; 2985--error ER_FK_CANNOT_OPEN_PARENT, 2986ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES no_such_parent(pk), ALGORITHM=INPLACE; 2987--echo # We allow creation of orphan FKs in FOREIGN_KEY_CHECKS = 0 mode. 2988SET FOREIGN_KEY_CHECKS = 0; 2989ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES no_such_parent(pk), ALGORITHM=COPY; 2990ALTER TABLE child ADD FOREIGN KEY (fk2) REFERENCES no_such_parent(pk), ALGORITHM=INPLACE; 2991SET FOREIGN_KEY_CHECKS = 1; 2992DROP TABLE child, self, parent; 2993 2994--echo # 2995--echo # 3) ALTER TABLE which drops parent key. 2996--echo # 2997CREATE TABLE parent (pk INT PRIMARY KEY, u INT NOT NULL, UNIQUE(u)); 2998CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(u)); 2999--error ER_DROP_INDEX_FK 3000ALTER TABLE parent DROP KEY u, ALGORITHM=COPY; 3001--error ER_DROP_INDEX_FK 3002ALTER TABLE parent DROP KEY u, ALGORITHM=INPLACE; 3003SET FOREIGN_KEY_CHECKS = 0; 3004--error ER_DROP_INDEX_FK 3005ALTER TABLE parent DROP KEY u, ALGORITHM=COPY; 3006--error ER_DROP_INDEX_FK 3007ALTER TABLE parent DROP KEY u, ALGORITHM=INPLACE; 3008SET FOREIGN_KEY_CHECKS = 1; 3009CREATE TABLE self (pk INT PRIMARY KEY, u INT NOT NULL, fk INT, UNIQUE(u), 3010 FOREIGN KEY (fk) REFERENCES self(u)); 3011--error ER_DROP_INDEX_FK 3012ALTER TABLE self DROP KEY u, ALGORITHM=COPY; 3013--error ER_DROP_INDEX_FK 3014ALTER TABLE self DROP KEY u, ALGORITHM=INPLACE; 3015SET FOREIGN_KEY_CHECKS = 0; 3016--error ER_DROP_INDEX_FK 3017ALTER TABLE self DROP KEY u, ALGORITHM=COPY; 3018--error ER_DROP_INDEX_FK 3019ALTER TABLE self DROP KEY u, ALGORITHM=INPLACE; 3020SET FOREIGN_KEY_CHECKS = 1; 3021--echo # Check case which requires additional handling during error-reporting. 3022--echo # Attempt to drop non-unique parent key. 3023ALTER TABLE parent DROP KEY u, ADD KEY nu(u); 3024--error ER_DROP_INDEX_FK 3025ALTER TABLE parent DROP KEY nu, ALGORITHM=COPY; 3026--error ER_DROP_INDEX_FK 3027ALTER TABLE parent DROP KEY nu, ALGORITHM=INPLACE; 3028SET FOREIGN_KEY_CHECKS = 0; 3029--error ER_DROP_INDEX_FK 3030ALTER TABLE parent DROP KEY nu, ALGORITHM=COPY; 3031--error ER_DROP_INDEX_FK 3032ALTER TABLE parent DROP KEY nu, ALGORITHM=INPLACE; 3033SET FOREIGN_KEY_CHECKS = 1; 3034ALTER TABLE self DROP KEY u, ADD KEY nu(u); 3035--error ER_DROP_INDEX_FK 3036ALTER TABLE self DROP KEY nu, ALGORITHM=COPY; 3037--error ER_DROP_INDEX_FK 3038ALTER TABLE self DROP KEY nu, ALGORITHM=INPLACE; 3039SET FOREIGN_KEY_CHECKS = 0; 3040--error ER_DROP_INDEX_FK 3041ALTER TABLE self DROP KEY nu, ALGORITHM=COPY; 3042--error ER_DROP_INDEX_FK 3043ALTER TABLE self DROP KEY nu, ALGORITHM=INPLACE; 3044SET FOREIGN_KEY_CHECKS = 1; 3045DROP TABLES self, child, parent; 3046 3047--echo # 3048--echo # 4) CREATE, RENAME and ALTER TABLE RENAME which create new 3049--echo # parent for previously orphan child table. 3050--echo # 3051SET FOREIGN_KEY_CHECKS = 0; 3052CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(a)); 3053SET FOREIGN_KEY_CHECKS = 1; 3054# We get more generic error message here because SE check kicks in 3055# before SQL-layer check. 3056--error ER_CANNOT_ADD_FOREIGN 3057CREATE TABLE parent (pk INT PRIMARY KEY, a INT); 3058SET FOREIGN_KEY_CHECKS = 0; 3059--error ER_FK_NO_INDEX_PARENT 3060CREATE TABLE parent (pk INT PRIMARY KEY, a INT); 3061SET FOREIGN_KEY_CHECKS = 1; 3062CREATE TABLE parent1 (pk INT PRIMARY KEY, a INT); 3063--error ER_ERROR_ON_RENAME 3064RENAME TABLE parent1 TO parent; 3065SET FOREIGN_KEY_CHECKS = 0; 3066--error ER_FK_NO_INDEX_PARENT 3067RENAME TABLE parent1 TO parent; 3068SET FOREIGN_KEY_CHECKS = 1; 3069--error ER_FK_NO_INDEX_PARENT 3070ALTER TABLE parent1 RENAME TO parent; 3071SET FOREIGN_KEY_CHECKS = 0; 3072--error ER_FK_NO_INDEX_PARENT 3073ALTER TABLE parent1 RENAME TO parent; 3074SET FOREIGN_KEY_CHECKS = 1; 3075--error ER_FK_NO_INDEX_PARENT 3076ALTER TABLE parent1 ADD COLUMN b INT, RENAME TO parent, ALGORITHM=INPLACE; 3077SET FOREIGN_KEY_CHECKS = 0; 3078--error ER_FK_NO_INDEX_PARENT 3079ALTER TABLE parent1 RENAME TO parent; 3080SET FOREIGN_KEY_CHECKS = 1; 3081--error ER_FK_NO_INDEX_PARENT 3082ALTER TABLE parent1 ADD COLUMN b INT, RENAME TO parent, ALGORITHM=COPY; 3083SET FOREIGN_KEY_CHECKS = 0; 3084--error ER_FK_NO_INDEX_PARENT 3085ALTER TABLE parent1 RENAME TO parent; 3086SET FOREIGN_KEY_CHECKS = 1; 3087DROP TABLE parent1, child; 3088 3089--echo # 3090--echo # 5) Special case. ALTER TABLE which replaces parent key with another 3091--echo # one due to new key creation. Old key is generated so it is 3092--echo # automagically replaced with a new explicit key or more universal 3093--echo # generated key. 3094--echo # 3095CREATE TABLE grandparent (pk INT PRIMARY KEY); 3096CREATE TABLE parent (pkfk INT, FOREIGN KEY (pkfk) REFERENCES grandparent(pk)); 3097SHOW CREATE TABLE parent; 3098CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pkfk)); 3099SELECT referenced_table_name, unique_constraint_name FROM 3100 information_schema.referential_constraints WHERE table_name = 'child'; 3101ALTER TABLE parent ADD UNIQUE KEY u (pkfk); 3102SHOW CREATE TABLE parent; 3103SELECT referenced_table_name, unique_constraint_name FROM 3104 information_schema.referential_constraints WHERE table_name = 'child'; 3105DROP TABLE child, parent; 3106 3107CREATE TABLE self (fk INT, pkfk INT, 3108 FOREIGN KEY (fk) REFERENCES self (pkfk), 3109 FOREIGN KEY (pkfk) REFERENCES grandparent(pk)); 3110SHOW CREATE TABLE self; 3111SELECT referenced_table_name, unique_constraint_name FROM 3112 information_schema.referential_constraints WHERE table_name = 'self' 3113 ORDER BY referenced_table_name; 3114ALTER TABLE self ADD UNIQUE KEY u (pkfk); 3115SHOW CREATE TABLE self; 3116SELECT referenced_table_name, unique_constraint_name FROM 3117 information_schema.referential_constraints WHERE table_name = 'self' 3118 ORDER BY referenced_table_name; 3119DROP TABLE self, grandparent; 3120 3121CREATE TABLE grandparent1 (pk INT PRIMARY KEY); 3122CREATE TABLE grandparent2 (pk1 INT , pk2 INT, PRIMARY KEY(pk1, pk2)); 3123CREATE TABLE parent (pkfk1 INT, pkfk2 INT, FOREIGN KEY (pkfk1) REFERENCES grandparent1(pk)); 3124SHOW CREATE TABLE parent; 3125CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pkfk1)); 3126SELECT referenced_table_name, unique_constraint_name FROM 3127 information_schema.referential_constraints WHERE table_name = 'child'; 3128ALTER TABLE parent ADD FOREIGN KEY (pkfk1, pkfk2) REFERENCES grandparent2(pk1, pk2); 3129SHOW CREATE TABLE parent; 3130SELECT referenced_table_name, unique_constraint_name FROM 3131 information_schema.referential_constraints WHERE table_name = 'child'; 3132DROP TABLE child, parent; 3133 3134CREATE TABLE self (fk INT, pkfk1 INT, pkfk2 INT, 3135 FOREIGN KEY (fk) REFERENCES self (pkfk1), 3136 FOREIGN KEY (pkfk1) REFERENCES grandparent1(pk)); 3137SHOW CREATE TABLE self; 3138SELECT referenced_table_name, unique_constraint_name FROM 3139 information_schema.referential_constraints WHERE table_name = 'self' 3140 ORDER BY referenced_table_name; 3141ALTER TABLE self ADD FOREIGN KEY (pkfk1, pkfk2) REFERENCES grandparent2(pk1, pk2); 3142SHOW CREATE TABLE self; 3143SELECT referenced_table_name, unique_constraint_name FROM 3144 information_schema.referential_constraints WHERE table_name = 'self' 3145 ORDER BY referenced_table_name; 3146DROP TABLE self, grandparent1, grandparent2; 3147 3148 3149--echo # 3150--echo # Bug#25722927 "NEWDD FK: ALTER TABLE CHANGE COLUMN TYPE SHOULD CHECK FK CONSTRAINT" 3151--echo # 3152 3153--echo # 3154--echo # 1) Check how missing/dropped referencing and referenced columns 3155--echo # are handled. 3156--echo # 3157 3158--echo # 3159--echo # 1.a) Missing referencing column. This problem is detected 3160--echo # during generated supported index processing. 3161CREATE TABLE parent (pk INT PRIMARY KEY, j INT); 3162--error ER_KEY_COLUMN_DOES_NOT_EXITS 3163CREATE TABLE child (fk INT, FOREIGN KEY (nocol) REFERENCES parent(pk)); 3164--error ER_KEY_COLUMN_DOES_NOT_EXITS 3165CREATE TABLE self (pk INT PRIMARY KEY, FOREIGN KEY (nocol) REFERENCES self(pk)); 3166CREATE TABLE child (fk INT, j INT); 3167CREATE TABLE self (pk INT PRIMARY KEY, fk INT); 3168--error ER_KEY_COLUMN_DOES_NOT_EXITS 3169ALTER TABLE child ADD FOREIGN KEY (nocol) REFERENCES parent(pk); 3170--error ER_KEY_COLUMN_DOES_NOT_EXITS 3171ALTER TABLE self ADD FOREIGN KEY (nocol) REFERENCES self(pk); 3172 3173--echo # 3174--echo # 1.b) Dropped referencing column. 3175ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk); 3176--error ER_FK_COLUMN_CANNOT_DROP 3177ALTER TABLE child DROP COLUMN fk; 3178--echo # Adding column with the same name at the same time should not help. 3179--error ER_FK_COLUMN_CANNOT_DROP 3180ALTER TABLE child DROP COLUMN fk, ADD COLUMN fk INT; 3181ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk); 3182--error ER_FK_COLUMN_CANNOT_DROP 3183ALTER TABLE self DROP COLUMN fk; 3184--error ER_FK_COLUMN_CANNOT_DROP 3185ALTER TABLE self DROP COLUMN fk, ADD COLUMN fk INT; 3186 3187--echo # 3188--echo # 1.c) Missing referenced column. 3189DROP TABLE child; 3190--error ER_FK_NO_COLUMN_PARENT 3191CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(nocol)); 3192DROP TABLE self; 3193--error ER_FK_NO_COLUMN_PARENT 3194CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES self(nocol)); 3195CREATE TABLE child (fk INT); 3196--error ER_FK_NO_COLUMN_PARENT 3197ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(nocol); 3198CREATE TABLE self (pk INT PRIMARY KEY, fk INT); 3199--error ER_FK_NO_COLUMN_PARENT 3200ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(nocol); 3201 3202--echo # 3203--echo # 1.d) Dropped referenced column. 3204ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk); 3205--error ER_FK_COLUMN_CANNOT_DROP_CHILD 3206ALTER TABLE parent DROP COLUMN pk; 3207--echo # Adding column with the same name at the same time should not help. 3208--error ER_FK_COLUMN_CANNOT_DROP_CHILD 3209ALTER TABLE parent DROP COLUMN pk, ADD COLUMN pk INT; 3210ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk); 3211--error ER_FK_COLUMN_CANNOT_DROP_CHILD 3212ALTER TABLE self DROP COLUMN pk; 3213--error ER_FK_COLUMN_CANNOT_DROP_CHILD 3214ALTER TABLE self DROP COLUMN pk, ADD COLUMN pk INT; 3215 3216--echo # 3217--echo # 1.e) Special case. Attempt to create parent for orphan 3218--echo # foreign key which doesn't have matching column. 3219DROP TABLES child, parent; 3220SET FOREIGN_KEY_CHECKS=0; 3221CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(nocol)); 3222--error ER_FK_NO_COLUMN_PARENT 3223CREATE TABLE parent (pk INT PRIMARY KEY); 3224CREATE TABLE parent0 (pk INT PRIMARY KEY); 3225--error ER_FK_NO_COLUMN_PARENT 3226RENAME TABLE parent0 TO parent; 3227--error ER_FK_NO_COLUMN_PARENT 3228ALTER TABLE parent0 RENAME TO parent; 3229SET FOREIGN_KEY_CHECKS=1; 3230DROP TABLES child, parent0, self; 3231 3232--echo # 3233--echo # 2) Handling of virtual columns in referencing and referenced 3234--echo # columns lists. 3235--echo # 3236 3237--echo # 3238--echo # 2.a) Virtual columns in referencing columns list are not 3239--echo # supported. 3240CREATE TABLE parent (pk INT PRIMARY KEY); 3241--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3242CREATE TABLE child (base INT, fk INT GENERATED ALWAYS AS (base+1) VIRTUAL, 3243 FOREIGN KEY (fk) REFERENCES parent(pk)); 3244--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3245CREATE TABLE self (pk INT PRIMARY KEY, base INT, 3246 fk INT GENERATED ALWAYS AS (base+1) VIRTUAL, 3247 FOREIGN KEY (fk) REFERENCES self(pk)); 3248CREATE TABLE child (base INT, fk INT GENERATED ALWAYS AS (base+1) VIRTUAL); 3249--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3250ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk); 3251CREATE TABLE self (pk INT PRIMARY KEY, base INT, 3252 fk INT GENERATED ALWAYS AS (base+1) VIRTUAL); 3253--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3254ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk); 3255 3256--echo # 3257--echo # 2.b) One should not be able to get virtual column in referencing 3258--echo # columns list by ALTERing column. 3259DROP TABLE child, self; 3260CREATE TABLE child (base INT, fk INT GENERATED ALWAYS AS (base+1) STORED, 3261 FOREIGN KEY (fk) REFERENCES parent(pk)); 3262--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN 3263ALTER TABLE child MODIFY fk INT GENERATED ALWAYS AS (base+1) VIRTUAL; 3264CREATE TABLE self (pk INT PRIMARY KEY, base INT, 3265 fk INT GENERATED ALWAYS AS (base+1) STORED, 3266 FOREIGN KEY (fk) REFERENCES self(pk)); 3267--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN 3268ALTER TABLE self MODIFY fk INT GENERATED ALWAYS AS (base+1) VIRTUAL; 3269 3270--echo # 3271--echo # 2.c) Virtual columns in referenced columns list are not 3272--echo # supported. 3273DROP TABLE child, parent, self; 3274CREATE TABLE parent (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, UNIQUE KEY(pk)); 3275--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3276CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3277CREATE TABLE child (fk INT); 3278--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3279ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk); 3280--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3281CREATE TABLE self (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, fk INT, 3282 UNIQUE KEY(pk), FOREIGN KEY (fk) REFERENCES self(pk)); 3283CREATE TABLE self (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, fk INT, 3284 UNIQUE KEY(pk)); 3285--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3286ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk); 3287 3288--echo # 3289--echo # 2.d) Again, one should not be able to get referenced virtual 3290--echo # column by ALTERing it. 3291DROP TABLE child, parent, self; 3292CREATE TABLE parent (base INT, pk INT GENERATED ALWAYS AS (base+1) STORED, UNIQUE KEY(pk)); 3293CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3294--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN 3295ALTER TABLE parent MODIFY pk INT GENERATED ALWAYS AS (base+1) VIRTUAL; 3296CREATE TABLE self (base INT, pk INT GENERATED ALWAYS AS (base+1) STORED, fk INT, 3297 UNIQUE KEY(pk), FOREIGN KEY (fk) REFERENCES self(pk)); 3298--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN 3299ALTER TABLE self MODIFY pk INT GENERATED ALWAYS AS (base+1) VIRTUAL; 3300 3301--echo # 3302--echo # 2.e) Special case. Attempt to create parent for orphan 3303--echo # foreign key which has virtual column. 3304DROP TABLES child, parent, self; 3305SET FOREIGN_KEY_CHECKS=0; 3306CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3307--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3308CREATE TABLE parent (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, UNIQUE KEY(pk)); 3309CREATE TABLE parent0 (base INT, pk INT GENERATED ALWAYS AS (base+1) VIRTUAL, UNIQUE KEY(pk)); 3310--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3311RENAME TABLE parent0 TO parent; 3312--error ER_FK_CANNOT_USE_VIRTUAL_COLUMN 3313ALTER TABLE parent0 RENAME TO parent; 3314SET FOREIGN_KEY_CHECKS=1; 3315DROP TABLES child, parent0; 3316 3317--echo # 3318--echo # 3) Check how missing/dropped supporting index on referencing table 3319--echo # is handled. 3320--echo # 3321 3322--echo # 3323--echo # 3.a) Normally, generated supporting index is automatically added 3324--echo # along with foreign key, so it can be missing only in some rare 3325--echo # corner case, for example, when generated index is automatically 3326--echo # converted to spatial index. 3327CREATE TABLE parent (pk POINT SRID 0 NOT NULL, KEY(pk)); 3328--error ER_FK_NO_INDEX_CHILD 3329CREATE TABLE child (fk POINT SRID 0 NOT NULL, FOREIGN KEY(fk) REFERENCES parent(pk)); 3330CREATE TABLE child (fk POINT SRID 0 NOT NULL); 3331--error ER_FK_NO_INDEX_CHILD 3332ALTER TABLE child ADD FOREIGN KEY(fk) REFERENCES parent(pk); 3333--error ER_FK_NO_INDEX_CHILD 3334CREATE TABLE self (pk POINT SRID 0 NOT NULL, fk POINT SRID 0 NOT NULL, 3335 KEY(pk), FOREIGN KEY(fk) REFERENCES self(pk)); 3336CREATE TABLE self (pk POINT SRID 0 NOT NULL, fk POINT SRID 0 NOT NULL, KEY(pk)); 3337--error ER_FK_NO_INDEX_CHILD 3338ALTER TABLE self ADD FOREIGN KEY(fk) REFERENCES self(pk); 3339 3340--echo # 3341--echo # 3.b) Attempt to drop supporting index should be prohibited. 3342DROP TABLES self, child, parent; 3343CREATE TABLE parent (pk INT PRIMARY KEY); 3344CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent(pk)); 3345--error ER_DROP_INDEX_FK 3346ALTER TABLE child DROP KEY fk; 3347CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY(fk) REFERENCES self(pk)); 3348--error ER_DROP_INDEX_FK 3349ALTER TABLE self DROP KEY fk; 3350--echo # However, we allow automatic dropping of generated index when new 3351--echo # explicit supporting index is added. 3352ALTER TABLE child ADD KEY fk_s(fk); 3353SHOW CREATE TABLE child; 3354ALTER TABLE self ADD KEY fk_s(fk); 3355SHOW CREATE TABLE self; 3356--echo # Also dropping supporting index and providing replacement is fine. 3357ALTER TABLE child DROP KEY fk_s, ADD COLUMN j INT, ADD KEY (fk, j); 3358ALTER TABLE self DROP KEY fk_s, ADD COLUMN j INT, ADD KEY(fk, j); 3359DROP TABLES self, child, parent; 3360 3361--echo # 3362--echo # 4) Check how foreign keys involving partitioned tables are handled. 3363--echo # 3364 3365--echo # 3366--echo # 4.a) Creation of partitioned child table, addition of foreign key to 3367--echo # partitioned table and attempt to partition child table in a 3368--echo # foreign key should all lead to errors (since InnoDB doesn't 3369--echo # support foreign keys involving partitioned tables yet). 3370CREATE TABLE parent (pk INT PRIMARY KEY); 3371--error ER_FOREIGN_KEY_ON_PARTITIONED 3372CREATE TABLE child (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) 3373 PARTITION BY KEY (pk) PARTITIONS 20; 3374 3375CREATE TABLE child (pk INT PRIMARY KEY, fk INT) PARTITION BY KEY (pk) PARTITIONS 20; 3376--error ER_FOREIGN_KEY_ON_PARTITIONED 3377ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk); 3378DROP TABLE child; 3379 3380CREATE TABLE child (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3381--error ER_FOREIGN_KEY_ON_PARTITIONED 3382ALTER TABLE child PARTITION BY KEY (pk) PARTITIONS 20; 3383 3384--echo # 3385--echo # 4.b) Attempt to partition parent table in a foreign key, 3386--echo # attempt to create table with foreign key referencing 3387--echo # partitioned table and addition of foreign key with 3388--echo # partitioned parent should all lead to errors as well. 3389--error ER_FOREIGN_KEY_ON_PARTITIONED 3390ALTER TABLE parent PARTITION BY KEY (pk) PARTITIONS 20; 3391DROP TABLES child, parent; 3392 3393CREATE TABLE parent (pk INT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 20; 3394--error ER_FOREIGN_KEY_ON_PARTITIONED 3395CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3396 3397CREATE TABLE child (fk INT); 3398--error ER_FOREIGN_KEY_ON_PARTITIONED 3399ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk); 3400DROP TABLES child, parent; 3401 3402--echo # 3403--echo # 4.c) Addition of partitioned parent for previously orphan foreign key 3404--echo # should result in error as well. 3405SET FOREIGN_KEY_CHECKS=0; 3406CREATE TABLE orphan (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3407SET FOREIGN_KEY_CHECKS=1; 3408--error ER_FOREIGN_KEY_ON_PARTITIONED 3409CREATE TABLE parent (pk INT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 20; 3410 3411CREATE TABLE parent0 (pk INT PRIMARY KEY) PARTITION BY KEY (pk) PARTITIONS 20; 3412--error ER_FOREIGN_KEY_ON_PARTITIONED 3413RENAME TABLE parent0 TO parent; 3414 3415--error ER_FOREIGN_KEY_ON_PARTITIONED 3416ALTER TABLE parent0 RENAME TO parent; 3417--error ER_FOREIGN_KEY_ON_PARTITIONED 3418ALTER TABLE parent0 RENAME TO parent, ADD COLUMN j INT, ALGORITHM=COPY; 3419--error ER_FOREIGN_KEY_ON_PARTITIONED 3420ALTER TABLE parent0 RENAME TO parent, ADD COLUMN j INT, ALGORITHM=INPLACE; 3421DROP TABLES orphan, parent0; 3422 3423--echo # 3424--echo # 4.d) ALTER TABLE which ADD/DROP FOREIGN KEY and change table's 3425--echo # partitioning status at the same time. 3426CREATE TABLE parent (pk INT PRIMARY KEY); 3427CREATE TABLE child (pk INT PRIMARY KEY, fk INT, 3428 CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk)); 3429--echo # Dropping foreign key and adding partitioning is OK. 3430ALTER TABLE child DROP FOREIGN KEY c PARTITION BY KEY (pk) PARTITIONS 20; 3431--echo # Adding foreign key and removing partitioning is OK. 3432ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) REMOVE PARTITIONING; 3433DROP TABLES child, parent; 3434CREATE TABLE self (pk INT PRIMARY KEY, fk INT, 3435 CONSTRAINT c FOREIGN KEY (fk) REFERENCES self(pk)); 3436--echo # Dropping foreign key and adding partitioning is OK. 3437ALTER TABLE self DROP FOREIGN KEY c PARTITION BY KEY (pk) PARTITIONS 20; 3438--echo # Adding foreign key and removing partitioning is OK. 3439--echo # The below statement provides coverage gor bug#28486106 "ALTER TABLE 3440--echo # ADD FOREIGN KEY ... REMOVE PARTITIONING FAILS IF SELF-REFENCE". 3441ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk) REMOVE PARTITIONING; 3442DROP TABLES self; 3443 3444--echo # 3445--echo # 6) Check that we prohibit foreign keys with SET NULL action with 3446--echo # non-nullable referencing columns. 3447--echo # 3448 3449--echo # 3450--echo # 6.1) Attempt to add foreign key with SET NULL action and 3451--echo # non-nullable column should lead to error. 3452CREATE TABLE parent (pk INT PRIMARY KEY); 3453--error ER_FK_COLUMN_NOT_NULL 3454CREATE TABLE child (fk INT NOT NULL, CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL); 3455--error ER_FK_COLUMN_NOT_NULL 3456CREATE TABLE child (fk INT NOT NULL, CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL); 3457CREATE TABLE child (fk INT NOT NULL); 3458--error ER_FK_COLUMN_NOT_NULL 3459ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL; 3460--error ER_FK_COLUMN_NOT_NULL 3461ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL; 3462DROP TABLE child; 3463 3464--echo # Case of when column is implicitly made non-nullable due to PRIMARY 3465--echo # KEY should be handled in the same way. 3466--error ER_FK_COLUMN_NOT_NULL 3467CREATE TABLE child (fk INT, PRIMARY KEY(fk), CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL); 3468--error ER_FK_COLUMN_NOT_NULL 3469CREATE TABLE child (fk INT, PRIMARY KEY(fk), CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL); 3470CREATE TABLE child (fk INT); 3471--error ER_FK_COLUMN_NOT_NULL 3472ALTER TABLE child ADD PRIMARY KEY (fk), ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL; 3473--error ER_FK_COLUMN_NOT_NULL 3474ALTER TABLE child ADD PRIMARY KEY (fk), ADD FOREIGN KEY (fk) REFERENCES parent(pk) ON UPDATE SET NULL; 3475DROP TABLE child; 3476 3477--echo # 3478--echo # 6.2) Attempt to make referencing column non-nullable in existing 3479--echo # foreign key with SET NULL action should lead to error as well. 3480CREATE TABLE child_one (fk INT, CONSTRAINT c FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL); 3481CREATE TABLE child_two (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk) ON DELETE SET NULL); 3482--error ER_FK_COLUMN_NOT_NULL 3483ALTER TABLE child_one MODIFY COLUMN fk INT NOT NULL; 3484--error ER_FK_COLUMN_NOT_NULL 3485ALTER TABLE child_two CHANGE COLUMN fk fk1 INT NOT NULL; 3486 3487--echo # Case of when column is implicitly made non-nullable due addition 3488--echo # of PRIMARY KEY should be handled in the same way. 3489--error ER_FK_COLUMN_NOT_NULL 3490ALTER TABLE child_one ADD PRIMARY KEY(fk); 3491--error ER_FK_COLUMN_NOT_NULL 3492ALTER TABLE child_two ADD PRIMARY KEY(fk); 3493DROP TABLES child_one, child_two, parent; 3494 3495--echo # 3496--echo # 7) Test that we check that referencing and referenced column types are 3497--echo # compatible. Such check should be performed for newly created foreign 3498--echo # keys and when we change types of columns in existing foreign keys. 3499--echo # 3500 3501--echo # 3502--echo # 7.a) Attempt to create new foreign key between columns of incompatible 3503--echo # types should lead to error. This should happen even in 3504--echo # FOREIGN_KEY_CHECKS=0 mode. 3505CREATE TABLE parent (pk INT PRIMARY KEY); 3506--error ER_FK_INCOMPATIBLE_COLUMNS 3507CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk)); 3508SET FOREIGN_KEY_CHECKS=0; 3509--error ER_FK_INCOMPATIBLE_COLUMNS 3510CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk)); 3511SET FOREIGN_KEY_CHECKS=1; 3512 3513CREATE TABLE child (fk CHAR(10)); 3514--error ER_FK_INCOMPATIBLE_COLUMNS 3515ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk); 3516SET FOREIGN_KEY_CHECKS=0; 3517--error ER_FK_INCOMPATIBLE_COLUMNS 3518ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk); 3519 3520SET FOREIGN_KEY_CHECKS=1; 3521--error ER_FK_INCOMPATIBLE_COLUMNS 3522CREATE TABLE self (pk INT PRIMARY KEY, fk CHAR(10), FOREIGN KEY (fk) REFERENCES self(pk)); 3523SET FOREIGN_KEY_CHECKS=0; 3524--error ER_FK_INCOMPATIBLE_COLUMNS 3525CREATE TABLE self (pk INT PRIMARY KEY, fk CHAR(10), FOREIGN KEY (fk) REFERENCES self(pk)); 3526SET FOREIGN_KEY_CHECKS=1; 3527 3528CREATE TABLE self (pk INT PRIMARY KEY, fk CHAR(10)); 3529--error ER_FK_INCOMPATIBLE_COLUMNS 3530ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk); 3531SET FOREIGN_KEY_CHECKS=0; 3532--error ER_FK_INCOMPATIBLE_COLUMNS 3533ALTER TABLE self ADD FOREIGN KEY (fk) REFERENCES self(pk); 3534SET FOREIGN_KEY_CHECKS=1; 3535DROP TABLES self, child; 3536 3537--echo # 3538--echo # 7.b) Attempt to change referencing or referenced column in existing 3539--echo # foreign key to incompatible type should lead to error. This 3540--echo # should also happen in FOREIGN_KEY_CHECKS=0 mode in general case 3541--echo # (there is exception for charset changes which we test below). 3542CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3543--error ER_FK_INCOMPATIBLE_COLUMNS 3544ALTER TABLE child MODIFY fk CHAR(10); 3545--error ER_FK_INCOMPATIBLE_COLUMNS 3546ALTER TABLE parent MODIFY pk CHAR(10); 3547SET FOREIGN_KEY_CHECKS=0; 3548--error ER_FK_INCOMPATIBLE_COLUMNS 3549ALTER TABLE child MODIFY fk CHAR(10); 3550--error ER_FK_INCOMPATIBLE_COLUMNS 3551ALTER TABLE parent MODIFY pk CHAR(10); 3552SET FOREIGN_KEY_CHECKS=1; 3553 3554CREATE TABLE self (pk INT PRIMARY KEY, fk INT, FOREIGN KEY (fk) REFERENCES self(pk)); 3555--error ER_FK_INCOMPATIBLE_COLUMNS 3556ALTER TABLE self MODIFY fk CHAR(10); 3557--error ER_FK_INCOMPATIBLE_COLUMNS 3558ALTER TABLE self MODIFY pk CHAR(10); 3559SET FOREIGN_KEY_CHECKS=0; 3560--error ER_FK_INCOMPATIBLE_COLUMNS 3561ALTER TABLE self MODIFY fk CHAR(10); 3562--error ER_FK_INCOMPATIBLE_COLUMNS 3563ALTER TABLE self MODIFY pk CHAR(10); 3564SET FOREIGN_KEY_CHECKS=1; 3565 3566--echo # Modifying types of both referencing and referenced columns to 3567--echo # to compatible types is OK. However FOREIGN_KEY_CHECKS=0 is needed 3568--echo # to avoid error about possible FK violation due to data conversion. 3569SET FOREIGN_KEY_CHECKS=0; 3570ALTER TABLE self MODIFY pk CHAR(10), MODIFY fk CHAR(10); 3571SET FOREIGN_KEY_CHECKS=1; 3572DROP TABLES child, parent, self; 3573 3574--echo # 3575--echo # 7.c) Test compatibility checks for multi-column foreign keys. 3576CREATE TABLE parent (pk1 INT, pk2 INT, PRIMARY KEY (pk1, pk2)); 3577--error ER_FK_INCOMPATIBLE_COLUMNS 3578CREATE TABLE child (fk1 INT, fk2 CHAR(10), FOREIGN KEY (fk1, fk2) REFERENCES parent(pk1, pk2)); 3579CREATE TABLE child (fk1 INT, fk2 CHAR(10)); 3580--error ER_FK_INCOMPATIBLE_COLUMNS 3581ALTER TABLE child ADD FOREIGN KEY (fk1, fk2) REFERENCES parent(pk1, pk2); 3582DROP TABLE child; 3583 3584CREATE TABLE child (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent(pk1, pk2)); 3585--error ER_FK_INCOMPATIBLE_COLUMNS 3586ALTER TABLE child MODIFY fk2 CHAR(10); 3587--error ER_FK_INCOMPATIBLE_COLUMNS 3588ALTER TABLE parent MODIFY pk2 CHAR(10); 3589DROP TABLE child, parent; 3590 3591--error ER_FK_INCOMPATIBLE_COLUMNS 3592CREATE TABLE self (pk1 INT, pk2 INT, fk1 INT, fk2 CHAR(10), PRIMARY KEY (pk1, pk2), 3593 FOREIGN KEY (fk1, fk2) REFERENCES self(pk1, pk2)); 3594CREATE TABLE self (pk1 INT, pk2 INT, fk1 INT, fk2 CHAR(10), PRIMARY KEY (pk1, pk2)); 3595--error ER_FK_INCOMPATIBLE_COLUMNS 3596ALTER TABLE self ADD FOREIGN KEY (fk1, fk2) REFERENCES self(pk1, pk2); 3597DROP TABLE self; 3598 3599CREATE TABLE self (pk1 INT, pk2 INT, fk1 INT, fk2 INT, PRIMARY KEY (pk1, pk2), 3600 FOREIGN KEY (fk1, fk2) REFERENCES self(pk1, pk2)); 3601--error ER_FK_INCOMPATIBLE_COLUMNS 3602ALTER TABLE self MODIFY fk2 CHAR(10); 3603--error ER_FK_INCOMPATIBLE_COLUMNS 3604ALTER TABLE self MODIFY pk2 CHAR(10); 3605DROP TABLE self; 3606 3607--echo # 3608--echo # 7.e) Test how compatibility rules work for various types. 3609--echo # Different engines have different rules, so the below 3610--echo # part of test is specific to InnoDB. 3611--echo # 3612--echo # We don't check compatibility for BLOB based types as they only 3613--echo # support prefix indexes which are not usable for foreign keys. 3614--echo # Also we don't provide coverage for legacy types. 3615 3616--echo # 3617--echo # 7.e.I) For integer types both type size and signedness should match. 3618CREATE TABLE parent (pk INT PRIMARY KEY); 3619--echo # Foreign keys over integer types are supported. 3620CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3621DROP TABLE child; 3622--error ER_FK_INCOMPATIBLE_COLUMNS 3623CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3624--error ER_FK_INCOMPATIBLE_COLUMNS 3625CREATE TABLE child (fk BIGINT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3626--error ER_FK_INCOMPATIBLE_COLUMNS 3627CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk)); 3628ALTER TABLE parent MODIFY pk INT UNSIGNED; 3629CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk)); 3630--error ER_FK_INCOMPATIBLE_COLUMNS 3631ALTER TABLE child MODIFY fk INT; 3632DROP TABLE child, parent; 3633--echo # Integer types are not compatible with most of other types 3634--echo # (we check types with same storage requirements as INT below). 3635CREATE TABLE parent (pk INT PRIMARY KEY); 3636--error ER_FK_INCOMPATIBLE_COLUMNS 3637CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk)); 3638--error ER_FK_INCOMPATIBLE_COLUMNS 3639CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk)); 3640--error ER_FK_INCOMPATIBLE_COLUMNS 3641CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3642--error ER_FK_INCOMPATIBLE_COLUMNS 3643CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk)); 3644--error ER_FK_INCOMPATIBLE_COLUMNS 3645CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk)); 3646--error ER_FK_INCOMPATIBLE_COLUMNS 3647CREATE TABLE child (fk BIT(32), FOREIGN KEY (fk) REFERENCES parent(pk)); 3648--echo # Oddly enough, some integer types are compatible with some temporal 3649--echo # types, enums and sets. However, this is probably a bug and not a 3650--echo # feature, so we don't test it here. 3651DROP TABLE parent; 3652 3653--echo # 3654--echo # 7.e.II) For floating point types only the exact type matters. 3655CREATE TABLE parent (pk DOUBLE PRIMARY KEY); 3656--echo # Though using such types in foreign key is EXTREMELY bad idea they 3657--echo # are supported. 3658CREATE TABLE child (fk DOUBLE, FOREIGN KEY (fk) REFERENCES parent(pk)); 3659DROP TABLE child; 3660--error ER_FK_INCOMPATIBLE_COLUMNS 3661CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3662--echo # Signedness doesn't matter. 3663CREATE TABLE child (fk DOUBLE UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk)); 3664DROP TABLES child, parent; 3665--echo # Floating point types are not compatible with other types. 3666--echo # (we check types with same storage requirements as FLOAT below). 3667CREATE TABLE parent (pk FLOAT PRIMARY KEY); 3668--error ER_FK_INCOMPATIBLE_COLUMNS 3669CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3670--error ER_FK_INCOMPATIBLE_COLUMNS 3671CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk)); 3672--error ER_FK_INCOMPATIBLE_COLUMNS 3673CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk)); 3674--error ER_FK_INCOMPATIBLE_COLUMNS 3675CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk)); 3676--error ER_FK_INCOMPATIBLE_COLUMNS 3677CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk)); 3678--error ER_FK_INCOMPATIBLE_COLUMNS 3679CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk)); 3680--error ER_FK_INCOMPATIBLE_COLUMNS 3681CREATE TABLE child (fk BIT(32), FOREIGN KEY (fk) REFERENCES parent(pk)); 3682DROP TABLE parent; 3683 3684--echo # 3685--echo # 7.e.III) Compatibility rules for DECIMAL type are broken. 3686--echo # 3687--echo # InnoDB considers this type to be binary string type. 3688--echo # So it doesn't take into account precision and scale. 3689--echo # And it is hard to imagine that comparing binary strings 3690--echo # representing DECIMAL(10,9) and DECIMAL(10,1) can work 3691--echo # correctly. Making this type compatible with other binary 3692--echo # string types, some temporals and BIT type is probably a 3693--echo # bad idea too. 3694CREATE TABLE parent (pk DECIMAL(6,2) PRIMARY KEY); 3695--echo # Foreign keys over DECIMAL columns are supported. 3696CREATE TABLE child (fk DECIMAL(6,2), FOREIGN KEY (fk) REFERENCES parent(pk)); 3697DROP TABLE child; 3698--echo # Signedness doesn't matter. 3699CREATE TABLE child (fk DECIMAL(6,2) UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk)); 3700DROP TABLE child; 3701--echo # DECIMAL type is not compatible with many other types. 3702--echo # (we check types with same storage requirements as DECIMAL(6,2) below). 3703--error ER_FK_INCOMPATIBLE_COLUMNS 3704CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3705--error ER_FK_INCOMPATIBLE_COLUMNS 3706CREATE TABLE child (fk CHAR(4) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk)); 3707--error ER_FK_INCOMPATIBLE_COLUMNS 3708CREATE TABLE child (fk VARCHAR(3) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk)); 3709--error ER_FK_INCOMPATIBLE_COLUMNS 3710CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3711DROP TABLE parent; 3712 3713--echo # 3714--echo # 7.e.IV) All string types are compatible with each other provided 3715--echo # that charset matches (there are exceptions to the latter 3716--echo # rule, see below). 3717CREATE TABLE parent (pk CHAR(10) PRIMARY KEY); 3718--echo # Difference in size doesn't matter. 3719CREATE TABLE child (fk CHAR(100), FOREIGN KEY (fk) REFERENCES parent(pk)); 3720--echo # Difference in type doesn't matter 3721DROP TABLE child; 3722CREATE TABLE child (fk VARCHAR(100), FOREIGN KEY (fk) REFERENCES parent(pk)); 3723DROP TABLES child, parent; 3724--echo # Even VARCHARs which use different number of bytes to store length 3725--echo # are compatible. 3726CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY); 3727CREATE TABLE child (fk VARCHAR(100), FOREIGN KEY (fk) REFERENCES parent(pk)); 3728DROP TABLE child, parent; 3729--echo # However both columns must use the same collation. 3730CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY); 3731--error ER_FK_INCOMPATIBLE_COLUMNS 3732CREATE TABLE child (fk VARCHAR(100) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk)); 3733--error ER_FK_INCOMPATIBLE_COLUMNS 3734CREATE TABLE child (fk VARCHAR(100) COLLATE utf8mb4_bin, FOREIGN KEY (fk) REFERENCES parent(pk)); 3735--echo # Binary strings are not compatible with non-binary strings. 3736--error ER_FK_INCOMPATIBLE_COLUMNS 3737CREATE TABLE child (fk VARBINARY(40), FOREIGN KEY (fk) REFERENCES parent(pk)); 3738DROP TABLE parent; 3739--echo # But all binary string types are compatible. 3740CREATE TABLE parent (pk VARBINARY(10) PRIMARY KEY); 3741CREATE TABLE child (fk BINARY(100), FOREIGN KEY (fk) REFERENCES parent(pk)); 3742DROP TABLES child, parent; 3743--echo # Non-binary string types are incompatible with non-string types. 3744CREATE TABLE parent (pk CHAR(4) CHARACTER SET latin1 PRIMARY KEY); 3745--error ER_FK_INCOMPATIBLE_COLUMNS 3746CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3747--error ER_FK_INCOMPATIBLE_COLUMNS 3748CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3749--error ER_FK_INCOMPATIBLE_COLUMNS 3750CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk)); 3751--error ER_FK_INCOMPATIBLE_COLUMNS 3752CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk)); 3753--error ER_FK_INCOMPATIBLE_COLUMNS 3754CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk)); 3755--error ER_FK_INCOMPATIBLE_COLUMNS 3756CREATE TABLE child (fk BIT(32), FOREIGN KEY (fk) REFERENCES parent(pk)); 3757ALTER TABLE parent MODIFY pk CHAR(1) CHARACTER SET latin1; 3758--error ER_FK_INCOMPATIBLE_COLUMNS 3759CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk)); 3760--error ER_FK_INCOMPATIBLE_COLUMNS 3761CREATE TABLE child (fk ENUM('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk)); 3762--error ER_FK_INCOMPATIBLE_COLUMNS 3763CREATE TABLE child (fk SET('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk)); 3764DROP TABLE parent; 3765--echo # Binary string types are incompatible with many non-string types. 3766CREATE TABLE parent (pk BINARY(4) PRIMARY KEY); 3767--error ER_FK_INCOMPATIBLE_COLUMNS 3768CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3769--error ER_FK_INCOMPATIBLE_COLUMNS 3770CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3771ALTER TABLE parent MODIFY pk BINARY(1); 3772--error ER_FK_INCOMPATIBLE_COLUMNS 3773CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk)); 3774--error ER_FK_INCOMPATIBLE_COLUMNS 3775CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk)); 3776--error ER_FK_INCOMPATIBLE_COLUMNS 3777CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk)); 3778--echo # Since we think that binary string compatibility with DECIMAL and some temporal types 3779--echo # is probably a bug, we don't cover it here. 3780DROP TABLE parent; 3781 3782--echo # 3783--echo # 7.e.V) Compatibility rules for some of temporal types are broken. 3784--echo # 3785--echo # InnoDB considers TIME, TIMESTAMP and DATETIME types to be 3786--echo # binary strings. As result they are compatible with each other 3787--echo # (no matter what scale is used), binary string, DECIMAL and BIT 3788--echo # types, which makes little sense. 3789--echo # YEAR and DATE types are considered to be integer types which 3790--echo # makes them compatible with some other integer types, enums and 3791--echo # sets, which is probably bad idea too. 3792--echo # 3793--echo # YEAR and DATE are only compatible with itself (and integer types, 3794--echo # enums and sets with the same storage size which is probably a bug); 3795CREATE TABLE parent(pk DATE PRIMARY KEY); 3796CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk)); 3797DROP TABLE child; 3798--error ER_FK_INCOMPATIBLE_COLUMNS 3799CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk)); 3800--error ER_FK_INCOMPATIBLE_COLUMNS 3801CREATE TABLE child (fk BINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk)); 3802--error ER_FK_INCOMPATIBLE_COLUMNS 3803CREATE TABLE child (fk VARBINARY(2), FOREIGN KEY (fk) REFERENCES parent(pk)); 3804--error ER_FK_INCOMPATIBLE_COLUMNS 3805CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3806--error ER_FK_INCOMPATIBLE_COLUMNS 3807CREATE TABLE child (fk DECIMAL(6,0), FOREIGN KEY (fk) REFERENCES parent(pk)); 3808--error ER_FK_INCOMPATIBLE_COLUMNS 3809CREATE TABLE child (fk TIME(0), FOREIGN KEY (fk) REFERENCES parent(pk)); 3810--error ER_FK_INCOMPATIBLE_COLUMNS 3811CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk)); 3812--error ER_FK_INCOMPATIBLE_COLUMNS 3813CREATE TABLE child (fk BIT(24), FOREIGN KEY (fk) REFERENCES parent(pk)); 3814DROP TABLE parent; 3815--echo # TIME, TIMESTAMP and DATETIME types are compatible only 3816--echo # with other types which InnoDB considers binary strings. 3817--echo # Their scale doesn't matter (which is probably a bug). 3818CREATE TABLE parent (pk TIMESTAMP PRIMARY KEY); 3819CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk)); 3820DROP TABLE child; 3821--error ER_FK_INCOMPATIBLE_COLUMNS 3822CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3823--error ER_FK_INCOMPATIBLE_COLUMNS 3824CREATE TABLE child (fk CHAR(4) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk)); 3825--error ER_FK_INCOMPATIBLE_COLUMNS 3826CREATE TABLE child (fk VARCHAR(3) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk)); 3827--error ER_FK_INCOMPATIBLE_COLUMNS 3828CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3829--error ER_FK_INCOMPATIBLE_COLUMNS 3830CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk)); 3831--error ER_FK_INCOMPATIBLE_COLUMNS 3832CREATE TABLE child (fk ENUM('a'), FOREIGN KEY (fk) REFERENCES parent(pk)); 3833--error ER_FK_INCOMPATIBLE_COLUMNS 3834CREATE TABLE child (fk SET('a'), FOREIGN KEY (fk) REFERENCES parent(pk)); 3835DROP TABLE parent; 3836 3837--echo # 3838--echo # 7.e.VI) Columns of ENUM type are compatible if storage size is 3839--echo # the same. 3840--echo # 3841--echo # They are also compatible with some integer types, but it is not 3842--echo # clear if it is a bug or feature. 3843--echo # 3844CREATE TABLE parent(pk ENUM('a') PRIMARY KEY); 3845--echo # Foreign key over ENUMs are supported, element names and count do 3846--echo # not matter provided that storage size is the same. 3847CREATE TABLE child (fk ENUM('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk)); 3848DROP TABLE child; 3849--echo # Storage size should match. 3850--let $x100enum =`WITH RECURSIVE gen (num, el) AS (SELECT 1, CAST('\'a1\'' AS CHAR(2048)) UNION SELECT num+1, CONCAT(el,',\'a',num+1,'\'') FROM gen WHERE num <= 256) SELECT el FROM gen WHERE num = 256` 3851--error ER_FK_INCOMPATIBLE_COLUMNS 3852--eval CREATE TABLE child (fk ENUM($x100enum), FOREIGN KEY (fk) REFERENCES parent(pk)) 3853--error ER_FK_INCOMPATIBLE_COLUMNS 3854CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk)); 3855--error ER_FK_INCOMPATIBLE_COLUMNS 3856CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk)); 3857--error ER_FK_INCOMPATIBLE_COLUMNS 3858CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk)); 3859--error ER_FK_INCOMPATIBLE_COLUMNS 3860CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3861--error ER_FK_INCOMPATIBLE_COLUMNS 3862CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk)); 3863--error ER_FK_INCOMPATIBLE_COLUMNS 3864CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk)); 3865--error ER_FK_INCOMPATIBLE_COLUMNS 3866CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk)); 3867--error ER_FK_INCOMPATIBLE_COLUMNS 3868CREATE TABLE child (fk BIT(8), FOREIGN KEY (fk) REFERENCES parent(pk)); 3869DROP TABLE parent; 3870 3871--echo # 3872--echo # 7.e.VII) Columns of SET type are compatible if storage size is 3873--echo # the same. 3874--echo # 3875--echo # They are also compatible with some integer types, but it is not 3876--echo # clear if it is a bug or feature. 3877--echo # 3878CREATE TABLE parent(pk SET('a') PRIMARY KEY); 3879--echo # Foreign key over SETs are supported, element names and count do 3880--echo # not matter provided that storage size is the same. 3881CREATE TABLE child (fk SET('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk)); 3882DROP TABLE child; 3883--echo # Storage size should match. 3884--error ER_FK_INCOMPATIBLE_COLUMNS 3885CREATE TABLE child (fk SET('a1','a2','a3','a4','a5','a6','a7','a8','a9'), FOREIGN KEY (fk) REFERENCES parent(pk)); 3886--error ER_FK_INCOMPATIBLE_COLUMNS 3887CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk)); 3888--error ER_FK_INCOMPATIBLE_COLUMNS 3889CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk)); 3890--error ER_FK_INCOMPATIBLE_COLUMNS 3891CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk)); 3892--error ER_FK_INCOMPATIBLE_COLUMNS 3893CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3894--error ER_FK_INCOMPATIBLE_COLUMNS 3895CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk)); 3896--error ER_FK_INCOMPATIBLE_COLUMNS 3897CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk)); 3898--error ER_FK_INCOMPATIBLE_COLUMNS 3899CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk)); 3900--error ER_FK_INCOMPATIBLE_COLUMNS 3901CREATE TABLE child (fk BIT(8), FOREIGN KEY (fk) REFERENCES parent(pk)); 3902DROP TABLE parent; 3903 3904--echo # 3905--echo # 7.e.VIII) Columns of BIT type are compatible with each other 3906--echo # independently of size. 3907--echo # 3908--echo # They are considered binary strings so compatible with other 3909--echo # binary string types (which might make sense). They are also 3910--echo # compatible with some temporals and DECIMAL type, which makes 3911--echo # little sense. 3912CREATE TABLE parent(pk BIT(32) PRIMARY KEY); 3913--echo # Column size doesn't matter. 3914CREATE TABLE child (fk BIT(10), FOREIGN KEY (fk) REFERENCES parent(pk)); 3915DROP TABLE child; 3916--error ER_FK_INCOMPATIBLE_COLUMNS 3917CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3918--error ER_FK_INCOMPATIBLE_COLUMNS 3919CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk)); 3920--error ER_FK_INCOMPATIBLE_COLUMNS 3921CREATE TABLE child (fk CHAR(4) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk)); 3922--error ER_FK_INCOMPATIBLE_COLUMNS 3923CREATE TABLE child (fk VARCHAR(3) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk)); 3924--error ER_FK_INCOMPATIBLE_COLUMNS 3925CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk)); 3926--error ER_FK_INCOMPATIBLE_COLUMNS 3927CREATE TABLE child (fk ENUM('a'), FOREIGN KEY (fk) REFERENCES parent(pk)); 3928--error ER_FK_INCOMPATIBLE_COLUMNS 3929CREATE TABLE child (fk SET('a'), FOREIGN KEY (fk) REFERENCES parent(pk)); 3930DROP TABLE parent; 3931 3932--echo # 3933--echo # 7.e.IX) In FOREIGN_KEY_CHECKS=0 mode InnoDB allows to change charsets 3934--echo # of string columns in existing foreign keys. Allowing such 3935--echo # temporary discrepancies is necessary as in general case there 3936--echo # is no way to change charset of both child and parent columns 3937--echo # simultaneously. Such discrepancies are still prohibited 3938--echo # in newly created foreign keys. 3939CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY); 3940CREATE TABLE child (fk VARCHAR(20) CHARACTER SET latin1, 3941 FOREIGN KEY (fk) REFERENCES parent(pk)); 3942SET FOREIGN_KEY_CHECKS=0; 3943ALTER TABLE parent MODIFY pk VARCHAR(10) CHARACTER SET utf8mb4; 3944ALTER TABLE child MODIFY fk VARCHAR(20) CHARACTER SET utf8mb4; 3945ALTER TABLE child MODIFY fk VARCHAR(20) CHARACTER SET latin1; 3946ALTER TABLE parent MODIFY pk VARCHAR(10) CHARACTER SET latin1; 3947DROP TABLE child; 3948--echo # Adding new foreign key with discrepancies is not allowed 3949--echo # even in FOREIGN_KEY_CHECKS=0 mode. 3950--error ER_FK_INCOMPATIBLE_COLUMNS 3951CREATE TABLE child (fk VARCHAR(20) CHARACTER SET utf8mb4, 3952 FOREIGN KEY (fk) REFERENCES parent(pk)); 3953CREATE TABLE child (fk VARCHAR(20) CHARACTER SET utf8mb4); 3954--error ER_FK_INCOMPATIBLE_COLUMNS 3955ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk); 3956DROP TABLE child, parent; 3957--echo # One cannot create such discrepancy when adding parent to 3958--echo # orphan foreign key. 3959CREATE TABLE child (fk VARCHAR(20) CHARACTER SET latin1, 3960 FOREIGN KEY (fk) REFERENCES parent(pk)); 3961--error ER_FK_INCOMPATIBLE_COLUMNS 3962CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY); 3963CREATE TABLE parent0 (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY); 3964--error ER_FK_INCOMPATIBLE_COLUMNS 3965RENAME TABLE parent0 TO parent; 3966DROP TABLES child, parent0; 3967--echo # This exception doesn't apply to binary strings though. 3968CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET binary PRIMARY KEY); 3969CREATE TABLE child (fk VARCHAR(20) CHARACTER SET binary, 3970 FOREIGN KEY (fk) REFERENCES parent(pk)); 3971--error ER_FK_INCOMPATIBLE_COLUMNS 3972ALTER TABLE parent MODIFY pk VARCHAR(10) CHARACTER SET utf8mb4; 3973--error ER_FK_INCOMPATIBLE_COLUMNS 3974ALTER TABLE child MODIFY fk VARCHAR(20) CHARACTER SET utf8mb4; 3975SET FOREIGN_KEY_CHECKS=1; 3976DROP TABLES child, parent; 3977 3978 3979--echo # 3980--echo # WL#8910: Ensure foreign key error does not reveal information about 3981--echo # parent table for which user has no access privileges. 3982--echo # 3983# We cannot verify these scenarios with 'test' database since MTR garants DB level 3984# privileges to anonymous user. That will interfere the testing 3985--let current_DB = `SELECT DATABASE()` 3986CREATE DATABASE wl8910db; 3987USE wl8910db; 3988--echo #Set up tables. 3989CREATE TABLE t1(fld1 INT PRIMARY KEY, fld2 INT) ENGINE=INNODB; 3990CREATE TABLE t2(fld1 INT PRIMARY KEY, fld2 INT, CONSTRAINT fk2 3991FOREIGN KEY (fld1) REFERENCES t1 (fld1)) ENGINE=InnoDB; 3992CREATE TABLE t3(fld1 INT PRIMARY KEY, fld2 INT) ENGINE=InnoDB; 3993 3994--echo # Set up stored routines 3995CREATE PROCEDURE p1() SQL SECURITY INVOKER INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 3996CREATE PROCEDURE p2() SQL SECURITY DEFINER INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 3997DELIMITER |; 3998CREATE FUNCTION f1() RETURNS INT SQL SECURITY INVOKER 3999BEGIN 4000 INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 4001 RETURN 0; 4002END| 4003 4004CREATE FUNCTION f2() RETURNS INT SQL SECURITY DEFINER 4005BEGIN 4006 INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 4007 RETURN 0; 4008END| 4009 4010DELIMITER ;| 4011 4012--echo # Set up views. 4013CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t2; 4014CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT * FROM t2; 4015 4016--echo # Set up users and permissions. 4017CREATE USER user1@localhost; 4018CREATE USER user2@localhost; 4019CREATE USER user3@localhost; 4020GRANT INSERT (fld1, fld2) ON t2 TO user1@localhost; 4021GRANT INSERT ON v1 TO user2@localhost; 4022GRANT INSERT ON v2 TO user2@localhost; 4023GRANT SYSTEM_USER ON *.* TO user2@localhost; 4024GRANT SET_USER_ID ON *.* TO user2@localhost; 4025GRANT EXECUTE ON PROCEDURE p1 TO user2@localhost; 4026GRANT EXECUTE ON PROCEDURE p2 TO user2@localhost; 4027GRANT EXECUTE ON FUNCTION f1 TO user2@localhost; 4028GRANT EXECUTE ON FUNCTION f2 TO user2@localhost; 4029 4030--enable_connect_log 4031 4032connect (con1, localhost, user1,,wl8910db); 4033--echo # Without patch, reveals parent table's information. 4034--error ER_NO_REFERENCED_ROW 4035INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 4036 4037--echo # Warning displayed does not reveal parent table information. 4038INSERT IGNORE INTO t2 (fld1, fld2) VALUES (1, 2); 4039SHOW WARNINGS; 4040 4041connection default; 4042GRANT SELECT ON t1 TO user1@localhost; 4043 4044connection con1; 4045--error ER_NO_REFERENCED_ROW_2 4046INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 4047 4048connection default; 4049ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (fld2) REFERENCES t3(fld1); 4050 4051connection con1; 4052--echo # Without patch, reveals parent table's information. 4053--error ER_NO_REFERENCED_ROW 4054INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 4055 4056--echo # Warning displayed does not reveal parent table information. 4057INSERT IGNORE INTO t2 (fld1, fld2) VALUES (1, 2); 4058SHOW WARNINGS; 4059 4060connection default; 4061GRANT SELECT ON t3 TO user1@localhost; 4062 4063connection con1; 4064--error ER_NO_REFERENCED_ROW_2 4065INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 4066 4067connection default; 4068GRANT INSERT (fld1, fld2) ON t2 TO user2@localhost; 4069GRANT CREATE ROUTINE ON wl8910db.* TO user2@localhost; 4070GRANT CREATE VIEW ON wl8910db.* TO user2@localhost; 4071 4072--echo # Tests where DML reports FK constraint failure within Stored Routines. 4073connect (con2, localhost, user2,,wl8910db); 4074 4075--echo # The SQL security for p1 is invoker where invoker lacks permission 4076--echo # to parent table, hence parent table information is not displayed. 4077--error ER_NO_REFERENCED_ROW 4078CALL p1(); 4079 4080--echo # The SQL security p2 is definer, where the definer has access privilege 4081--echo # to the parent table, hence parent table information is displayed. 4082--error ER_NO_REFERENCED_ROW_2 4083CALL p2(); 4084 4085--echo # The SQL security for f1 is invoker where invoker lacks permission 4086--echo # to parent table, hence parent table information is not displayed. 4087--error ER_NO_REFERENCED_ROW 4088SELECT f1(); 4089 4090--echo # The SQL security f2 is definer, where the definer has access privilege 4091--echo # to the parent table, hence parent table information is displayed. 4092--error ER_NO_REFERENCED_ROW_2 4093SELECT f2(); 4094 4095--echo # Test for DMLs on VIEWS. 4096 4097--echo # The invoker does not have access to the parent table, hence the parent 4098--echo # table information is not displayed. 4099--error ER_NO_REFERENCED_ROW 4100INSERT INTO v1 VALUES (1, 2); 4101 4102--echo # DML on view executed within the definer context where the invoker does 4103--echo # not have access to the parent table, hence the parent table information 4104--echo # is not displayed. 4105--error ER_NO_REFERENCED_ROW 4106INSERT INTO v2 VALUES (1, 2); 4107 4108connection default; 4109GRANT SELECT ON t1 TO user2@localhost; 4110GRANT SELECT ON t3 TO user2@localhost; 4111 4112connection con2; 4113--echo # DML on view executed within the definer context where the invoker 4114--echo # has access to the parent table, hence the parent table information 4115--echo # is displayed. 4116--error ER_NO_REFERENCED_ROW_2 4117INSERT INTO v2 VALUES (1, 2); 4118 4119--echo # Tests with SET_USER_ID 4120 4121--echo # Set up stored routines and views by user with SET_USER_ID 4122--echo # privilege. 4123 4124CREATE DEFINER=root@localhost PROCEDURE p3() SQL SECURITY DEFINER 4125INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 4126 4127DELIMITER |; 4128CREATE DEFINER=root@localhost FUNCTION f3() RETURNS 4129INT SQL SECURITY DEFINER 4130BEGIN 4131 INSERT INTO t2 (fld1, fld2) VALUES (1, 2); 4132 RETURN 0; 4133END| 4134 4135DELIMITER ;| 4136 4137CREATE DEFINER=root@localhost SQL SECURITY DEFINER VIEW v3 AS 4138SELECT * FROM t2; 4139 4140--echo # Grant privileges for user3. 4141connection default; 4142GRANT EXECUTE ON PROCEDURE p3 TO user3@localhost; 4143GRANT EXECUTE ON FUNCTION f3 TO user3@localhost; 4144GRANT INSERT ON v3 TO user3@localhost; 4145GRANT SELECT ON t1 TO user3@localhost; 4146GRANT SELECT ON t3 TO user3@localhost; 4147 4148connect (con3, localhost, user3,,wl8910db); 4149 4150--echo # Since the execution happens in the definer 'root' user 4151--echo # context, the parent table informaton is exposed. 4152--error ER_NO_REFERENCED_ROW_2 4153CALL p3(); 4154 4155--error ER_NO_REFERENCED_ROW_2 4156SELECT f3(); 4157 4158--error ER_NO_REFERENCED_ROW_2 4159INSERT INTO v3 VALUES(4, 5); 4160 4161--echo # Cleanup 4162connection default; 4163disconnect con1; 4164disconnect con2; 4165disconnect con3; 4166DROP VIEW v1, v2, v3; 4167DROP TABLE t2, t3, t1; 4168DROP USER user1@localhost; 4169DROP USER user2@localhost; 4170DROP USER user3@localhost; 4171DROP PROCEDURE p1; 4172DROP PROCEDURE p2; 4173DROP PROCEDURE p3; 4174DROP FUNCTION f1; 4175DROP FUNCTION f2; 4176DROP FUNCTION f3; 4177DROP DATABASE wl8910db; 4178# Restore the current DB 4179--eval USE $current_DB 4180 4181--disable_connect_log 4182--echo # 4183--echo # Bug #28122781 FOREIGN KEY REFERENCE OPTION RESTRICT IGNORED AFTER MYSQLDUMP AND RELOAD. 4184--echo # 4185 4186CREATE TABLE t1 ( 4187 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY 4188); 4189 4190CREATE TABLE t2 ( 4191 t1_id INT NOT NULL, 4192 CONSTRAINT t2_fk FOREIGN KEY (t1_id) 4193 REFERENCES t1(id) ON UPDATE RESTRICT 4194); 4195 4196CREATE TABLE t3 ( 4197 t1_id INT NOT NULL, 4198 CONSTRAINT t3_fk FOREIGN KEY (t1_id) 4199 REFERENCES t1(id) ON DELETE RESTRICT 4200); 4201 4202CREATE TABLE t4 ( 4203 t1_id INT NOT NULL, 4204 CONSTRAINT t4_fk FOREIGN KEY (t1_id) 4205 REFERENCES t1(id) ON DELETE RESTRICT ON UPDATE RESTRICT 4206); 4207 4208SHOW CREATE TABLE t1; 4209SHOW CREATE TABLE t2; 4210SHOW CREATE TABLE t3; 4211SHOW CREATE TABLE t4; 4212 4213SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE 4214TABLE_NAME IN ('t1', 't2', 't3', 't4'); 4215 4216--echo # dump tables t1, t2, t3, t3 4217--exec $MYSQL_DUMP test t1 t2 t3 t4 > $MYSQLTEST_VARDIR/tmp/testing.sql 4218 4219DROP TABLE t1, t2, t3, t4; 4220 4221--echo # reload dump 4222--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/testing.sql 4223 4224SHOW CREATE TABLE t1; 4225SHOW CREATE TABLE t2; 4226SHOW CREATE TABLE t3; 4227SHOW CREATE TABLE t4; 4228 4229SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE 4230TABLE_NAME IN ('t1', 't2', 't3', 't4'); 4231 4232DROP TABLE t1, t2, t3, t4; 4233--remove_file $MYSQLTEST_VARDIR/tmp/testing.sql 4234 4235 4236--echo # 4237--echo # Bug #27353767 "FOREIGN KEY IS ALWAYS IN LOWER CASE". 4238--echo # 4239--echo # Check that the way in which referencing and referenced column names in 4240--echo # foreign key definition are stored and shown in SHOW CREATE TABLE output 4241--echo # and I_S tables is with 5.7 and earlier versions. 4242 4243--echo # 4244--echo # We always use version of name of referencing column which comes from 4245--echo # the definition of referencing table and not version from FOREIGN KEY 4246--echo # clause. 4247--echo # If referenced table exists than we use version of referenced column 4248--echo # name which comes from its definition, and not version from FOREIGN KEY 4249--echo # clause. 4250CREATE TABLE parent (Pk VARCHAR(10) PRIMARY KEY); 4251CREATE TABLE child (Fk VARCHAR(10), FOREIGN KEY (fK) REFERENCES parent(pK)); 4252SHOW CREATE TABLE child; 4253SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4254 WHERE referenced_table_schema='test' AND referenced_table_name='parent'; 4255DROP TABLE child; 4256CREATE TABLE child (Fk VARCHAR(10)); 4257ALTER TABLE child ADD FOREIGN KEY (fK) REFERENCES parent(pK); 4258SHOW CREATE TABLE child; 4259SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4260 WHERE referenced_table_schema='test' AND referenced_table_name='parent'; 4261 4262--echo # Ditto for self-referencing tables. 4263CREATE TABLE self (Pk VARCHAR(10) PRIMARY KEY, Fk VARCHAR(10), 4264 FOREIGN KEY (fK) REFERENCES self(pK)); 4265SHOW CREATE TABLE self; 4266SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4267 WHERE referenced_table_schema='test' AND referenced_table_name='self'; 4268DROP TABLE self; 4269CREATE TABLE self (Pk VARCHAR(10) PRIMARY KEY, Fk VARCHAR(10)); 4270ALTER TABLE self ADD FOREIGN KEY (fK) REFERENCES self(pK); 4271SHOW CREATE TABLE self; 4272SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4273 WHERE referenced_table_schema='test' AND referenced_table_name='self'; 4274 4275--echo # 4276--echo # If column name is changed (even to equivalent one) then the name shown 4277--echo # in foreign key definition is updated too. 4278ALTER TABLE parent CHANGE COLUMN Pk PK VARCHAR(20); 4279SHOW CREATE TABLE child; 4280SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4281 WHERE referenced_table_schema='test' AND referenced_table_name='parent'; 4282ALTER TABLE child CHANGE COLUMN Fk FK VARCHAR(20); 4283SHOW CREATE TABLE child; 4284SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4285 WHERE referenced_table_schema='test' AND referenced_table_name='parent'; 4286 4287--echo # Ditto for self-referencing tables. 4288ALTER TABLE self CHANGE COLUMN Pk PK VARCHAR(20); 4289SHOW CREATE TABLE self; 4290SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4291 WHERE referenced_table_schema='test' AND referenced_table_name='self'; 4292ALTER TABLE self CHANGE COLUMN Fk FK VARCHAR(20); 4293SHOW CREATE TABLE self; 4294SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4295 WHERE referenced_table_schema='test' AND referenced_table_name='self'; 4296DROP TABLE self; 4297 4298--echo # 4299--echo # Referenced column names are preserved when referenced table is dropped 4300--echo # (which is possible in FOREIGN_KEY_CHECKS = 0 mode). 4301SET FOREIGN_KEY_CHECKS=0; 4302DROP TABLE parent; 4303SHOW CREATE TABLE child; 4304SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4305 WHERE referenced_table_schema='test' AND referenced_table_name='parent'; 4306 4307--echo # 4308--echo # Addition of parent doesn't change the referenced column names. 4309CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY); 4310SHOW CREATE TABLE child; 4311SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4312 WHERE referenced_table_schema='test' AND referenced_table_name='parent'; 4313DROP TABLES child, parent; 4314 4315--echo # 4316--echo # We use version of referenced column name coming from FOREIGN KEY clause 4317--echo # when orphan foreign key is created (as referenced table doesn't exist). 4318--echo # 4319--echo # Oddly, 5.7 uses version of referencing column name coming from FOREIGN 4320--echo # KEY clause when orphan FK is added via ALTER TABLE (but not CREATE 4321--echo # TABLE). This means that in 5.7 there is discrepancy between CREATE and 4322--echo # ALTER TABLE behavior in this case. 4323--echo # In 8.0 we are consistent and stick to CREATE TABLE behavior in such 4324--echo # cases. 4325CREATE TABLE child (Fk VARCHAR(10), FOREIGN KEY (fK) REFERENCES parent(pK)); 4326SHOW CREATE TABLE child; 4327SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4328 WHERE referenced_table_schema='test' AND referenced_table_name='parent'; 4329DROP TABLE child; 4330CREATE TABLE child (Fk VARCHAR(10)); 4331ALTER TABLE child ADD FOREIGN KEY (fK) REFERENCES parent(pK); 4332SHOW CREATE TABLE child; 4333SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4334 WHERE referenced_table_schema='test' AND referenced_table_name='parent'; 4335 4336--echo # 4337--echo # Addition of parent doesn't change the referenced column names. 4338CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY); 4339SHOW CREATE TABLE child; 4340SELECT column_name, referenced_column_name FROM information_schema.key_column_usage 4341 WHERE referenced_table_schema='test' AND referenced_table_name='parent'; 4342SET FOREIGN_KEY_CHECKS=1; 4343DROP TABLES child, parent; 4344 4345 4346--echo # 4347--echo # Test for bug#29173134 "FOREIGN KEY CONSTRAINT NAMES TAKING INDEX NAME". 4348--echo # 4349--echo # 4350--echo # Original test case. 4351CREATE TABLE t1 (id INT PRIMARY KEY); 4352CREATE TABLE t2 (t1id INT, FOREIGN KEY fk_index (t1id) REFERENCES t1 (id)); 4353CREATE TABLE t3 (t1id INT, FOREIGN KEY fk_index (t1id) REFERENCES t1 (id)); 4354SHOW CREATE TABLE t2; 4355SHOW CREATE TABLE t3; 4356SELECT CONSTRAINT_NAME, TABLE_NAME 4357 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 4358 WHERE TABLE_NAME IN ('t2', 't3') ORDER BY CONSTRAINT_NAME; 4359 4360--echo # 4361--echo # Let us check what foreign key names are used in different situations. 4362CREATE TABLE t4 (fk1 INT, fk2 INT, fk3 INT, fk4 INT, 4363 FOREIGN KEY (fk1) REFERENCES t1 (id), 4364 CONSTRAINT c FOREIGN KEY (fk2) REFERENCES t1 (id), 4365 FOREIGN KEY d (fk3) REFERENCES t1 (id), 4366 CONSTRAINT e FOREIGN KEY f (fk4) REFERENCES t1 (id)); 4367SHOW CREATE TABLE t4; 4368SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 4369 WHERE TABLE_NAME = 't4' ORDER BY CONSTRAINT_NAME; 4370 4371--echo # Clean-up. 4372DROP TABLES t1, t2, t3, t4; 4373 4374 4375--echo # 4376--echo # Bug #11756183 "FOREIGN KEYS MAY DISAPPEAR AFTER ALTER TABLE RENAME". 4377--echo # Bug #18713399 "FK CHILD TABLE CANNOT BE CREATED: PROBLEMS AFTER TABLE RENAME" 4378--echo # 4379CREATE TABLE parent (pk INT PRIMARY KEY); 4380INSERT INTO parent VALUES (1); 4381CREATE TABLE child (fk INT, b INT, FOREIGN KEY (fk) REFERENCES parent (pk)); 4382INSERT INTO child VALUES (1, 1); 4383SHOW CREATE TABLE child; 4384--echo # We use ALGORITHM=COPY to ensure that the below ALTER is executed 4385--echo # using COPY algorithm even if changing column datatype becomes 4386--echo # in-place operation. 4387ALTER TABLE child MODIFY COLUMN b BIGINT, RENAME TO child_renamed, ALGORITHM=COPY; 4388--echo # The foreign key should be still in SHOW CREATE TABLE output. 4389SHOW CREATE TABLE child_renamed; 4390--echo # Removal of parent row should not be allowed. 4391--error ER_ROW_IS_REFERENCED_2 4392DELETE FROM parent WHERE pk = 1; 4393DROP TABLE child_renamed; 4394--echo # Part of test covering bug#18713399. The below CREATE TABLE statement 4395--echo # should not fail due to duplicate foreign key name. 4396CREATE TABLE child (fk INT, b INT, FOREIGN KEY (fk) REFERENCES parent (pk)); 4397DROP TABLES child, parent; 4398 4399 4400--echo # 4401--echo # Bug #18199504 "AUTO-NAMING OF FOREIGN KEYS SEEMS BROKEN WHEN 4402--echo # MULTI-OPERATION ALTER IS USED". 4403--echo # 4404CREATE TABLE parent (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c CHAR(32)); 4405CREATE TABLE uncle (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c CHAR(32)); 4406CREATE TABLE child (parent_id INT, c CHAR(32), FOREIGN KEY (parent_id) REFERENCES parent (id)); 4407--echo # Turn off foreign key checking so we can add foreign key using 4408--echo # inplace algorithm. 4409SET FOREIGN_KEY_CHECKS=0; 4410--echo # The problem occured when a foreign key was added by ALTER TABLE 4411--echo # executed using inplace algorithm which still did table rebuild 4412--echo # internally. So we drop column to make operation non-instant for 4413--echo # sure. 4414ALTER TABLE child ADD COLUMN uncle_id INT, DROP COLUMN c, ADD CONSTRAINT FOREIGN KEY (uncle_id) REFERENCES uncle (id), ALGORITHM=INPLACE; 4415SET FOREIGN_KEY_CHECKS=1; 4416SHOW CREATE TABLE child; 4417DROP TABLES child, parent, uncle; 4418 4419 4420--echo # 4421--echo # Bug #28480149 "UPGRADE FAIL: FAILED TO ADD THE FOREIGN KEY 4422--echo # CONSTRAINT. MISSING INDEX FOR CONSTR". 4423--echo # 4424--echo # When InnoDB SE checks if a key can serve as a parent key for a foreign 4425--echo # key it takes into account columns from hidden part of the key. This 4426--echo # hidden part consists of primary key columns which are implicitly 4427--echo # added by InnoDB to non-primary keys. 4428--echo # 4429--echo # See dd_upgrade_test test for coverage of upgrade process for such 4430--echo # foreign keys. 4431--echo # 4432CREATE TABLE parent (pk INT PRIMARY KEY, a INT, b INT, KEY(a), UNIQUE(b)); 4433--echo # This works both for non-unique and unique keys. 4434CREATE TABLE child1 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk)); 4435CREATE TABLE child2 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (b, pk)); 4436DROP TABLES child1, child2; 4437--echo # This also works when foreign keys are added by ALTER TABLE. 4438CREATE TABLE child1 (fk1 INT, fk2 INT); 4439ALTER TABLE child1 ADD FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk); 4440CREATE TABLE child2 (fk1 INT, fk2 INT); 4441ALTER TABLE child2 ADD FOREIGN KEY (fk1, fk2) REFERENCES parent (b, pk); 4442DROP TABLES child1, child2, parent; 4443--echo # And for complex multi-column cases too. 4444CREATE TABLE parent (a INT, b INT, c INT, PRIMARY KEY (a,b), KEY(c, a)); 4445CREATE TABLE child (fk1 INT, fk2 INT, fk3 INT, FOREIGN KEY (fk1, fk2, fk3) REFERENCES parent (c, a, b)); 4446DROP TABLES child, parent; 4447--echo # Moreover, this works for keys which are promoted to primary. 4448CREATE TABLE parent (u INT NOT NULL, a INT, b INT, UNIQUE(u), KEY(a), UNIQUE(b)); 4449CREATE TABLE child1 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (a, u)); 4450CREATE TABLE child2 (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (b, u)); 4451DROP TABLES child1, child2, parent; 4452--echo # Also works correctly when parent table is altered. 4453CREATE TABLE parent (pk INT PRIMARY KEY, a INT, KEY k1(a, pk), UNIQUE k2(a)); 4454CREATE TABLE child (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk)); 4455ALTER TABLE parent DROP KEY k1; 4456DROP TABLES child, parent; 4457--echo # And when we add parent table to previously orphan foreign key. 4458SET FOREIGN_KEY_CHECKS=0; 4459CREATE TABLE child (fk1 INT, fk2 INT, CONSTRAINT c FOREIGN KEY (fk1, fk2) REFERENCES parent (a, pk)); 4460SET FOREIGN_KEY_CHECKS=1; 4461CREATE TABLE parent (pk INT PRIMARY KEY, a INT, UNIQUE ua(a)); 4462SELECT constraint_name, unique_constraint_name FROM information_schema.referential_constraints 4463 WHERE constraint_schema='test' AND constraint_name='c'; 4464DROP TABLES child, parent; 4465--echo # However, columns which are implicitly added by InnoDB as hidden 4466--echo # elements to the primary key are not considered when searching for 4467--echo # parent key (because primary key contains hidden system DB_TRX_ID, 4468--echo # DB_ROLL_PTR columns before hidden normal columns). 4469CREATE TABLE parent (pk INT PRIMARY KEY, a INT); 4470--error ER_FK_NO_INDEX_PARENT 4471CREATE TABLE child (fk1 INT, fk2 INT, FOREIGN KEY (fk1, fk2) REFERENCES parent (pk, a)); 4472DROP TABLE parent; 4473--echo # Hidden parts which are column prefixes (because primary key has prefix 4474--echo # parts [sic!]) are not allowed as parent key parts. 4475CREATE TABLE parent (a CHAR(10), b int, KEY(b), PRIMARY KEY (a(5))); 4476--error ER_FK_NO_INDEX_PARENT 4477CREATE TABLE child (fk1 int, fk2 CHAR(10), FOREIGN KEY (fk1, fk2) REFERENCES parent (b, a)); 4478DROP TABLE parent; 4479--echo # Moreover, even hidden parts for full columns are not allowed if 4480--echo # primary key contains prefix parts. This was supported in 5.7. 4481CREATE TABLE parent (a INT, b CHAR(10), c int, KEY(c), PRIMARY KEY (a, b(5))); 4482--error ER_FK_NO_INDEX_PARENT 4483CREATE TABLE child (fk1 int, fk2 int, FOREIGN KEY (fk1, fk2) REFERENCES parent (c, a)); 4484DROP TABLE parent; 4485 4486--echo # 4487--echo # Now similar tests for self-referencing foreign keys. 4488--echo # 4489CREATE TABLE self1 (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT, 4490 KEY(a), FOREIGN KEY (fk1, fk2) REFERENCES self1 (a, pk)); 4491CREATE TABLE self2 (pk INT PRIMARY KEY, b INT, fk1 INT, fk2 INT, 4492 UNIQUE(b), FOREIGN KEY (fk1, fk2) REFERENCES self2 (b, pk)); 4493DROP TABLES self1, self2; 4494CREATE TABLE self1 (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT, KEY(a)); 4495ALTER TABLE self1 ADD FOREIGN KEY (fk1, fk2) REFERENCES self1 (a, pk); 4496CREATE TABLE self2 (pk INT PRIMARY KEY, b INT, fk1 INT, fk2 INT, UNIQUE(b)); 4497ALTER TABLE self2 ADD FOREIGN KEY (fk1, fk2) REFERENCES self2 (b, pk); 4498DROP TABLES self1, self2; 4499--echo # Test for complex multi-column case. 4500CREATE TABLE self (a INT, b INT, c INT, fk1 INT, fk2 INT, fk3 INT, 4501 PRIMARY KEY (a,b), KEY(c, a), 4502 FOREIGN KEY (fk1, fk2, fk3) REFERENCES self (c, a, b)); 4503DROP TABLE self; 4504--echo # Test for removing parent key. 4505CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT, 4506 KEY k1(a, pk), UNIQUE k2(a), 4507 FOREIGN KEY (fk1, fk2) REFERENCES self (a, pk)); 4508ALTER TABLE self DROP KEY k1; 4509DROP TABLE self; 4510--echo # But again this is not supposed to work for hidden columns added to 4511--echo # primary key. 4512--error ER_FK_NO_INDEX_PARENT 4513CREATE TABLE self (pk INT PRIMARY KEY, a INT, fk1 INT, fk2 INT, 4514 FOREIGN KEY (fk1, fk2) REFERENCES self (pk, a)); 4515--echo # Hidden parts which are column prefixes should not work. 4516--error ER_FK_NO_INDEX_PARENT 4517CREATE TABLE self (a CHAR(10), b int, fk1 int, fk2 CHAR(10), KEY(b), 4518 PRIMARY KEY (a(5)), FOREIGN KEY (fk1, fk2) REFERENCES self (b, a)); 4519--echo # Hidden parts for full columns are not allowed either if 4520--echo # primary key contains prefix parts. This was supported in 5.7. 4521--error ER_FK_NO_INDEX_PARENT 4522CREATE TABLE self (a INT, b CHAR(10), c int, fk1 int, fk2 int, KEY(c), 4523 PRIMARY KEY (a, b(5)), FOREIGN KEY (fk1, fk2) REFERENCES self (c, a)); 4524 4525 4526--echo # 4527--echo # Bug#21308781 "DROP FOREIGN KEY LEAD TO INCONSISTENT TABLE STRUCTURE 4528--echo # ON MASTER AND SLAVE". 4529--echo # 4530CREATE TABLE t1(pk INT PRIMARY KEY); 4531CREATE TABLE t2(pk INT PRIMARY KEY); 4532CREATE TABLE t3(fk1 INT, fk2 INT, KEY k1(fk1), 4533 CONSTRAINT a FOREIGN KEY (fk1) REFERENCES t1(pk), 4534 CONSTRAINT b FOREIGN KEY (fk2) REFERENCES t2(pk)); 4535--error ER_DROP_INDEX_FK 4536ALTER TABLE t3 DROP KEY k1, DROP FOREIGN KEY b, ALGORITHM=COPY; 4537--echo # Failed ALTER TABLE should have left table intact! 4538SHOW CREATE TABLE t3; 4539DROP TABLES t3, t2, t1; 4540 4541 4542--echo # 4543--echo # Bug#30214965 "GENERATION OF FK NAMES IS NOT RE-EXECUTION SAFE". 4544--echo # 4545CREATE TABLE parent (pk INT PRIMARY KEY); 4546CREATE TABLE child (fk INT, a INT); 4547PREPARE stmt1 FROM 'ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent (pk)'; 4548EXECUTE stmt1; 4549SHOW CREATE TABLE child; 4550ALTER TABLE child RENAME COLUMN fk TO fkold, RENAME COLUMN a TO fk; 4551--echo # Re-execution of ALTER TABLE ADD FOREIGN KEY should succeed. 4552--echo # New foreign key should get new foreign key name. 4553EXECUTE stmt1; 4554SHOW CREATE TABLE child; 4555DEALLOCATE PREPARE stmt1; 4556DROP TABLES child, parent; 4557 4558 4559--echo # 4560--echo # Bug#30267236 "REPLACE DICT_FOREIGN_PARSE_DROP_CONSTRAINTS() WITH 4561--echo # SQL-LAYER CHECK." 4562--echo # 4563 4564CREATE TABLE parent (pk INT PRIMARY KEY); 4565CREATE TABLE child (fk1 INT, b INT, CONSTRAINT c FOREIGN KEY (fk1) REFERENCES parent (pk)); 4566CREATE TABLE unrelated (a INT); 4567 4568--echo # 4569--echo # Dropping non-existing foreign key should fail with consistent error. 4570--error ER_CANT_DROP_FIELD_OR_KEY 4571ALTER TABLE child DROP FOREIGN KEY no_such_fk; 4572--error ER_CANT_DROP_FIELD_OR_KEY 4573ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=INPLACE; 4574--error ER_CANT_DROP_FIELD_OR_KEY 4575ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=COPY; 4576 4577--echo # 4578--echo # Dropping existing foreign key using wrong table name should fail 4579--echo # with consistent error as well. 4580--error ER_CANT_DROP_FIELD_OR_KEY 4581ALTER TABLE unrelated DROP FOREIGN KEY c; 4582--error ER_CANT_DROP_FIELD_OR_KEY 4583ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=INPLACE; 4584--error ER_CANT_DROP_FIELD_OR_KEY 4585ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=COPY; 4586 4587--echo # 4588--echo # The same should happen if one tries to drop foreign key using 4589--echo # parent table. 4590--error ER_CANT_DROP_FIELD_OR_KEY 4591ALTER TABLE parent DROP FOREIGN KEY c; 4592--error ER_CANT_DROP_FIELD_OR_KEY 4593ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=INPLACE; 4594--error ER_CANT_DROP_FIELD_OR_KEY 4595ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=COPY; 4596DROP TABLES unrelated, child, parent; 4597 4598 4599--echo # 4600--echo # Bug#16904122 "MULTIPLE FK WITH SAME NAME ALLOWED IN ALTER". 4601--echo # 4602CREATE TABLE parent (pk INT PRIMARY KEY); 4603CREATE TABLE child (fk1 INT, fk2 INT, a INT, KEY(fk1), KEY(fk2)); 4604INSERT INTO child VALUES (NULL, NULL, 1), (NULL, NULL, 1); 4605--echo # Usage of duplicate foreign key name should be detected before 4606--echo # proceeding to COPYing of rows (and thus duplicate key error) 4607--echo # and checking if INPLACE algorithm is supported. 4608--error ER_FK_DUP_NAME 4609ALTER TABLE child ADD CONSTRAINT f FOREIGN KEY (fk1) REFERENCES parent (pk), 4610 ADD CONSTRAINT f FOREIGN KEY (fk2) REFERENCES parent (pk), 4611 ADD UNIQUE (a); 4612--error ER_FK_DUP_NAME 4613ALTER TABLE child ADD CONSTRAINT f FOREIGN KEY (fk1) REFERENCES parent (pk), 4614 ADD CONSTRAINT f FOREIGN KEY (fk2) REFERENCES parent (pk), 4615 ADD UNIQUE (a), ALGORITHM=COPY; 4616--error ER_FK_DUP_NAME 4617ALTER TABLE child ADD CONSTRAINT f FOREIGN KEY (fk1) REFERENCES parent (pk), 4618 ADD CONSTRAINT f FOREIGN KEY (fk2) REFERENCES parent (pk), 4619 ADD UNIQUE (a), ALGORITHM=INPLACE; 4620--echo # Also check case when we created duplicate to already pre-existing 4621--echo # foreign key name. Also check that foreign key name comparison is 4622--echo # case-insensitive in this case. 4623ALTER TABLE child ADD CONSTRAINT f FOREIGN KEY (fk1) REFERENCES parent (pk); 4624--error ER_FK_DUP_NAME 4625ALTER TABLE child ADD CONSTRAINT F FOREIGN KEY (fk2) REFERENCES parent (pk), 4626 ADD UNIQUE (a); 4627--error ER_FK_DUP_NAME 4628ALTER TABLE child ADD CONSTRAINT F FOREIGN KEY (fk2) REFERENCES parent (pk), 4629 ADD UNIQUE (a), ALGORITHM=COPY; 4630--error ER_FK_DUP_NAME 4631ALTER TABLE child ADD CONSTRAINT F FOREIGN KEY (fk2) REFERENCES parent (pk), 4632 ADD UNIQUE (a), ALGORITHM=INPLACE; 4633DROP TABLES child, parent; 4634 4635 4636--echo # 4637--echo # Bug#22364336 "ANOMALY IN ALTER TABLE ADD FOREIGN KEY". 4638--echo # 4639--echo # Spaces surrounding dot in qualified parent table name should 4640--echo # be allowed. 4641CREATE TABLE parent (pk INT PRIMARY KEY); 4642CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES test . parent (pk)); 4643DROP TABLE child; 4644CREATE TABLE child (fk INT); 4645ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES test . parent (pk); 4646DROP TABLES child, parent; 4647 4648 4649--echo # 4650--echo # Bug#28703793 "CANNOT ADD FOREIGN KEY CONSTRAINT TO TABLES INSIDE A 4651--echo # DATABASE NAMED `AUX`" 4652--echo # 4653CREATE DATABASE aux; 4654CREATE TABLE aux.parent (pk INT PRIMARY KEY); 4655CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES aux.parent (pk)); 4656DROP TABLE child; 4657DROP DATABASE aux; 4658 4659 4660--echo # 4661--echo # Bug#11754659 "CONDITIONAL COMMENTS NOT WORKING IN FOREIGN KEY DEFINITION". 4662--echo # 4663CREATE TABLE parent (id INT PRIMARY KEY); 4664--echo # Test if versioned conditional comments work in CREATE TABLE 4665--echo # (issue that was reported originally). 4666CREATE TABLE child (fk INT, CONSTRAINT c1 FOREIGN KEY (fk) REFERENCES parent (id) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */); 4667SHOW CREATE TABLE child; 4668ALTER TABLE child DROP FOREIGN KEY c1; 4669--echo # Also check if they work in ALTER TABLE and that MySQL-specific 4670--echo # conditional comments also work. 4671ALTER TABLE child ADD CONSTRAINT c2 FOREIGN KEY (fk) REFERENCES parent /*! (id) */ /*!40008 ON DELETE SET NULL */; 4672SHOW CREATE TABLE child; 4673DROP TABLES child, parent; 4674 4675 4676--echo # 4677--echo # Bug#21919887 "STATEMENTS IN "EXECUTABLE COMMENTS" ARE IGNORING 4678--echo # CONSTRAINTS". 4679--echo # 4680CREATE TABLE parent (pk INT PRIMARY KEY); 4681/*!50101 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) */; 4682SHOW CREATE TABLE child; 4683DROP TABLE child; 4684CREATE TABLE child (fk INT); 4685/*! ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk) */; 4686SHOW CREATE TABLE child; 4687DROP TABLES child, parent; 4688