summaryrefslogtreecommitdiff
path: root/mysql-test/main/lowercase_table4.test
blob: 981c8a14564f69e877f485a6a182c142631db702 (plain)
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
--source include/have_case_insensitive_file_system.inc
--source include/have_innodb.inc

--echo #
--echo # Bug#46941 crash with lower_case_table_names=2 and
--echo #           foreign data dictionary confusion
--echo #

CREATE DATABASE XY;
USE XY;
set @old_table_open_cache=@@table_open_cache;
set global table_open_cache = 512;

#
# 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(b)) 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
set global table_open_cache = @old_table_open_cache;
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 #

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='test';
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='test';
DROP TABLE Product_Order;
DROP TABLE Product;
DROP TABLE Customer;