From f4ff086abea975222572fcfd232bf296018f5d85 Mon Sep 17 00:00:00 2001 From: Arun Kuruvila Date: Fri, 21 Aug 2015 08:35:42 +0530 Subject: Bug#20198490 : LOWER_CASE_TABLE_NAMES=0 ON WINDOWS LEADS TO PROBLEMS Description:- Server variable "--lower_case_tables_names" when set to "0" on windows platform which does not support case sensitive file operations leads to problems. A warning message is printed in the error log while starting the server with "--lower_case_tables_names=0". Also according to the documentation, seting "lower_case_tables_names" to "0" on a case-insensitive filesystem might lead to index corruption. Analysis:- The problem reported in the bug is:- Creating an INNODB table 'a' and executing a query, "INSERT INTO a SELECT a FROM A;" on a server started with "--lower_case_tables_names=0" and running on a case-insensitive filesystem leads innodb to flat spin. Optimizer thinks that "a" and "A" are two different tables as the variable "lower_case_table_names" is set to "0". As a result, optimizer comes up with a plan which does not need a temporary table. If the same table is used in select and insert, a temporary table is needed. This incorrect optimizer plan leads to infinite insertions. Fix:- If the server is started with "--lower_case_tables_names" set to 0 on a case-insensitive filesystem, an error, "The server option 'lower_case_table_names'is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode.", is printed in the server error log and the server exits. --- mysql-test/r/lowercase_fs_on.result | 3 ++ mysql-test/r/lowercase_table3.result | 11 ------- .../suite/innodb/t/innodb_bug60229-master.opt | 1 - mysql-test/suite/innodb/t/innodb_bug60229.test | 4 +++ mysql-test/suite/jp/t/jp_enum_sjis-master.opt | 1 - mysql-test/suite/jp/t/jp_enum_sjis.test | 4 +++ mysql-test/suite/jp/t/jp_enum_ucs2-master.opt | 1 - mysql-test/suite/jp/t/jp_enum_ucs2.test | 4 +++ mysql-test/suite/jp/t/jp_enum_ujis-master.opt | 1 - mysql-test/suite/jp/t/jp_enum_ujis.test | 4 +++ mysql-test/suite/jp/t/jp_enum_utf8-master.opt | 1 - mysql-test/suite/jp/t/jp_enum_utf8.test | 4 +++ mysql-test/t/lowercase_fs_on.test | 38 ++++++++++++++++++++++ mysql-test/t/lowercase_table3-master.opt | 1 - mysql-test/t/lowercase_table3.test | 37 --------------------- sql/mysqld.cc | 15 +++++---- 16 files changed, 69 insertions(+), 61 deletions(-) create mode 100644 mysql-test/r/lowercase_fs_on.result delete mode 100644 mysql-test/r/lowercase_table3.result delete mode 100644 mysql-test/suite/innodb/t/innodb_bug60229-master.opt delete mode 100644 mysql-test/suite/jp/t/jp_enum_sjis-master.opt delete mode 100644 mysql-test/suite/jp/t/jp_enum_ucs2-master.opt delete mode 100644 mysql-test/suite/jp/t/jp_enum_ujis-master.opt delete mode 100644 mysql-test/suite/jp/t/jp_enum_utf8-master.opt create mode 100644 mysql-test/t/lowercase_fs_on.test delete mode 100644 mysql-test/t/lowercase_table3-master.opt delete mode 100644 mysql-test/t/lowercase_table3.test diff --git a/mysql-test/r/lowercase_fs_on.result b/mysql-test/r/lowercase_fs_on.result new file mode 100644 index 00000000000..a090f46cfbf --- /dev/null +++ b/mysql-test/r/lowercase_fs_on.result @@ -0,0 +1,3 @@ +# +# Bug#20198490 : LOWER_CASE_TABLE_NAMES=0 ON WINDOWS LEADS TO PROBLEMS +# diff --git a/mysql-test/r/lowercase_table3.result b/mysql-test/r/lowercase_table3.result deleted file mode 100644 index 22e80aaeb26..00000000000 --- a/mysql-test/r/lowercase_table3.result +++ /dev/null @@ -1,11 +0,0 @@ -call mtr.add_suppression("Cannot find or open table test/BUG29839 from"); -DROP TABLE IF EXISTS t1,T1; -CREATE TABLE t1 (a INT); -SELECT * FROM T1; -a -FLUSH TABLES; -DROP TABLE t1; -CREATE TABLE bug29839 (a INT) ENGINE=INNODB; -SELECT * FROM BUG29839; -ERROR 42S02: Table 'test.BUG29839' doesn't exist -DROP TABLE bug29839; diff --git a/mysql-test/suite/innodb/t/innodb_bug60229-master.opt b/mysql-test/suite/innodb/t/innodb_bug60229-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug60229-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/innodb/t/innodb_bug60229.test b/mysql-test/suite/innodb/t/innodb_bug60229.test index 8dcf15157d6..aee0b96a942 100644 --- a/mysql-test/suite/innodb/t/innodb_bug60229.test +++ b/mysql-test/suite/innodb/t/innodb_bug60229.test @@ -2,6 +2,10 @@ # Bug #13083023 - 60229: BROKEN COMPATIBILITY: ERROR WHILE CREATE TABLE # WITH FOREIGN KEY CONSTRAINT. +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc -- source include/have_innodb.inc CREATE TABLE PERSON ( diff --git a/mysql-test/suite/jp/t/jp_enum_sjis-master.opt b/mysql-test/suite/jp/t/jp_enum_sjis-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_sjis-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_sjis.test b/mysql-test/suite/jp/t/jp_enum_sjis.test index e1f22f6fe27..1060f83ec06 100644 --- a/mysql-test/suite/jp/t/jp_enum_sjis.test +++ b/mysql-test/suite/jp/t/jp_enum_sjis.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_sjis.inc --source include/have_innodb.inc --character_set sjis diff --git a/mysql-test/suite/jp/t/jp_enum_ucs2-master.opt b/mysql-test/suite/jp/t/jp_enum_ucs2-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_ucs2-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_ucs2.test b/mysql-test/suite/jp/t/jp_enum_ucs2.test index a3d7c47705d..321f8952148 100644 --- a/mysql-test/suite/jp/t/jp_enum_ucs2.test +++ b/mysql-test/suite/jp/t/jp_enum_ucs2.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_ucs2.inc --source include/have_innodb.inc diff --git a/mysql-test/suite/jp/t/jp_enum_ujis-master.opt b/mysql-test/suite/jp/t/jp_enum_ujis-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_ujis-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_ujis.test b/mysql-test/suite/jp/t/jp_enum_ujis.test index 17e41e0691e..10e8ad55a3c 100644 --- a/mysql-test/suite/jp/t/jp_enum_ujis.test +++ b/mysql-test/suite/jp/t/jp_enum_ujis.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_ujis.inc --source include/have_innodb.inc diff --git a/mysql-test/suite/jp/t/jp_enum_utf8-master.opt b/mysql-test/suite/jp/t/jp_enum_utf8-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/suite/jp/t/jp_enum_utf8-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/suite/jp/t/jp_enum_utf8.test b/mysql-test/suite/jp/t/jp_enum_utf8.test index ff5978da69c..f67939bd02b 100644 --- a/mysql-test/suite/jp/t/jp_enum_utf8.test +++ b/mysql-test/suite/jp/t/jp_enum_utf8.test @@ -1,3 +1,7 @@ +#Server variable option 'lower_case_table_names' sets '0' as default value +#in case sensitive filesystem. Using 'lower_case_table_names=0' in case of +#insensitive filsystem is not allowed. +-- source include/have_case_sensitive_file_system.inc --source include/have_utf8.inc --source include/have_innodb.inc --disable_warnings diff --git a/mysql-test/t/lowercase_fs_on.test b/mysql-test/t/lowercase_fs_on.test new file mode 100644 index 00000000000..6da3ef32a0b --- /dev/null +++ b/mysql-test/t/lowercase_fs_on.test @@ -0,0 +1,38 @@ +# +# Specific tests for case-insensitive file systems +# i.e. lower_case_filesystem=ON +# +-- source include/have_case_insensitive_file_system.inc +# Embedded server does not support restarting. +--source include/not_embedded.inc + +--echo # +--echo # Bug#20198490 : LOWER_CASE_TABLE_NAMES=0 ON WINDOWS LEADS TO PROBLEMS +--echo # + +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/my_restart.err; + +--error 0,1 +--remove_file $SEARCH_FILE + +#Shutdown the server +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc + +#Start the server with --lower_case_table_names=0 in Windows. +--enable_reconnect +--error 1 +--exec $MYSQLD_CMD --lower_case_table_names=0 > $SEARCH_FILE 2>&1 + +#Search for the error messege in the server error log. +let SEARCH_PATTERN= \[ERROR\] The server option \'lower_case_table_names\' is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination\. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode\.; +--source include/search_pattern_in_file.inc + +#Restart the server +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--source include/wait_until_connected_again.inc + +#Cleanup +--error 0,1 +--remove_file $SEARCH_FILE diff --git a/mysql-test/t/lowercase_table3-master.opt b/mysql-test/t/lowercase_table3-master.opt deleted file mode 100644 index 9b27aef9bf8..00000000000 --- a/mysql-test/t/lowercase_table3-master.opt +++ /dev/null @@ -1 +0,0 @@ ---lower_case_table_names=0 diff --git a/mysql-test/t/lowercase_table3.test b/mysql-test/t/lowercase_table3.test deleted file mode 100644 index f7ca8211288..00000000000 --- a/mysql-test/t/lowercase_table3.test +++ /dev/null @@ -1,37 +0,0 @@ -# -# Test of force of lower-case-table-names=0 -# (User has case insensitive file system and wants to preserve case of -# table names) -# - ---source include/have_innodb.inc ---source include/have_lowercase0.inc ---source include/have_case_insensitive_file_system.inc ---source include/not_windows.inc - -call mtr.add_suppression("Cannot find or open table test/BUG29839 from"); - ---disable_warnings -DROP TABLE IF EXISTS t1,T1; ---enable_warnings - -# -# This is actually an error, but ok as the user has forced this -# by using --lower-case-table-names=0 -CREATE TABLE t1 (a INT); -SELECT * FROM T1; -FLUSH TABLES; -DROP TABLE t1; - -# -# InnoDB should in this case be case sensitive -# Note that this is not true on windows as no this OS, InnoDB is always -# storing things in lower case. -# - -CREATE TABLE bug29839 (a INT) ENGINE=INNODB; ---error ER_NO_SUCH_TABLE -SELECT * FROM BUG29839; -DROP TABLE bug29839; - -# End of 4.1 tests diff --git a/sql/mysqld.cc b/sql/mysqld.cc index c999e0c5a0f..a2532ceddd3 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3551,13 +3551,14 @@ static int init_common_variables() { if (lower_case_table_names_used) { - if (global_system_variables.log_warnings) - sql_print_warning("\ -You have forced lower_case_table_names to 0 through a command-line \ -option, even though your file system '%s' is case insensitive. This means \ -that you can corrupt a MyISAM table by accessing it with different cases. \ -You should consider changing lower_case_table_names to 1 or 2", - mysql_real_data_home); + sql_print_error("The server option 'lower_case_table_names' is " + "configured to use case sensitive table names but the " + "data directory is on a case-insensitive file system " + "which is an unsupported combination. Please consider " + "either using a case sensitive file system for your data " + "directory or switching to a case-insensitive table name " + "mode."); + return 1; } else { -- cgit v1.2.1