1--source include/have_case_insensitive_file_system.inc
2--source include/have_innodb.inc
3# This test is slow on buildbot.
4--source include/big_test.inc
5
6--echo #
7--echo # Bug#46941 crash with lower_case_table_names=2 and
8--echo #           foreign data dictionary confusion
9--echo #
10
11CREATE DATABASE XY;
12USE XY;
13set @old_table_open_cache=@@table_open_cache;
14set global table_open_cache = 512;
15
16#
17# Logs are disabled, since the number of creates tables
18# and subsequent select statements may vary between
19# versions
20#
21--disable_query_log
22--disable_result_log
23
24let $tcs = `SELECT @@table_open_cache + 1`;
25
26let $i = $tcs;
27
28while ($i)
29{
30  eval CREATE TABLE XY.T_$i (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT,
31                             primary key(a, b), unique(b)) ENGINE=InnoDB;
32  dec $i;
33}
34
35eval ALTER TABLE XY.T_$tcs ADD INDEX I1 (c, b),
36                           ADD CONSTRAINT C1 FOREIGN KEY (c, b) REFERENCES XY.T_1 (a, b);
37
38eval ALTER TABLE XY.T_$tcs ADD INDEX I2 (b),
39                           ADD CONSTRAINT C2 FOREIGN KEY (b) REFERENCES XY.T_1(a);
40
41let $i = $tcs;
42while ($i)
43{
44  eval SELECT * FROM XY.T_$i LIMIT 1;
45  dec $i;
46}
47
48DROP DATABASE XY;
49CREATE DATABASE XY;
50USE XY;
51eval CREATE TABLE XY.T_$tcs (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT,
52                             PRIMARY KEY(a, b), UNIQUE(b)) ENGINE=InnoDB;
53#
54# The bug causes this SELECT to err
55eval SELECT * FROM XY.T_$tcs LIMIT 1;
56
57--enable_query_log
58--enable_result_log
59set global table_open_cache = @old_table_open_cache;
60DROP DATABASE XY;
61USE TEST;
62
63--echo #
64--echo # Bug55222 Mysqldump table names case bug in REFERENCES clause
65--echo # InnoDB did not handle lower_case_table_names=2 for
66--echo # foreign_table_names and referenced_table_names.
67--echo #
68
69SHOW VARIABLES LIKE 'lower_case_table_names';
70
71--disable_warnings
72DROP TABLE IF EXISTS `Table2`;
73DROP TABLE IF EXISTS `Table1`;
74--disable_warnings
75
76CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB;
77CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB;
78ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1);
79query_vertical SHOW CREATE TABLE `Table2`;
80query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test';
81DROP TABLE `Table2`;
82DROP TABLE `Table1`;
83
84--disable_warnings
85DROP TABLE IF EXISTS Product_Order;
86DROP TABLE IF EXISTS Product;
87DROP TABLE IF EXISTS Customer;
88--enable_warnings
89
90CREATE TABLE Product (Category INT NOT NULL, Id INT NOT NULL,
91	Price DECIMAL, PRIMARY KEY(Category, Id)) ENGINE=InnoDB;
92CREATE TABLE Customer (Id INT NOT NULL, PRIMARY KEY (Id)) ENGINE=InnoDB;
93CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT,
94	Product_Category INT NOT NULL,
95	Product_Id INT NOT NULL,
96	Customer_Id INT NOT NULL,
97	PRIMARY KEY(No),
98	INDEX (Product_Category, Product_Id),
99	FOREIGN KEY (Product_Category, Product_Id)
100		REFERENCES Product(Category, Id) ON UPDATE CASCADE ON DELETE RESTRICT,
101	INDEX (Customer_Id),
102	FOREIGN KEY (Customer_Id)
103		REFERENCES Customer(Id)
104	) ENGINE=INNODB;
105
106query_vertical SHOW CREATE TABLE Product_Order;
107query_vertical SHOW CREATE TABLE Product;
108query_vertical SHOW CREATE TABLE Customer;
109query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test';
110DROP TABLE Product_Order;
111DROP TABLE Product;
112DROP TABLE Customer;
113
114