Orphan .ibd files when playing with foreign keys

[Valerii Kravchuk]

This was reported upstream as http://bugs.mysql.com/bug.php?id=81444 by Arnaud Adant, but maybe Percona can fix this in XtraDB faster.

I use the same test case:

openxs@ubuntu:~$ mysql -uroot -proot -f test
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.11-4-log Percona Server (GPL), Release '4', Revision '5c940e1'

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop database if exists bug_fk;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create database bug_fk;
Query OK, 1 row affected (0.02 sec)

mysql> use bug_fk;
Database changed
mysql> CREATE TABLE b (
    -> b int unsigned NOT NULL,
    -> d1 datetime NOT NULL,
    -> PRIMARY KEY (b,d1)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> 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;
Query OK, 0 rows affected (0.08 sec)

mysql> set foreign_key_checks = 0;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS b;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE b (
    -> b bigint unsigned NOT NULL,
    -> d1 date NOT NULL,
    -> PRIMARY KEY (b,d1)
    -> ) ENGINE=InnoDB;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> DROP TABLE IF EXISTS d;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> 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;
Query OK, 0 rows affected (0.12 sec)

mysql> set foreign_key_checks = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE b (
    -> b bigint unsigned NOT NULL,
    -> d1 date NOT NULL,
    -> PRIMARY KEY (b,d1)
    -> ) ENGINE=InnoDB;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table c;
Query OK, 0 rows affected (0.07 sec)

mysql> drop table d;
Query OK, 0 rows affected (2.38 sec)

mysql> create table b(id int);
ERROR 1813 (HY000): Tablespace '`bug_fk`.`b`' exists.
mysql>
mysql> exit
Bye

Now this is what we have in the database directory:

openxs@ubuntu:~$ sudo ls -l /var/lib/mysql/bug_fk
[sudo] password for openxs:
total 100
-rw-r----- 1 mysql mysql 98304 May 17 06:51 b.ibd
-rw-r----- 1 mysql mysql 65 May 17 06:50 db.opt
openxs@ubuntu:~$

b.ibd should not exist without b.frm.

[Jericho Rivera (jericho-rivera)]

Easily reproducible in PS 5.6.29 as well. Upstream bug also verified, marking this as confirmed.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1710