Orphan .ibd files when playing with foreign keys

Bug #1582501 reported by Valerii Kravchuk
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Invalid
Undecided
Unassigned
5.6
Triaged
Medium
Unassigned
5.7
Triaged
Medium
Unassigned

Bug Description

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.

tags: added: upstream
Revision history for this message
Jericho Rivera (jericho-rivera) wrote :

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

Changed in percona-server:
status: New → Confirmed
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.