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

Orphan ibd file when playing with foreign keys

    XMLWordPrintable

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

          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.