diff options
Diffstat (limited to 'mysql-test/main/lowercase_table2.test')
| -rw-r--r-- | mysql-test/main/lowercase_table2.test | 330 |
1 files changed, 330 insertions, 0 deletions
diff --git a/mysql-test/main/lowercase_table2.test b/mysql-test/main/lowercase_table2.test new file mode 100644 index 00000000..5d595e96 --- /dev/null +++ b/mysql-test/main/lowercase_table2.test @@ -0,0 +1,330 @@ +# +# Test of --lower-case-table-names=2 +# (User has case insensitive file system and wants to preserve case of +# table names) +# +--source include/have_innodb.inc +--require include/lowercase2.require +disable_query_log; +show variables like "lower_case_table_names"; +enable_query_log; + +--disable_warnings +DROP TABLE IF EXISTS t1,t2,t3,t2aA,t1Aa; +DROP DATABASE IF EXISTS `TEST_$1`; +DROP DATABASE IF EXISTS `test_$1`; +DROP DATABASE IF EXISTS mysqltest_LC2; +--enable_warnings + +CREATE TABLE T1 (a int) ENGINE=MyISAM; +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +SHOW TABLES LIKE "t1"; +SHOW CREATE TABLE T1; +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +SELECT * FROM t2; +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; + +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +SELECT * from T1; +DROP TABLE T1; + +# +# Test database level +# + +CREATE DATABASE `TEST_$1`; +SHOW DATABASES LIKE "TEST%"; +DROP DATABASE `test_$1`; + +# +# Test of innodb tables with lower_case_table_names=2 +# + +CREATE TABLE T1 (a int) engine=innodb; +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +SHOW TABLES LIKE "t1"; +SHOW CREATE TABLE T1; +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +SELECT * FROM t2; +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; + +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +SELECT * from T1; +DROP TABLE T1; + +create database mysqltest_UPPERCASE; +CREATE TABLE mysqltest_UPPERCASE.T1 (a int) engine=innodb; +CREATE TABLE mysqltest_uppercase.t2 (a int) engine=innodb; +CREATE TABLE mysqltest_UPPERCASE.t3 (a int) engine=innodb; +drop table mysqltest_UPPERCASE.T1; +drop table mysqltest_UPPERCASE.T2; +drop table mysqltest_uppercase.t3; +drop database mysqltest_UPPERCASE; + +# +# Test problem with temporary tables (Bug #2858) +# + +create table T1 (EVENT_ID int auto_increment primary key, LOCATION char(20)); +insert into T1 values (NULL,"Mic-4"),(NULL,"Mic-5"),(NULL,"Mic-6"); +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +SELECT LOCATION FROM T1 WHERE EVENT_ID=2 UNION ALL SELECT LOCATION FROM T1 WHERE EVENT_ID=3; +drop table T1; + +# +# Test name conversion with ALTER TABLE / CREATE INDEX (Bug #3109) +# + +create table T1 (A int); +alter table T1 add index (A); +show tables like 'T1%'; +alter table t1 add index (A); +show tables like 't1%'; +drop table t1; + +# +# Bug #7261: Alter table loses temp table +# + +create temporary table T1(a int(11), b varchar(8)); +insert into T1 values (1, 'abc'); +select * from T1; +alter table T1 add index (a); +select * from T1; +drop table T1; + +# +# Bug #8355: Tables not dropped from table cache on drop db +# +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +show tables; +insert into myUC values (1),(2),(3); +select * from myUC; +use test; +drop database mysqltest_LC2; +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +select * from myUC; +use test; +drop database mysqltest_LC2; + +# +# Bug #9500: Problem with WHERE clause +# +create table t2aA (col1 int); +create table t1Aa (col1 int); +select t1Aa.col1 from t1aA,t2Aa where t1Aa.col1 = t2aA.col1; +drop table t2aA, t1Aa; + +# End of 4.1 tests + +# +# Bug#17661 information_schema.SCHEMATA returns uppercase with lower_case_table_names = 1 +# +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +select TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES +where TABLE_SCHEMA ='mysqltest_LC2'; +use test; +drop database mysqltest_LC2; + + +--echo # +--echo # Bug #11758687: 50924: object names not resolved correctly +--echo # on lctn2 systems +--echo # + +CREATE DATABASE BUP_XPFM_COMPAT_DB2; + +CREATE TABLE BUP_XPFM_COMPAT_DB2.TABLE2 (c13 INT) DEFAULT CHARSET latin1; +CREATE TABLE BUP_XPFM_COMPAT_DB2.table1 (c13 INT) DEFAULT CHARSET latin1; +CREATE TABLE bup_xpfm_compat_db2.table3 (c13 INT) DEFAULT CHARSET latin1; + +delimiter |; +# +CREATE TRIGGER BUP_XPFM_COMPAT_DB2.trigger1 AFTER INSERT + ON BUP_XPFM_COMPAT_DB2.table1 FOR EACH ROW + update BUP_XPFM_COMPAT_DB2.table1 set c13=12; +| +CREATE TRIGGER BUP_XPFM_COMPAT_DB2.TRIGGER2 AFTER INSERT + ON BUP_XPFM_COMPAT_DB2.TABLE2 FOR EACH ROW + update BUP_XPFM_COMPAT_DB2.table1 set c13=12; +| +CREATE TRIGGER BUP_XPFM_COMPAT_DB2.TrigGer3 AFTER INSERT + ON BUP_XPFM_COMPAT_DB2.TaBle3 FOR EACH ROW + update BUP_XPFM_COMPAT_DB2.table1 set c13=12; +| +delimiter ;| + +SELECT trigger_schema, trigger_name, event_object_table FROM +INFORMATION_SCHEMA.TRIGGERS + WHERE trigger_schema COLLATE utf8_bin = 'BUP_XPFM_COMPAT_DB2' + ORDER BY trigger_schema, trigger_name; + +DROP DATABASE BUP_XPFM_COMPAT_DB2; + +--echo # End of 5.1 tests + + +--echo # +--echo # Test for bug #44738 "fill_schema_table_from_frm() opens tables without +--echo # lowercasing table name". Due to not properly normalizing table names +--echo # in lower_case_table_names modes in this function queries to I_S which +--echo # were executed through it left entries with incorrect key in table +--echo # definition cache. As result further queries to I_S that used this +--echo # function produced stale results in cases when table definition was +--echo # changed by a DDL statement. Also combination of this issue and a +--echo # similar problem in CREATE TABLE (it also has peeked into table +--echo # definition cache using non-normalized key) led to spurious +--echo # ER_TABLE_EXISTS_ERROR errors when one tried to create table with the +--echo # same name as a previously existing but dropped table. +--echo # +--disable_warnings +drop database if exists mysqltest_UPPERCASE; +drop table if exists t_bug44738_UPPERCASE; +--enable_warnings +create database mysqltest_UPPERCASE; +use mysqltest_UPPERCASE; +select database(); +create table t_bug44738_UPPERCASE (i int) comment='Old comment'; +create table t_bug44738_lowercase (i int) comment='Old comment'; +create table t_bug44738_UPPERCASE2 (i int) comment='Old comment'; +create table t_bug44738_lowercase2 (i int) comment='Old comment'; +select table_schema, table_name, table_comment from information_schema.tables + where table_schema like 'mysqltest_%' and table_name like 't_bug44738_%' + order by table_name; +alter table t_bug44738_UPPERCASE comment='New comment'; +alter table t_bug44738_lowercase comment='New comment'; +alter table mysqltest_uppercase.t_bug44738_UPPERCASE2 comment='New comment'; +alter table mysqltest_UPPERCASE.t_bug44738_lowercase2 comment='New comment'; +--echo # There should be no stale entries in TDC for our tables after the +--echo # above ALTER TABLE statements so new version of comments should be +--echo # returned by the below query to I_S. +select table_schema, table_name, table_comment from information_schema.tables + where table_schema like 'mysqltest_%' and table_name like 't_bug44738_%' + order by table_name; +drop database mysqltest_UPPERCASE; +use test; + +--echo # Let us check that the original test case which led to discovery +--echo # of this problem also works. +create table t_bug44738_UPPERCASE (i int); +select table_schema, table_name, table_comment from information_schema.tables + where table_schema = 'test' and table_name like 't_bug44738_%'; +drop table t_bug44738_UPPERCASE; +--echo # After the above DROP TABLE there are no entries in TDC which correspond +--echo # to our table and therefore the below statement should succeed. +create table t_bug44738_UPPERCASE (i int); +drop table t_bug44738_UPPERCASE; + +--echo # Finally, let us check that another issue which was exposed by +--echo # the original test case is solved. I.e. that the table is not +--echo # created if there is an entry for it in TDC even though it was +--echo # removed from disk. +create table t_bug44738_UPPERCASE (i int) engine = myisam; +--echo # Load table definition in TDC. +select table_schema, table_name, table_comment from information_schema.tables + where table_schema = 'test' and table_name like 't_bug44738_%'; +--echo # Simulate manual removal of the table. +let $MYSQLD_DATADIR= `select @@datadir`; +--remove_file $MYSQLD_DATADIR/test/t_bug44738_UPPERCASE.frm +--remove_file $MYSQLD_DATADIR/test/t_bug44738_UPPERCASE.MYD +--remove_file $MYSQLD_DATADIR/test/t_bug44738_UPPERCASE.MYI +--echo # Check that still there is an entry for table in TDC. +show open tables like 't_bug44738_%'; +--echo # So attempt to create table with the same name should fail. +--error ER_TABLE_EXISTS_ERROR +create table t_bug44738_UPPERCASE (i int); +--echo # And should succeed after FLUSH TABLES. +flush tables; +create table t_bug44738_UPPERCASE (i int); +drop table t_bug44738_UPPERCASE; + +--echo # +--echo # Bug #21317 SHOW CREATE DATABASE does not obey to +--echo # lower_case_table_names +--echo # + +create database mysql_TEST; +create table mysql_TEST.T1(a int); +show databases like 'mysql%'; +show databases like 'mysql_T%'; +show databases like 'mysql_t%'; +show create database mysql_test; +show create database mysql_TEST; +show create table mysql_TEST.T1; +show create table mysql_test.t1; +drop database mysql_TEST; + +--echo # MDEV-30765 SHOW TABLES not working properly with +--echo # lower_case_table_names=2 +--echo # +create database db1; +use db1; +--echo # lowercase table name +create table `a` (a int); +--echo # uppercase table name +create table `B` (a int); + +create user 'mysqltest_1'@'localhost' identified by 'password'; + +grant select, show view on db1.`a` to 'mysqltest_1'@'localhost'; +grant select, show view on db1.`B` to 'mysqltest_1'@'localhost'; + +connect (conn1, localhost, mysqltest_1, password, db1); +show tables; + +connection default; +disconnect conn1; +drop user 'mysqltest_1'@'localhost'; +drop tables a, B; +drop database db1; + +--echo # +--echo # MDEV-32025 Crashes in MDL_key::mdl_key_init with lower-case-table-names=2 +--echo # + +CREATE DATABASE `#mysql50#D+b1`; +ALTER DATABASE `#mysql50#D+b1` UPGRADE DATA DIRECTORY NAME; +SHOW CREATE DATABASE `D+b1`; +SHOW CREATE DATABASE `d+b1`; +DROP DATABASE `D+b1`; + +CREATE DATABASE Db1; +ALTER DATABASE Db1 DEFAULT CHARACTER SET utf8; +SHOW CREATE DATABASE Db1; +SHOW CREATE DATABASE db1; +DROP DATABASE Db1; |