1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
--source include/have_innodb.inc
--source include/default_charset.inc
#
# MDEV-10083: Orphan ibd file when playing with foreign keys
#
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
#
CREATE TABLE b (
b bigint unsigned NOT NULL,
d1 date NOT NULL,
PRIMARY KEY (b,d1)
) ENGINE=InnoDB;
DROP TABLE b;
set foreign_key_checks = 1;
--error ER_CANT_CREATE_TABLE
CREATE TABLE b (
b bigint unsigned NOT NULL,
d1 date NOT NULL,
PRIMARY KEY (b,d1)
) ENGINE=InnoDB;
show warnings;
set foreign_key_checks = 0;
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
#
drop table b;
drop database bug_fk;
|
