1# 2# Bug#46941 crash with lower_case_table_names=2 and 3# foreign data dictionary confusion 4# 5CREATE DATABASE XY; 6USE XY; 7set @old_table_open_cache=@@table_open_cache; 8set global table_open_cache = 512; 9set global table_open_cache = @old_table_open_cache; 10DROP DATABASE XY; 11USE TEST; 12# 13# Bug55222 Mysqldump table names case bug in REFERENCES clause 14# InnoDB did not handle lower_case_table_names=2 for 15# foreign_table_names and referenced_table_names. 16# 17SHOW VARIABLES LIKE 'lower_case_table_names'; 18Variable_name Value 19lower_case_table_names 2 20DROP TABLE IF EXISTS `Table2`; 21DROP TABLE IF EXISTS `Table1`; 22CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB; 23CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB; 24ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1); 25SHOW CREATE TABLE `Table2`; 26Table Table2 27Create Table CREATE TABLE `Table2` ( 28 `c1` int(11) NOT NULL, 29 `c2` int(11) DEFAULT NULL, 30 PRIMARY KEY (`c1`), 31 KEY `fk1` (`c2`), 32 CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `Table1` (`c1`) 33) ENGINE=InnoDB DEFAULT CHARSET=latin1 34SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test'; 35CONSTRAINT_CATALOG def 36CONSTRAINT_SCHEMA test 37CONSTRAINT_NAME fk1 38UNIQUE_CONSTRAINT_CATALOG def 39UNIQUE_CONSTRAINT_SCHEMA test 40UNIQUE_CONSTRAINT_NAME PRIMARY 41MATCH_OPTION NONE 42UPDATE_RULE RESTRICT 43DELETE_RULE RESTRICT 44TABLE_NAME Table2 45REFERENCED_TABLE_NAME Table1 46DROP TABLE `Table2`; 47DROP TABLE `Table1`; 48DROP TABLE IF EXISTS Product_Order; 49DROP TABLE IF EXISTS Product; 50DROP TABLE IF EXISTS Customer; 51CREATE TABLE Product (Category INT NOT NULL, Id INT NOT NULL, 52Price DECIMAL, PRIMARY KEY(Category, Id)) ENGINE=InnoDB; 53CREATE TABLE Customer (Id INT NOT NULL, PRIMARY KEY (Id)) ENGINE=InnoDB; 54CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT, 55Product_Category INT NOT NULL, 56Product_Id INT NOT NULL, 57Customer_Id INT NOT NULL, 58PRIMARY KEY(No), 59INDEX (Product_Category, Product_Id), 60FOREIGN KEY (Product_Category, Product_Id) 61REFERENCES Product(Category, Id) ON UPDATE CASCADE ON DELETE RESTRICT, 62INDEX (Customer_Id), 63FOREIGN KEY (Customer_Id) 64REFERENCES Customer(Id) 65) ENGINE=INNODB; 66SHOW CREATE TABLE Product_Order; 67Table Product_Order 68Create Table CREATE TABLE `Product_Order` ( 69 `No` int(11) NOT NULL AUTO_INCREMENT, 70 `Product_Category` int(11) NOT NULL, 71 `Product_Id` int(11) NOT NULL, 72 `Customer_Id` int(11) NOT NULL, 73 PRIMARY KEY (`No`), 74 KEY `Product_Category` (`Product_Category`,`Product_Id`), 75 KEY `Customer_Id` (`Customer_Id`), 76 CONSTRAINT `product_order_ibfk_1` FOREIGN KEY (`Product_Category`, `Product_Id`) REFERENCES `Product` (`Category`, `Id`) ON UPDATE CASCADE, 77 CONSTRAINT `product_order_ibfk_2` FOREIGN KEY (`Customer_Id`) REFERENCES `Customer` (`Id`) 78) ENGINE=InnoDB DEFAULT CHARSET=latin1 79SHOW CREATE TABLE Product; 80Table Product 81Create Table CREATE TABLE `Product` ( 82 `Category` int(11) NOT NULL, 83 `Id` int(11) NOT NULL, 84 `Price` decimal(10,0) DEFAULT NULL, 85 PRIMARY KEY (`Category`,`Id`) 86) ENGINE=InnoDB DEFAULT CHARSET=latin1 87SHOW CREATE TABLE Customer; 88Table Customer 89Create Table CREATE TABLE `Customer` ( 90 `Id` int(11) NOT NULL, 91 PRIMARY KEY (`Id`) 92) ENGINE=InnoDB DEFAULT CHARSET=latin1 93SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test'; 94CONSTRAINT_CATALOG def 95CONSTRAINT_SCHEMA test 96CONSTRAINT_NAME product_order_ibfk_1 97UNIQUE_CONSTRAINT_CATALOG def 98UNIQUE_CONSTRAINT_SCHEMA test 99UNIQUE_CONSTRAINT_NAME PRIMARY 100MATCH_OPTION NONE 101UPDATE_RULE CASCADE 102DELETE_RULE RESTRICT 103TABLE_NAME Product_Order 104REFERENCED_TABLE_NAME Product 105CONSTRAINT_CATALOG def 106CONSTRAINT_SCHEMA test 107CONSTRAINT_NAME product_order_ibfk_2 108UNIQUE_CONSTRAINT_CATALOG def 109UNIQUE_CONSTRAINT_SCHEMA test 110UNIQUE_CONSTRAINT_NAME PRIMARY 111MATCH_OPTION NONE 112UPDATE_RULE RESTRICT 113DELETE_RULE RESTRICT 114TABLE_NAME Product_Order 115REFERENCED_TABLE_NAME Customer 116DROP TABLE Product_Order; 117DROP TABLE Product; 118DROP TABLE Customer; 119