1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
#
# 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.
#
# Also provides coverage for bug#25583288 "DO NOT USE
# HA_INNOBASE::GET_FOREIGN_KEY_CREATE_INFO() FOR SHOW CREATE
# TABLE FK" and bug#30110545 "FK TABLE NAME CASING IS OMITTED
# ON MYSQL 8 LOWER_CASE_TABLE_NAMES=2"
#
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 NOT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`c1`),
KEY `fk1` (`c2`),
CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `Table1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA != 'PERFORMANCE_SCHEMA';
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 NO ACTION
DELETE_RULE NO ACTION
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 NOT NULL AUTO_INCREMENT,
`Product_Category` int NOT NULL,
`Product_Id` int NOT NULL,
`Customer_Id` int 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 DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `product_order_ibfk_2` FOREIGN KEY (`Customer_Id`) REFERENCES `Customer` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE Product;
Table Product
Create Table CREATE TABLE `Product` (
`Category` int NOT NULL,
`Id` int NOT NULL,
`Price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`Category`,`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE Customer;
Table Customer
Create Table CREATE TABLE `Customer` (
`Id` int NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA != 'PERFORMANCE_SCHEMA';
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 NO ACTION
DELETE_RULE NO ACTION
TABLE_NAME Product_Order
REFERENCED_TABLE_NAME Customer
DROP TABLE Product_Order;
DROP TABLE Product;
DROP TABLE Customer;
#
# Bug#29957361: TABLE NOT IN THE SHOW TABLES WITH LOWER_CASE_TABLE_NAMES=2
#
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;
Tables_in_my_db
lowercase_innodb
lowercase_myisam
UPPERCASE_INNODB
UPPERCASE_MYISAM
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';
USE my_db;
# Without the patch, tables with uppercase table names were not displayed.
SHOW TABLES;
Tables_in_my_db
lowercase_innodb
lowercase_myisam
UPPERCASE_INNODB
UPPERCASE_MYISAM
DROP USER 'test_user'@'localhost';
DROP DATABASE my_db;
USE test;
|
