[MDEV-10083] Orphan ibd file when playing with foreign keys Created: 2016-05-19  Updated: 2016-06-23  Resolved: 2016-06-23

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.1.13
Fix Version/s: 5.5.51, 10.0.26, 10.1.15

Type: Bug Priority: Critical
Reporter: Valerii Kravchuk Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: upstream

Sprint: 10.1.15

 Description   

The below test case shows that you can create an ibd file without an frm file using sql and foreign keys:

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);

Check https://bugs.mysql.com/bug.php?id=81444 and https://bugs.launchpad.net/percona-server/+bug/1582501 for more details (note that -f flag is NOT needed in case of mysql client from MariaDB, as it continues to execute statements in case of SQL error by default).

 Comments   
Comment by Jan Lindström (Inactive) [ 2016-06-22 ]

Also 5.5 effected.

Comment by Jan Lindström (Inactive) [ 2016-06-23 ]

For 5.5:

commit ef92aaf9ece92c873ae0f3448ab2274c958ba3fe
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Wed Jun 22 22:37:28 2016 +0300

MDEV-10083: Orphan ibd file when playing with foreign keys

Analysis: row_drop_table_for_mysql did not allow dropping
referenced table even in case when actual creating of the
referenced table was not successfull if foreign_key_checks=1.

Fix: Allow dropping referenced table even if foreign_key_checks=1
if actual table create returned error.

Comment by Jan Lindström (Inactive) [ 2016-06-23 ]

For 10.0:

commit 26de9061e8c4462152b4bcff2b9cbb80cde12de1
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Thu Jun 23 07:42:40 2016 +0300

Merge following commit from 5.5:

commit ef92aaf9ece92c873ae0f3448ab2274c958ba3fe
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Wed Jun 22 22:37:28 2016 +0300

MDEV-10083: Orphan ibd file when playing with foreign keys

Analysis: row_drop_table_for_mysql did not allow dropping
referenced table even in case when actual creating of the
referenced table was not successfull if foreign_key_checks=1.

Fix: Allow dropping referenced table even if foreign_key_checks=1
if actual table create returned error.

Generated at Thu Feb 08 07:39:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.