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

Missing FK constraint after changing column type under disabled FK checks

    XMLWordPrintable

Details

    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

          Activity

            People

              marko Marko Mäkelä
              nikitamalyavin Nikita Malyavin
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.