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

Rename table operation using copy algorithm fails to rename the foreign key constraint

    XMLWordPrintable

Details

    Description

      InnoDB fails to rename the foreign key constraint during rename table alter using
      copy algorithm.

      The following test case repeats the scenario:

      --source include/have_innodb.inc
      create table t1(f1 int not null, index(f1))engine=innodb;
       
      create table t2(f1 int not null,
      foreign key(f1) references t1(f1))engine=innodb;
       
      alter table t1 rename to tm1, algorithm=copy;
       
      show create table tm1;
      Table	Create Table
      tm1	CREATE TABLE `tm1` (
        `f1` int(11) NOT NULL,
        KEY `f1` (`f1`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       
      show create table t2;
      Table	Create Table
      t2	CREATE TABLE `t2` (
        `f1` int(11) NOT NULL,
        KEY `f1` (`f1`),
        CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       
      select * from information_schema.innodb_sys_foreign;
      ID	FOR_NAME	REF_NAME	N_COLS	TYPE
      test/t2_ibfk_1	test/t2	test/t1	1	0
       
       
      create table t1(f1 int not null)engine=innodb;
      Error failed: 1215: Cannot add foreign key constraint
      

      Analysis:
      =========
      Copy alter algorithm does the following steps:
      1) Create a new table (#sql) and copies the row from old table

      • Doesn't add anything in SYS_FOREIGN

      2) Rename t1 to #sql2

      • Rename the table name in InnoDB dictionary
      • Rename the file name too
      • Doesn't rename the constraint in SYS_FOREIGN tables

      3) RENAME #sql to tm1

      • Rename the table name in InnoDB dictionary
      • Loads the foreign & referenced index from SYS_FOREIGN
        using the table name(tm1). But InnoDB doesn't have any FK constraint
        with the name (tm1)

      4) DROP #sql2

      • Does check whether the SYS_FOREIGN has the foreign key constraint using the table name.

      Attachments

        Issue Links

          Activity

            People

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