147 lines
4.4 KiB
Text
147 lines
4.4 KiB
Text
--source include/big_test.inc
|
|
--source include/have_case_insensitive_file_system.inc
|
|
--source include/have_lowercase2.inc
|
|
|
|
--echo #
|
|
--echo # Bug#46941 crash with lower_case_table_names=2 and
|
|
--echo # foreign data dictionary confusion
|
|
--echo #
|
|
|
|
CREATE DATABASE XY;
|
|
USE XY;
|
|
|
|
#
|
|
# Logs are disabled, since the number of creates tables
|
|
# and subsequent select statements may vary between
|
|
# versions
|
|
#
|
|
--disable_query_log
|
|
--disable_result_log
|
|
|
|
let $tcs = `SELECT @@table_open_cache + 1`;
|
|
|
|
let $i = $tcs;
|
|
|
|
while ($i)
|
|
{
|
|
eval CREATE TABLE XY.T_$i (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT,
|
|
primary key(a, b), unique(a)) ENGINE=InnoDB;
|
|
dec $i;
|
|
}
|
|
|
|
eval ALTER TABLE XY.T_$tcs ADD INDEX I1 (c, b),
|
|
ADD CONSTRAINT C1 FOREIGN KEY (c, b) REFERENCES XY.T_1 (a, b);
|
|
|
|
eval ALTER TABLE XY.T_$tcs ADD INDEX I2 (b),
|
|
ADD CONSTRAINT C2 FOREIGN KEY (b) REFERENCES XY.T_1(a);
|
|
|
|
let $i = $tcs;
|
|
while ($i)
|
|
{
|
|
eval SELECT * FROM XY.T_$i LIMIT 1;
|
|
dec $i;
|
|
}
|
|
|
|
DROP DATABASE XY;
|
|
CREATE DATABASE XY;
|
|
USE XY;
|
|
eval CREATE TABLE XY.T_$tcs (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT,
|
|
PRIMARY KEY(a, b), UNIQUE(b)) ENGINE=InnoDB;
|
|
#
|
|
# The bug causes this SELECT to err
|
|
eval SELECT * FROM XY.T_$tcs LIMIT 1;
|
|
|
|
--enable_query_log
|
|
--enable_result_log
|
|
DROP DATABASE XY;
|
|
USE TEST;
|
|
|
|
--echo #
|
|
--echo # Bug55222 Mysqldump table names case bug in REFERENCES clause
|
|
--echo # InnoDB did not handle lower_case_table_names=2 for
|
|
--echo # foreign_table_names and referenced_table_names.
|
|
--echo #
|
|
--echo # Also provides coverage for bug#25583288 "DO NOT USE
|
|
--echo # HA_INNOBASE::GET_FOREIGN_KEY_CREATE_INFO() FOR SHOW CREATE
|
|
--echo # TABLE FK" and bug#30110545 "FK TABLE NAME CASING IS OMITTED
|
|
--echo # ON MYSQL 8 LOWER_CASE_TABLE_NAMES=2"
|
|
--echo #
|
|
|
|
SHOW VARIABLES LIKE 'lower_case_table_names';
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS `Table2`;
|
|
DROP TABLE IF EXISTS `Table1`;
|
|
--disable_warnings
|
|
|
|
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);
|
|
query_vertical SHOW CREATE TABLE `Table2`;
|
|
query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
|
|
WHERE CONSTRAINT_SCHEMA != 'PERFORMANCE_SCHEMA';
|
|
DROP TABLE `Table2`;
|
|
DROP TABLE `Table1`;
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS Product_Order;
|
|
DROP TABLE IF EXISTS Product;
|
|
DROP TABLE IF EXISTS Customer;
|
|
--enable_warnings
|
|
|
|
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;
|
|
|
|
query_vertical SHOW CREATE TABLE Product_Order;
|
|
query_vertical SHOW CREATE TABLE Product;
|
|
query_vertical SHOW CREATE TABLE Customer;
|
|
query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
|
|
WHERE CONSTRAINT_SCHEMA != 'PERFORMANCE_SCHEMA';
|
|
DROP TABLE Product_Order;
|
|
DROP TABLE Product;
|
|
DROP TABLE Customer;
|
|
|
|
--echo #
|
|
--echo # Bug#29957361: TABLE NOT IN THE SHOW TABLES WITH LOWER_CASE_TABLE_NAMES=2
|
|
--echo #
|
|
|
|
CREATE DATABASE my_db;
|
|
USE my_db;
|
|
CREATE TABLE UPPERCASE_MYISAM (a INT) ENGINE=MYISAM;
|
|
CREATE TABLE lowercase_myisam (a INT) ENGINE=MYISAM;
|
|
CREATE TABLE UPPERCASE_INNODB (a INT) ENGINE=InnoDB;
|
|
CREATE TABLE lowercase_innodb (a INT) ENGINE=InnoDB;
|
|
SHOW TABLES;
|
|
|
|
# Create a new user and assign SELECT privileges on the above tables.
|
|
CREATE USER 'test_user'@'localhost';
|
|
GRANT SELECT ON my_db.UPPERCASE_MYISAM TO 'test_user'@'localhost';
|
|
GRANT SELECT ON my_db.lowercase_myisam TO 'test_user'@'localhost';
|
|
GRANT SELECT ON my_db.UPPERCASE_INNODB TO 'test_user'@'localhost';
|
|
GRANT SELECT ON my_db.lowercase_innodb TO 'test_user'@'localhost';
|
|
|
|
# Connect using the new user
|
|
--connect(con1, localhost, test_user,,)
|
|
|
|
USE my_db;
|
|
--echo # Without the patch, tables with uppercase table names were not displayed.
|
|
SHOW TABLES;
|
|
|
|
--connection default
|
|
--disconnect con1
|
|
DROP USER 'test_user'@'localhost';
|
|
DROP DATABASE my_db;
|
|
USE test;
|