1
--source include/have_case_insensitive_file_system.inc
2
--source include/have_innodb.inc
5
--echo # Bug#46941 crash with lower_case_table_names=2 and
6
--echo # foreign data dictionary confusion
13
# Logs are disabled, since the number of creates tables
14
# and subsequent select statements may vary between
20
let $tcs = `SELECT @@table_open_cache + 1`;
26
eval CREATE TABLE XY.T_$i (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT,
27
primary key(a, b), unique(b)) ENGINE=InnoDB;
31
eval ALTER TABLE XY.T_$tcs ADD INDEX I1 (c, b),
32
ADD CONSTRAINT C1 FOREIGN KEY (c, b) REFERENCES XY.T_1 (a, b);
34
eval ALTER TABLE XY.T_$tcs ADD INDEX I2 (b),
35
ADD CONSTRAINT C2 FOREIGN KEY (b) REFERENCES XY.T_1(a);
40
eval SELECT * FROM XY.T_$i LIMIT 1;
47
eval CREATE TABLE XY.T_$tcs (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT,
48
PRIMARY KEY(a, b), UNIQUE(b)) ENGINE=InnoDB;
50
# The bug causes this SELECT to err
51
eval SELECT * FROM XY.T_$tcs LIMIT 1;
59
--echo # Bug55222 Mysqldump table names case bug in REFERENCES clause
60
--echo # InnoDB did not handle lower_case_table_names=2 for
61
--echo # foreign_table_names and referenced_table_names.
64
SHOW VARIABLES LIKE 'lower_case_table_names';
67
DROP TABLE IF EXISTS `Table2`;
68
DROP TABLE IF EXISTS `Table1`;
71
CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB;
72
CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB;
73
ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1);
74
query_vertical SHOW CREATE TABLE `Table2`;
75
query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
80
DROP TABLE IF EXISTS Product_Order;
81
DROP TABLE IF EXISTS Product;
82
DROP TABLE IF EXISTS Customer;
85
CREATE TABLE Product (Category INT NOT NULL, Id INT NOT NULL,
86
Price DECIMAL, PRIMARY KEY(Category, Id)) ENGINE=InnoDB;
87
CREATE TABLE Customer (Id INT NOT NULL, PRIMARY KEY (Id)) ENGINE=InnoDB;
88
CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT,
89
Product_Category INT NOT NULL,
90
Product_Id INT NOT NULL,
91
Customer_Id INT NOT NULL,
93
INDEX (Product_Category, Product_Id),
94
FOREIGN KEY (Product_Category, Product_Id)
95
REFERENCES Product(Category, Id) ON UPDATE CASCADE ON DELETE RESTRICT,
97
FOREIGN KEY (Customer_Id)
98
REFERENCES Customer(Id)
101
query_vertical SHOW CREATE TABLE Product_Order;
102
query_vertical SHOW CREATE TABLE Product;
103
query_vertical SHOW CREATE TABLE Customer;
104
query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
105
DROP TABLE Product_Order;