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

Orphan ibd file when playing with foreign keys

    Details

    • Sprint:
      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

            • Assignee:
              jplindst Jan Lindström
              Reporter:
              valerii Valerii Kravchuk
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: