Debian Codegraph @

mysql-test/suite/innodb/t/innodb-fkcheck.test

raw
1 --source include/have_innodb.inc
2 --source include/default_charset.inc
3
4 #
5 # MDEV-10083: Orphan ibd file when playing with foreign keys
6 #
7
8 let $MYSQLD_DATADIR = `select @@datadir`;
9
10 create database bug_fk;
11 use bug_fk;
12
13 CREATE TABLE b (
14 b int unsigned NOT NULL,
15 d1 datetime NOT NULL,
16 PRIMARY KEY (b,d1)
17 ) ENGINE=InnoDB;
18
19 CREATE TABLE c (
20 b int unsigned NOT NULL,
21 d1 datetime NOT NULL,
22 d2 datetime NOT NULL,
23 PRIMARY KEY (b,d1),
24 CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b)
25 ) ENGINE=InnoDB;
26
27 show warnings;
28
29 set foreign_key_checks = 0;
30
31 DROP TABLE IF EXISTS b;
32
33 show create table c;
34
35 #
36 # Note that column b has different type in parent table
37 #
38 CREATE TABLE b (
39 b bigint unsigned NOT NULL,
40 d1 date NOT NULL,
41 PRIMARY KEY (b,d1)
42 ) ENGINE=InnoDB;
43 DROP TABLE b;
44
45 set foreign_key_checks = 1;
46 --error ER_CANT_CREATE_TABLE
47 CREATE TABLE b (
48 b bigint unsigned NOT NULL,
49 d1 date NOT NULL,
50 PRIMARY KEY (b,d1)
51 ) ENGINE=InnoDB;
52
53 show warnings;
54 set foreign_key_checks = 0;
55
56 DROP TABLE IF EXISTS d;
57
58 CREATE TABLE d (
59 b bigint unsigned NOT NULL,
60 d1 date NOT NULL,
61 PRIMARY KEY (b,d1),
62 CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b)
63 ) ENGINE=InnoDB;
64
65 show warnings;
66
67 set foreign_key_checks = 1;
68
69 show create table c;
70 show create table d;
71
72 #
73 # Table c column b used on foreign key has different type
74 # compared referenced column b in table b, but this
75 # create still produced b.ibd file. This is because
76 # we row_drop_table_for_mysql was called and referenced
77 # table is not allowed to be dropped even in case
78 # when actual create is not successfull.
79 #
80 --error 1005
81 CREATE TABLE b (
82 b bigint unsigned NOT NULL,
83 d1 date NOT NULL,
84 PRIMARY KEY (b,d1)
85 ) ENGINE=InnoDB;
86
87 show warnings;
88
89 --list_files $MYSQLD_DATADIR/bug_fk b*
90
91 set foreign_key_checks=0;
92
93 drop table c;
94 drop table d;
95
96 --list_files $MYSQLD_DATADIR/bug_fk b*
97
98 create table b(id int) engine=innodb;
99 show warnings;
100
101 --list_files $MYSQLD_DATADIR/bug_fk b*
102
103 #
104 # Cleanup
105 #
106
107 drop table b;
108 drop database bug_fk;