EVOLUTION-MANAGER

Edit File: innodb-fkcheck.test

--source include/have_innodb.inc

#
# MDEV-10083: Orphan ibd file when playing with foreign keys
#
--disable_query_log
SET @start_global_fpt = @@global.innodb_file_per_table;
SET @start_global_fkc = @@global.foreign_key_checks;
--enable_query_log

set global innodb_file_per_table = 1;

--disable_warnings
drop table if exists b;
drop database if exists bug_fk;
--enable_warnings

let $MYSQLD_DATADIR = `select @@datadir`;

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;

show warnings;

set foreign_key_checks = 0;

DROP TABLE IF EXISTS b;

show create table c;

#
# Note that column b has different type in parent table
#
--error 1005
CREATE TABLE b (
  b bigint unsigned NOT NULL,
  d1 date NOT NULL,
  PRIMARY KEY (b,d1)
) ENGINE=InnoDB;

show warnings;

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;

show warnings;

set foreign_key_checks = 1;

show create table c;
show create table d;

#
# Table c column b used on foreign key has different type
# compared referenced column b in table b, but this
# create still produced b.ibd file. This is because
# we row_drop_table_for_mysql was called and referenced
# table is not allowed to be dropped even in case
# when actual create is not successfull.
#
--error 1005
CREATE TABLE b (
  b bigint unsigned NOT NULL,
  d1 date NOT NULL,
  PRIMARY KEY (b,d1)
) ENGINE=InnoDB;

show warnings;

--list_files $MYSQLD_DATADIR/bug_fk b*

set foreign_key_checks=0;

drop table c;
drop table d;

--list_files $MYSQLD_DATADIR/bug_fk b*

create table b(id int) engine=innodb;
show warnings;

--list_files $MYSQLD_DATADIR/bug_fk b*

#
# Cleanup
#
--disable_query_log
SET @@global.innodb_file_per_table = @start_global_fpt;
SET @@global.foreign_key_checks = @start_global_fkc;
--enable_query_log

--disable_warnings
drop table if exists b;
drop database if exists bug_fk;
--enable_warnings