|
1
by brian
clean slate |
1 |
#
|
2 |
# Test of --lower-case-table-names=2
|
|
3 |
# (User has case insensitive file system and wants to preserve case of
|
|
4 |
# table names)
|
|
5 |
#
|
|
6 |
--source include/have_innodb.inc |
|
7 |
--require r/lowercase2.require |
|
8 |
disable_query_log; |
|
9 |
show variables like "lower_case_table_names"; |
|
10 |
enable_query_log; |
|
11 |
||
12 |
--disable_warnings |
|
13 |
DROP TABLE IF EXISTS t1,t2,t3,t2aA,t1Aa; |
|
14 |
DROP DATABASE IF EXISTS `TEST_$1`; |
|
15 |
DROP DATABASE IF EXISTS `test_$1`; |
|
16 |
DROP DATABASE IF EXISTS mysqltest_LC2; |
|
17 |
--enable_warnings |
|
18 |
||
19 |
CREATE TABLE T1 (a int); |
|
20 |
INSERT INTO T1 VALUES (1); |
|
21 |
SHOW TABLES LIKE "T1"; |
|
22 |
SHOW TABLES LIKE "t1"; |
|
23 |
SHOW CREATE TABLE T1; |
|
24 |
RENAME TABLE T1 TO T2; |
|
25 |
SHOW TABLES LIKE "T2"; |
|
26 |
SELECT * FROM t2; |
|
27 |
RENAME TABLE T2 TO t3; |
|
28 |
SHOW TABLES LIKE "T3"; |
|
29 |
RENAME TABLE T3 TO T1; |
|
30 |
SHOW TABLES LIKE "T1"; |
|
31 |
ALTER TABLE T1 add b int; |
|
32 |
SHOW TABLES LIKE "T1"; |
|
33 |
ALTER TABLE T1 RENAME T2; |
|
34 |
SHOW TABLES LIKE "T2"; |
|
35 |
||
36 |
LOCK TABLE T2 WRITE; |
|
37 |
ALTER TABLE T2 drop b; |
|
38 |
SHOW TABLES LIKE "T2"; |
|
39 |
UNLOCK TABLES; |
|
40 |
RENAME TABLE T2 TO T1; |
|
41 |
SHOW TABLES LIKE "T1"; |
|
42 |
SELECT * from T1; |
|
43 |
DROP TABLE T1; |
|
44 |
||
45 |
#
|
|
46 |
# Test database level
|
|
47 |
#
|
|
48 |
||
49 |
CREATE DATABASE `TEST_$1`; |
|
50 |
SHOW DATABASES LIKE "TEST%"; |
|
51 |
DROP DATABASE `test_$1`; |
|
52 |
||
53 |
#
|
|
54 |
# Test of innodb tables with lower_case_table_names=2
|
|
55 |
#
|
|
56 |
||
57 |
CREATE TABLE T1 (a int) engine=innodb; |
|
58 |
INSERT INTO T1 VALUES (1); |
|
59 |
SHOW TABLES LIKE "T1"; |
|
60 |
SHOW TABLES LIKE "t1"; |
|
61 |
SHOW CREATE TABLE T1; |
|
62 |
RENAME TABLE T1 TO T2; |
|
63 |
SHOW TABLES LIKE "T2"; |
|
64 |
SELECT * FROM t2; |
|
65 |
RENAME TABLE T2 TO t3; |
|
66 |
SHOW TABLES LIKE "T3"; |
|
67 |
RENAME TABLE T3 TO T1; |
|
68 |
SHOW TABLES LIKE "T1"; |
|
69 |
ALTER TABLE T1 add b int; |
|
70 |
SHOW TABLES LIKE "T1"; |
|
71 |
ALTER TABLE T1 RENAME T2; |
|
72 |
SHOW TABLES LIKE "T2"; |
|
73 |
||
74 |
LOCK TABLE T2 WRITE; |
|
75 |
ALTER TABLE T2 drop b; |
|
76 |
SHOW TABLES LIKE "T2"; |
|
77 |
UNLOCK TABLES; |
|
78 |
RENAME TABLE T2 TO T1; |
|
79 |
SHOW TABLES LIKE "T1"; |
|
80 |
SELECT * from T1; |
|
81 |
DROP TABLE T1; |
|
82 |
||
83 |
#
|
|
84 |
# Test problem with temporary tables (Bug #2858)
|
|
85 |
#
|
|
86 |
||
87 |
create table T1 (EVENT_ID int auto_increment primary key, LOCATION char(20)); |
|
88 |
insert into T1 values (NULL,"Mic-4"),(NULL,"Mic-5"),(NULL,"Mic-6"); |
|
89 |
SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; |
|
90 |
SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; |
|
91 |
SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; |
|
92 |
drop table T1; |
|
93 |
||
94 |
#
|
|
95 |
# Test name conversion with ALTER TABLE / CREATE INDEX (Bug #3109)
|
|
96 |
#
|
|
97 |
||
98 |
create table T1 (A int); |
|
99 |
alter table T1 add index (A); |
|
100 |
show tables like 'T1%'; |
|
101 |
alter table t1 add index (A); |
|
102 |
show tables like 't1%'; |
|
103 |
drop table t1; |
|
104 |
||
105 |
#
|
|
106 |
# Bug #7261: Alter table loses temp table
|
|
107 |
#
|
|
108 |
||
109 |
create temporary table T1(a int(11), b varchar(8)); |
|
110 |
insert into T1 values (1, 'abc'); |
|
111 |
select * from T1; |
|
112 |
alter table T1 add index (a); |
|
113 |
select * from T1; |
|
114 |
drop table T1; |
|
115 |
||
116 |
#
|
|
117 |
# Bug #8355: Tables not dropped from table cache on drop db
|
|
118 |
#
|
|
119 |
create database mysqltest_LC2; |
|
120 |
use mysqltest_LC2; |
|
121 |
create table myUC (i int); |
|
122 |
insert into myUC values (1),(2),(3); |
|
123 |
select * from myUC; |
|
124 |
use test; |
|
125 |
drop database mysqltest_LC2; |
|
126 |
create database mysqltest_LC2; |
|
127 |
use mysqltest_LC2; |
|
128 |
create table myUC (i int); |
|
129 |
select * from myUC; |
|
130 |
use test; |
|
131 |
drop database mysqltest_LC2; |
|
132 |
||
133 |
#
|
|
134 |
# Bug #9500: Problem with WHERE clause
|
|
135 |
#
|
|
136 |
create table t2aA (col1 int); |
|
137 |
create table t1Aa (col1 int); |
|
138 |
select t1Aa.col1 from t1aA,t2Aa where t1Aa.col1 = t2aA.col1; |
|
139 |
drop table t2aA, t1Aa; |
|
140 |
||
141 |
# End of 4.1 tests
|
|
142 |
||
143 |
#
|
|
144 |
# Bug#17661 information_schema.SCHEMATA returns uppercase with lower_case_table_names = 1
|
|
145 |
#
|
|
146 |
create database mysqltest_LC2; |
|
147 |
use mysqltest_LC2; |
|
148 |
create table myUC (i int); |
|
149 |
select TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES |
|
150 |
where TABLE_SCHEMA ='mysqltest_LC2'; |
|
151 |
use test; |
|
152 |
drop database mysqltest_LC2; |