Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
None
Description
In the foolowing test there are two ALTER TABLES. After the first one, a foreign key constraint
is gone missing, but it is returned after the second ALTER TABLE.
--source include/have_innodb.inc
|
call mtr.add_suppression(
|
"has or is referenced in foreign key constraints"); |
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, a INT, KEY(a)) |
ENGINE=InnoDB;
|
CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, |
FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=InnoDB; |
|
SET FOREIGN_KEY_CHECKS= OFF; |
ALTER TABLE t2 MODIFY COLUMN b YEAR; |
SHOW CREATE TABLE t2; |
--connect (con1,localhost,root,,test)
|
INSERT INTO t2 values (1, 1); |
|
ALTER TABLE t2 ADD INDEX ind (b); |
SHOW CREATE TABLE t2; |
--connection con1
|
INSERT INTO t2 values (2, 2); |
|
#Cleanup
|
DROP TABLE t2, t1; |
SHOW CREATE outputs are according, as well as the DML behavior in the concurrent connection:
10.6 2ac1edb1 |
SET FOREIGN_KEY_CHECKS= OFF;
|
ALTER TABLE t2 MODIFY COLUMN b YEAR;
|
SHOW CREATE TABLE t2;
|
Table Create Table
|
t2 CREATE TABLE `t2` (
|
`pk` int(11) NOT NULL,
|
`b` year(4) DEFAULT NULL,
|
PRIMARY KEY (`pk`),
|
KEY `b` (`b`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
connect con1,localhost,root,,test;
|
INSERT INTO t2 values (1, 1);
|
ALTER TABLE t2 ADD INDEX ind (b);
|
SHOW CREATE TABLE t2;
|
Table Create Table
|
t2 CREATE TABLE `t2` (
|
`pk` int(11) NOT NULL,
|
`b` year(4) DEFAULT NULL,
|
PRIMARY KEY (`pk`),
|
KEY `ind` (`b`),
|
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
connection con1;
|
INSERT INTO t2 values (2, 2);
|
|
mysqltest: At line 21: query 'INSERT INTO t2 values (2, 2)' failed: ER_NO_REFERENCED_ROW_2 (1452): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
|
Attachments
Issue Links
- relates to
-
MDEV-18421 Server crash or assertion `foreign->foreign_table != __null' failed in init_fts_doc_id_for_ref
- Stalled