MySQL Bugs: #81444: Orphan ibd file when playing with foreign keys

Bug #81444	Orphan ibd file when playing with foreign keys
Submitted:	16 May 2016 22:34	Modified:	18 Apr 2019 8:50
Reporter:	Arnaud Adant	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7, 5.7.12, 5.6.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	composite foreighn keys, foreign keys

Description:
Not sure if this is serious. The below test case shows that you can create an ibd file without an frm file using sql and foreign keys.

ls -al /home/aadant/mysql-5.7.11-linux-glibc2.5-x86_64/data/bug_fk/
total 108
drwxr-x--- 2 aadant dba  4096 May 16 17:14 .
drwxr-xr-x 9 aadant dba  4096 May 16 17:14 ..
-rw-r----- 1 aadant dba 98304 May 16 17:14 b.ibd
-rw-r----- 1 aadant dba    65 May 16 17:14 db.opt

How to repeat:
Run this script with mysql -f 

drop database if exists bug_fk;
create database bug_fk;
use bug_fk;
CREATE TABLE b (
  b int unsigned NOT NULL,
  d1 datetime NOT NULL,
  PRIMARY KEY (b,d1)
) ENGINE=InnoDB;

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;
set foreign_key_checks = 0;
DROP TABLE IF EXISTS b;
CREATE TABLE b (
  b bigint unsigned NOT NULL,
  d1 date NOT NULL,
  PRIMARY KEY (b,d1)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS d;

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;

set foreign_key_checks = 1;
CREATE TABLE b (
  b bigint unsigned NOT NULL,
  d1 date NOT NULL,
  PRIMARY KEY (b,d1)
) ENGINE=InnoDB;
set foreign_key_checks=0;
drop table c;
drop table d;
create table b(id int);

Here is the result on 5.7.11 :

ERROR 1215 (HY000) at line 19: Cannot add foreign key constraint
ERROR 1215 (HY000) at line 35: Cannot add foreign key constraint
ERROR 1813 (HY000) at line 43: Tablespace '`bug_fk`.`b`' exists.

Suggested fix:
Do not persist the ibd file without the frm file. The workaround I found to get rid of the ibd file is to dump / drop the database / restore.

Hello Arnaud,

Thank you for the report and test case.
Observed this with 5.7.12 build.

-- 5.7.12

mysql>  \! ls -l /var/lib/mysql/bug_fk
total 100
-rw-r----- 1 root root 98304 May 19 22:29 b.ibd
-rw-r----- 1 root root    65 May 19 22:29 db.opt

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.12                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.12                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

Regards,
Umesh

-- 5.6.30 - leaves orphan ibd file(some extra info in the error message)

mysql> create table b(id int);
ERROR 1813 (HY000): Tablespace for table '`bug_fk`.`b`' exists. Please DISCARD the tablespace before IMPORT.
mysql>
mysql> \! ls -l 81444/bug_fk
total 132
-rw-rw---- 1 umshastr common 98304 May 17 06:34 b.ibd
-rw-rw---- 1 umshastr common    65 May 17 06:34 db.opt
mysql>
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.30                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.30-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql>

Also 5.5 is effected.

One possible way to fix this pug.

Attachment: fkbug.diff (text/x-patch), 11.36 KiB.

Above patch for 5.5 version.

Posted by developer:
 
Hello!

The problem is still repeatable on 5.7.27-git.
Simplified test case for it looks like:

CREATE DATABASE bug_fk;
USE bug_fk;
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE c (
b INT UNSIGNED NOT NULL,
CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b)
) ENGINE=InnoDB;
CREATE TABLE d (
b BIGINT UNSIGNED NOT NULL,
CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b)
) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE b (
b BIGINT UNSIGNED PRIMARY KEY
) ENGINE=InnoDB;
# The above statement fails with ERROR HY000: Cannot add foreign key constraint
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE c;
DROP TABLE d;
CREATE TABLE b(id INT);
# The above statement incorrectly fails with ERROR HY000: Tablespace '`bug_fk`.`b`' exists.

However, the same test case runs fine on recent 8.0
(I have used 8.0.17-git):

CREATE DATABASE bug_fk;
USE bug_fk;
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE c (
b INT UNSIGNED NOT NULL,
CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b)
) ENGINE=InnoDB;
CREATE TABLE d (
b BIGINT UNSIGNED NOT NULL,
CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b)
) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE b (
b BIGINT UNSIGNED PRIMARY KEY
) ENGINE=InnoDB;
# The above statement fails with ERROR HY000: Cannot add foreign key constraint
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE c;
DROP TABLE d;
# The below statement runs fine!
CREATE TABLE b(id INT);

This happens because starting from 8.0.3 CREATE TABLE for InnoDB tables became
atomic DDL statement. And thus all effects of failed CREATE TABLE b (b BIGINT ...)
statement are properly rolled back.

Therefore I am closing this bug as fixed in 8.0.3.

Since the fact that CREATE TABLE for InnoDB tables is now atomic is emphasized in
Release Notes for 8.0.3 already, I don't think there is anything to document
additionally.