Edit File: lowercase_table4.result
# # Bug#46941 crash with lower_case_table_names=2 and # foreign data dictionary confusion # CREATE DATABASE XY; USE XY; DROP DATABASE XY; USE TEST; # # Bug55222 Mysqldump table names case bug in REFERENCES clause # InnoDB did not handle lower_case_table_names=2 for # foreign_table_names and referenced_table_names. # SHOW VARIABLES LIKE 'lower_case_table_names'; Variable_name Value lower_case_table_names 2 DROP TABLE IF EXISTS `Table2`; DROP TABLE IF EXISTS `Table1`; CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB; ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1); SHOW CREATE TABLE `Table2`; Table Table2 Create Table CREATE TABLE `Table2` ( `c1` int(11) NOT NULL, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `fk1` (`c2`), CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `Table1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS; CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME fk1 UNIQUE_CONSTRAINT_CATALOG def UNIQUE_CONSTRAINT_SCHEMA test UNIQUE_CONSTRAINT_NAME PRIMARY MATCH_OPTION NONE UPDATE_RULE RESTRICT DELETE_RULE RESTRICT TABLE_NAME Table2 REFERENCED_TABLE_NAME Table1 DROP TABLE `Table2`; DROP TABLE `Table1`; DROP TABLE IF EXISTS Product_Order; DROP TABLE IF EXISTS Product; DROP TABLE IF EXISTS Customer; CREATE TABLE Product (Category INT NOT NULL, Id INT NOT NULL, Price DECIMAL, PRIMARY KEY(Category, Id)) ENGINE=InnoDB; CREATE TABLE Customer (Id INT NOT NULL, PRIMARY KEY (Id)) ENGINE=InnoDB; CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT, Product_Category INT NOT NULL, Product_Id INT NOT NULL, Customer_Id INT NOT NULL, PRIMARY KEY(No), INDEX (Product_Category, Product_Id), FOREIGN KEY (Product_Category, Product_Id) REFERENCES Product(Category, Id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (Customer_Id), FOREIGN KEY (Customer_Id) REFERENCES Customer(Id) ) ENGINE=INNODB; SHOW CREATE TABLE Product_Order; Table Product_Order Create Table CREATE TABLE `Product_Order` ( `No` int(11) NOT NULL AUTO_INCREMENT, `Product_Category` int(11) NOT NULL, `Product_Id` int(11) NOT NULL, `Customer_Id` int(11) NOT NULL, PRIMARY KEY (`No`), KEY `Product_Category` (`Product_Category`,`Product_Id`), KEY `Customer_Id` (`Customer_Id`), CONSTRAINT `product_order_ibfk_1` FOREIGN KEY (`Product_Category`, `Product_Id`) REFERENCES `Product` (`Category`, `Id`) ON UPDATE CASCADE, CONSTRAINT `product_order_ibfk_2` FOREIGN KEY (`Customer_Id`) REFERENCES `Customer` (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE Product; Table Product Create Table CREATE TABLE `Product` ( `Category` int(11) NOT NULL, `Id` int(11) NOT NULL, `Price` decimal(10,0) DEFAULT NULL, PRIMARY KEY (`Category`,`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE Customer; Table Customer Create Table CREATE TABLE `Customer` ( `Id` int(11) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS; CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME product_order_ibfk_1 UNIQUE_CONSTRAINT_CATALOG def UNIQUE_CONSTRAINT_SCHEMA test UNIQUE_CONSTRAINT_NAME PRIMARY MATCH_OPTION NONE UPDATE_RULE CASCADE DELETE_RULE RESTRICT TABLE_NAME Product_Order REFERENCED_TABLE_NAME Product CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME product_order_ibfk_2 UNIQUE_CONSTRAINT_CATALOG def UNIQUE_CONSTRAINT_SCHEMA test UNIQUE_CONSTRAINT_NAME PRIMARY MATCH_OPTION NONE UPDATE_RULE RESTRICT DELETE_RULE RESTRICT TABLE_NAME Product_Order REFERENCED_TABLE_NAME Customer DROP TABLE Product_Order; DROP TABLE Product; DROP TABLE Customer;
Back to File Manager