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

    XMLWordPrintable

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

          Activity

            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.