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
          valerii Valerii Kravchuk created issue -
          valerii Valerii Kravchuk made changes -
          Field Original Value New Value
          Labels upstream
          serg Sergei Golubchik made changes -
          Description The below test case shows that you can create an ibd file without an frm file using sql and foreign keys:

          {noformat}
          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);
          {noformat}

          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).           		The below test case shows that you can create an ibd file without an frm file using sql and foreign keys:

          {code:sql}
          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);
          {code}

          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).
          serg Sergei Golubchik made changes -
          serg Sergei Golubchik made changes -
          serg Sergei Golubchik made changes -
          Assignee Jan Lindström [ jplindst ]
          serg Sergei Golubchik made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.1 [ 16100 ]
          jplindst Jan Lindström (Inactive) made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Sprint 10.1.15 [ 75 ]
          jplindst Jan Lindström (Inactive) made changes -
          Fix Version/s 5.5 [ 15800 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0 [ 16000 ]
          jplindst Jan Lindström (Inactive) made changes -
          issue.field.resolutiondate 2016-06-23 04:59:37.0 2016-06-23 04:59:37.966
          jplindst Jan Lindström (Inactive) made changes -
          Fix Version/s 5.5.51 [ 22015 ]
          Fix Version/s 10.0.26 [ 22016 ]
          Fix Version/s 10.1.15 [ 22018 ]
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 75701 ] MariaDB v4 [ 150436 ]

          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.