Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.4, 11.8
-
None
Description
While checking the foreign key constraint, InnoDB fails with foreign key constraint.
CREATE TABLE t (pk int PRIMARY KEY, a varchar(15), b char(15), KEY(a), key(b), FOREIGN KEY (a) REFERENCES t(b)) ENGINE=InnoDB;
|
|
|
SHOW CREATE TABLE t;
|
Table Create Table
|
t CREATE TABLE `t` (
|
`pk` int(11) NOT NULL,
|
`a` varchar(15) DEFAULT NULL,
|
`b` char(15) DEFAULT NULL,
|
PRIMARY KEY (`pk`),
|
KEY `a` (`a`),
|
KEY `b` (`b`),
|
CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`b`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
|
INSERT INTO t VALUES (1,'foo', 'foo');
|
|
Above insert fails with
query 'INSERT INTO t VALUES (1,'foo', 'foo')' failed: ER_NO_REFERENCED_ROW_2 (1452): Cannot add or update a child row: a foreign key constraint fails (`test`.`t`, CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`b`))
|
But for the similar schema, insert didn't fail with foreign key constraint
CREATE TABLE t (pk int PRIMARY KEY, a varchar(15), b char(15), KEY(b), FOREIGN KEY (a) REFERENCES t(b)) ENGINE=InnoDB;
|
SHOW CREATE TABLE t;
|
Table Create Table
|
t CREATE TABLE `t` (
|
`pk` int(11) NOT NULL,
|
`a` varchar(15) DEFAULT NULL,
|
`b` char(15) DEFAULT NULL,
|
PRIMARY KEY (`pk`),
|
KEY `b` (`b`),
|
KEY `a` (`a`),
|
CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`b`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
INSERT INTO t VALUES (1,'foo', 'foo');
|
Problem for the first case is that while inserting the row, InnoDB does insert into referencing index(a) first and try to do referenced index(b) last. While inserting the referencing index(a), InnoDB tries to find the matching row in referenced index(b) and fails.
But in 2nd case, InnoDB does insert into referenced index (b) first and later to the referencing index(a). So insert is successful in 2nd case.
This issue can be solved by deferring the foreign key constraint later after completing the row insert.
Attachments
Issue Links
- relates to
-
MDEV-26096 Constraints: support deferred constraints [WAS: Make the Unique index ONLY evaluate immediately before the commit (NOT after each UPDATE)]
-
- Open
-
-
-
- Closed
-
The implementation of this should include the test case of
MDEV-36112and ensure that the deadlock is no longer reproducible.