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

Bogus ER_TABLE_EXISTS_ERROR upon an attempt to add a foreign key

Details

    Description

      --source include/have_innodb.inc
      		 
       
      		 
      CREATE TABLE t1 (f1 INT, f2 INT, KEY(f1), KEY(f2)) ENGINE=InnoDB;
      		 
       
      		 
      CREATE TABLE t2 (a INT, b INT, KEY(b)) ENGINE = InnoDB;
      		 
      ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t1(f1) ON DELETE SET NULL;
      		 
       
      		 
      SET foreign_key_checks= OFF;
      		 
      ALTER TABLE t2 MODIFY a CHAR(1) NOT NULL;
      		 
      SET foreign_key_checks= ON;
      		 
       
      		 
      ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (b) REFERENCES t1 (f2);
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t2, t1;

      10.2 54d7ba96

      		 
      mysqltest: At line 12: query 'ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (b) REFERENCES t1 (f2)' failed: 1050: Table './test/t2' already exists

      Reproducible on 10.2-10.6.

      It happens even if we also modify t1.f1 to CHAR(1) NOT NULL after the first ALTER, to restore the consistency between column definitions. It still leaves the foreign key invalid though, because it can't really set NULL to a non-NULL column; so, some error is probably expected, but ER_TABLE_EXISTS_ERROR is clearly a wrong choice.

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31086 MODIFY COLUMN can break FK constraints, and lead to unrestorable dumps

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25620 InnoDB: Failing assertion: id != 0 or Assertion `dict_table_is_file_per_table(this)' failed in dict_table_t::rename_tablespace

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Description {code:sql}
            --source include/have_innodb.inc

            CREATE TABLE t1 (f1 INT, f2 INT, KEY(f1), KEY(f2)) ENGINE=InnoDB;

            CREATE TABLE t2 (a INT, b INT, KEY(b)) ENGINE = InnoDB;
            ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t1(f1) ON DELETE SET NULL;

            SET foreign_key_checks= OFF;
            ALTER TABLE t2 MODIFY a CHAR(1) NOT NULL;
            SET foreign_key_checks= ON;

            ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (b) REFERENCES t1 (f2);

            # Cleanup
            DROP TABLE t2, t1;
            {code}

            {noformat:title=10.2 54d7ba96}
            mysqltest: At line 12: query 'ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (b) REFERENCES t1 (f2)' failed: 1050: Table './test/t2' already exists
            {noformat}

            Reproducible on 10.2-10.6.

            It happens even if we also modify {{t1.f1}} to {{CHAR(1) NOT NULL}} after the first ALTER, to restore the consistency between data types. It still leaves the foreign key invalid though, because it can't really set NULL to a non-NULL column; so, *some* error is probably expected, but ER_TABLE_EXISTS_ERROR is clearly a wrong choice.             		{code:sql}
            --source include/have_innodb.inc

            CREATE TABLE t1 (f1 INT, f2 INT, KEY(f1), KEY(f2)) ENGINE=InnoDB;

            CREATE TABLE t2 (a INT, b INT, KEY(b)) ENGINE = InnoDB;
            ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t1(f1) ON DELETE SET NULL;

            SET foreign_key_checks= OFF;
            ALTER TABLE t2 MODIFY a CHAR(1) NOT NULL;
            SET foreign_key_checks= ON;

            ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (b) REFERENCES t1 (f2);

            # Cleanup
            DROP TABLE t2, t1;
            {code}

            {noformat:title=10.2 54d7ba96}
            mysqltest: At line 12: query 'ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (b) REFERENCES t1 (f2)' failed: 1050: Table './test/t2' already exists
            {noformat}

            Reproducible on 10.2-10.6.

            It happens even if we also modify {{t1.f1}} to {{CHAR(1) NOT NULL}} after the first ALTER, to restore the consistency between column definitions. It still leaves the foreign key invalid though, because it can't really set NULL to a non-NULL column; so, *some* error is probably expected, but ER_TABLE_EXISTS_ERROR is clearly a wrong choice.
            elenst Elena Stepanova made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 121688 ] MariaDB v4 [ 143688 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.2 [ 14601 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Duplicate [ 3 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            thiru Thirunarayanan Balathandayuthapani made changes -

            People

              thiru Thirunarayanan Balathandayuthapani
              elenst Elena Stepanova
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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