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

Failed to INSERT a proper value when no FOREIGN KEY violation

Details

    Description

      Run the following statements, in which the last INSERT statement should succeed, since there is no FOREIGN KEY violation. But an error is retuned.

      CREATE TABLE t0 (c2 FLOAT8, PRIMARY KEY (c2));
      		 
      CREATE TABLE t1 (c1 FLOAT8 UNIQUE, FOREIGN KEY (c1) REFERENCES t0(c2) ON UPDATE CASCADE);
      		 
      ALTER TABLE t0 RENAME AS t2, ALGORITHM COPY;
      		 
      INSERT INTO t2 (c2) VALUES (1);
      		 
      INSERT INTO t1 (c1) VALUES (1); -- actual: failed, expected: succeed

      I got the following error message, which seems incorrect, since there is no table with name t0.

      ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t0` (`c2`) ON UPDATE CASCADE)

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25885 Rename table operation using copy algorithm fails to rename the foreign key constraint

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            John Jove John Jove created issue -
            John Jove John Jove added a comment -

            When executing `SHOW CREATE TABLE t1`, an incorrect table definition is returned, since there is no table t0;

             CREATE TABLE `t1` (
            		 
              `c1` double DEFAULT NULL,
            		 
              UNIQUE KEY `c1` (`c1`),
            		 
              CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t0` (`c2`) ON UPDATE CASCADE
            		 
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

            Unsurprisingly, the execution of `DROP TABLE t2` succeed, since an incorrect constraint is maintained.

            John Jove John Jove added a comment - When executing `SHOW CREATE TABLE t1`, an incorrect table definition is returned, since there is no table t0; CREATE TABLE `t1` ( `c1` double DEFAULT NULL , UNIQUE KEY `c1` (`c1`), CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t0` (`c2`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_general_ci Unsurprisingly, the execution of `DROP TABLE t2` succeed, since an incorrect constraint is maintained.
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Fix Version/s 11.2 [ 28603 ]
            serg Sergei Golubchik made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ]
            marko Marko Mäkelä added a comment -

            The root cause of the problem would be highlighted by the following:

            --source include/have_innodb.inc
            		 
            CREATE TABLE t0 (c2 FLOAT8, PRIMARY KEY (c2)) ENGINE=InnoDB;
            		 
            CREATE TABLE t1 (c1 FLOAT8 UNIQUE, FOREIGN KEY (c1) REFERENCES t0(c2) ON UPDATE CASCADE) ENGINE=InnoDB;
            		 
            ALTER TABLE t0 RENAME AS t2, ALGORITHM COPY;
            		 
            SHOW CREATE TABLE t1;

            I tested it in a little older major version:

            10.6 887bb3f73555ff8a50138a580ca8308b9b5c069c

            		 
              CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t0` (`c2`) ON UPDATE CASCADE

            If I change the COPY to NOCOPY in the ALTER TABLE statement or if I replace the statement with RENAME TABLE, the FOREIGN KEY constraint in t1 will be adjusted to point to t2.

            I would assume this bug to exist in all MariaDB Server versions.

            marko Marko Mäkelä added a comment - The root cause of the problem would be highlighted by the following: --source include/have_innodb.inc CREATE TABLE t0 (c2 FLOAT8, PRIMARY KEY (c2)) ENGINE=InnoDB; CREATE TABLE t1 (c1 FLOAT8 UNIQUE , FOREIGN KEY (c1) REFERENCES t0(c2) ON UPDATE CASCADE ) ENGINE=InnoDB; ALTER TABLE t0 RENAME AS t2, ALGORITHM COPY; SHOW CREATE TABLE t1; I tested it in a little older major version: 10.6 887bb3f73555ff8a50138a580ca8308b9b5c069c CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t0` (`c2`) ON UPDATE CASCADE If I change the COPY to NOCOPY in the ALTER TABLE statement or if I replace the statement with RENAME TABLE , the FOREIGN KEY constraint in t1 will be adjusted to point to t2 . I would assume this bug to exist in all MariaDB Server versions.
            marko Marko Mäkelä made changes -
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.1 [ 28549 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.11 [ 27614 ]
            Affects Version/s 11.0 [ 28320 ]
            Affects Version/s 11.1 [ 28549 ]
            Affects Version/s 11.2 [ 28603 ]
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.0 [ 28320 ]
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani added a comment -

            Since this issue has workaround like nocopy or inplace and it exist in all older version.
            So reducing the priority

            thiru Thirunarayanan Balathandayuthapani added a comment - Since this issue has workaround like nocopy or inplace and it exist in all older version. So reducing the priority
            thiru Thirunarayanan Balathandayuthapani made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.1 [ 28549 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.2(EOL) [ 28603 ]

            People

              thiru Thirunarayanan Balathandayuthapani
              John Jove John Jove
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.