Public bug reported:
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.
** Affects: mysql-server
Importance: Unknown
Status: Unknown
** Affects: percona-server
Importance: Undecided
Status: New
** Tags: upstream
** Tags added: upstream
** Bug watch added: MySQL Bug System #81444
http://bugs.mysql.com/bug.php?id=81444
** Also affects: mysql-server via
http://bugs.mysql.com/bug.php?id=81444
Importance: Unknown
Status: Unknown
--
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1582501
Title:
Orphan .ibd files when playing with foreign keys
To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1582501/+subscriptions
--
Mailing list: https://launchpad.net/~enterprise-support
Post to : enterprise-support@lists.launchpad.net
Unsubscribe : https://launchpad.net/~enterprise-support
More help : https://help.launchpad.net/ListHelp
