Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35957

Defer Foreign key constraint check

Details

    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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-26096 Constraints: support deferred constraints [WAS: Make the Unique index ONLY evaluate immediately before the commit (NOT after each UPDATE)]

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36112 Deadlock Issue with Foreign Key Constraints on concurrent execution

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            marko Marko Mäkelä added a comment -

            The implementation of this should include the test case of MDEV-36112 and ensure that the deadlock is no longer reproducible.

            marko Marko Mäkelä added a comment - The implementation of this should include the test case of MDEV-36112 and ensure that the deadlock is no longer reproducible.

            People

              thiru Thirunarayanan Balathandayuthapani
              thiru Thirunarayanan Balathandayuthapani
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.