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

ALTER IGNORE ... ADD FOREIGN KEY ... ALGORITHM=COPY causes bogus ER_TABLE_EXISTS_ERROR and lengthy InnoDB error message

    XMLWordPrintable

    Details

      Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB;
      CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB;
       
      ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f);
      ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1), ALGORITHM=COPY;
       
      # Cleanup
      DROP TABLE t1, t2;
      

      10.0 802ce9672f

      CURRENT_TEST: bug.t5a
      mysqltest: At line 7: query 'ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1), ALGORITHM=COPY' failed: 1050: Table './test/t1' already exists
      

      2019-01-05 00:14:36 7f391882c700  InnoDB: Error; possible reasons:
      InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
      InnoDB: to have the same internal name in case-insensitive comparison.
      InnoDB: 2) table `test`.`t1` exists in the InnoDB internal data
      InnoDB: dictionary though MySQL is trying to rename table `test`.`#sql-5a68_3` to it.
      InnoDB: Have you deleted the .frm file and not used DROP TABLE?
      InnoDB: You can look for further help from
      InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
      InnoDB: If table `test`.`t1` is a temporary table #sql..., then it can be that
      InnoDB: there are still queries running on the table, and it will be
      InnoDB: dropped automatically when the queries end.
      InnoDB: You can drop the orphaned table inside InnoDB by
      InnoDB: creating an InnoDB table with the same name in another
      InnoDB: database and copying the .frm file to the current database.
      InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
      InnoDB: succeed.
      

      Also reproducible on MySQL 5.6.
      MySQL 5.7 doesn't understand ALTER IGNORE, so the test case is not applicable.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: