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

Orphan ibd file when playing with foreign keys

Details

    • 10.1.15

    Description

      The below test case shows that you can create an ibd file without an frm file using sql and foreign keys:

      drop database if exists bug_fk;
      		 
      create database bug_fk;
      		 
      use bug_fk;
      		 
      CREATE TABLE b (
      		 
        b int unsigned NOT NULL,
      		 
        d1 datetime NOT NULL,
      		 
        PRIMARY KEY (b,d1)
      		 
      ) ENGINE=InnoDB;
      		 
       
      		 
      CREATE TABLE c (
      		 
        b int unsigned NOT NULL,
      		 
        d1 datetime NOT NULL,
      		 
        d2 datetime NOT NULL,
      		 
        PRIMARY KEY (b,d1),
      		 
        CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b)
      		 
      ) ENGINE=InnoDB;
      		 
      set foreign_key_checks = 0;
      		 
      DROP TABLE IF EXISTS b;
      		 
      CREATE TABLE b (
      		 
        b bigint unsigned NOT NULL,
      		 
        d1 date NOT NULL,
      		 
        PRIMARY KEY (b,d1)
      		 
      ) ENGINE=InnoDB;
      		 
       
      		 
      DROP TABLE IF EXISTS d;
      		 
       
      		 
      CREATE TABLE d (
      		 
        b bigint unsigned NOT NULL,
      		 
        d1 date NOT NULL,
      		 
        PRIMARY KEY (b,d1),
      		 
        CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b)
      		 
      ) ENGINE=InnoDB;
      		 
       
      		 
      set foreign_key_checks = 1;
      		 
      CREATE TABLE b (
      		 
        b bigint unsigned NOT NULL,
      		 
        d1 date NOT NULL,
      		 
        PRIMARY KEY (b,d1)
      		 
      ) ENGINE=InnoDB;
      		 
      set foreign_key_checks=0;
      		 
      drop table c;
      		 
      drop table d;
      		 
      create table b(id int);

      Check https://bugs.mysql.com/bug.php?id=81444 and https://bugs.launchpad.net/percona-server/+bug/1582501 for more details (note that -f flag is NOT needed in case of mysql client from MariaDB, as it continues to execute statements in case of SQL error by default).

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          jplindst Jan Lindström (Inactive) added a comment -

          Also 5.5 effected.

          jplindst Jan Lindström (Inactive) added a comment - Also 5.5 effected.
          jplindst Jan Lindström (Inactive) added a comment -

          For 5.5:

          commit ef92aaf9ece92c873ae0f3448ab2274c958ba3fe
          Author: Jan Lindström <jan.lindstrom@mariadb.com>
          Date: Wed Jun 22 22:37:28 2016 +0300

          MDEV-10083: Orphan ibd file when playing with foreign keys

          Analysis: row_drop_table_for_mysql did not allow dropping
          referenced table even in case when actual creating of the
          referenced table was not successfull if foreign_key_checks=1.

          Fix: Allow dropping referenced table even if foreign_key_checks=1
          if actual table create returned error.

          jplindst Jan Lindström (Inactive) added a comment - For 5.5: commit ef92aaf9ece92c873ae0f3448ab2274c958ba3fe Author: Jan Lindström <jan.lindstrom@mariadb.com> Date: Wed Jun 22 22:37:28 2016 +0300 MDEV-10083 : Orphan ibd file when playing with foreign keys Analysis: row_drop_table_for_mysql did not allow dropping referenced table even in case when actual creating of the referenced table was not successfull if foreign_key_checks=1. Fix: Allow dropping referenced table even if foreign_key_checks=1 if actual table create returned error.
          jplindst Jan Lindström (Inactive) added a comment -

          For 10.0:

          commit 26de9061e8c4462152b4bcff2b9cbb80cde12de1
          Author: Jan Lindström <jan.lindstrom@mariadb.com>
          Date: Thu Jun 23 07:42:40 2016 +0300

          Merge following commit from 5.5:

          commit ef92aaf9ece92c873ae0f3448ab2274c958ba3fe
          Author: Jan Lindström <jan.lindstrom@mariadb.com>
          Date: Wed Jun 22 22:37:28 2016 +0300

          MDEV-10083: Orphan ibd file when playing with foreign keys

          Analysis: row_drop_table_for_mysql did not allow dropping
          referenced table even in case when actual creating of the
          referenced table was not successfull if foreign_key_checks=1.

          Fix: Allow dropping referenced table even if foreign_key_checks=1
          if actual table create returned error.

          jplindst Jan Lindström (Inactive) added a comment - For 10.0: commit 26de9061e8c4462152b4bcff2b9cbb80cde12de1 Author: Jan Lindström <jan.lindstrom@mariadb.com> Date: Thu Jun 23 07:42:40 2016 +0300 Merge following commit from 5.5: commit ef92aaf9ece92c873ae0f3448ab2274c958ba3fe Author: Jan Lindström <jan.lindstrom@mariadb.com> Date: Wed Jun 22 22:37:28 2016 +0300 MDEV-10083 : Orphan ibd file when playing with foreign keys Analysis: row_drop_table_for_mysql did not allow dropping referenced table even in case when actual creating of the referenced table was not successfull if foreign_key_checks=1. Fix: Allow dropping referenced table even if foreign_key_checks=1 if actual table create returned error.

          People

            jplindst Jan Lindström (Inactive)
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.