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