Details
-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 10.1.13
-
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
-
Labels:
-
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).