Orphan .ibd files when playing with foreign keys
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://
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_
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/
[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 |
Easily reproducible in PS 5.6.29 as well. Upstream bug also verified, marking this as confirmed.