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

Misleading error message due to FOREIGN KEY on RENAME TABLE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6
    • Fix Version/s: 10.6
    • Labels:
      None

      Description

      Matthias Leich reported that RENAME TABLE is reporting a misleading error message about the table existing, when in reality there is a duplicate FOREIGN KEY constraint. The following should be close to a minimal test case:

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
      CREATE TABLE t2 (a INT PRIMARY KEY, CONSTRAINT t3_ibfk_1 FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB;
      CREATE TABLE t2b (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB;
      --error ER_TABLE_EXISTS_ERROR
      RENAME TABLE t2b TO t3;
       
      DROP TABLE t2b,t2,t1;
      

      Note: the internal SYS_FOREIGN.NAME='test/t3_ibfk_1' is already occupied by table t2, and renaming the auto-assigned constraint test/t2b_ibfk_1 to test/t3_ibfk_1 would fail. The client only sees a misleading error message:

      ERROR 42S01: Table './test/t3' already exists
      

      More details are spammed to the server error log (but MDEV-25506 part 3 removed that spam in 10.6.2):

      10.2 c307dc6efde682c0768dc900818f4c0b418f9c6f

      2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: Possible reasons:
      2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: (1) Table rename would cause two FOREIGN KEY constraints to have the same internal name in case-insensitive comparison.
      2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: (2) Table `test`.`t3` exists in the InnoDB internal data dictionary though MySQL is trying to rename table `test`.`t2b` to it. Have you deleted the .frm file and not used DROP TABLE?
      2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: If table `test`.`t3` is a temporary table #sql..., then it can be that there are still queries running on the table, and it will be dropped automatically when the queries end. You can drop the orphaned table inside InnoDB by creating an InnoDB table with the same name in another database and copying the .frm file to the current database. Then MySQL thinks the table exists, and DROP TABLE will succeed.
      

      I think that we will need a dedicated error message for this case. But, I have understood that we want to avoid having storage engine specific error messages.

      As far as I understand, this error source should be removed by MDEV-16417 in a future version.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.