enterprise-support team mailing list archive

[Valerii Kravchuk <valerii.kravchuk@xxxxxxxxxxx>]

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