MySQL Bugs: #75185: lower_case_table_names=0 on windows leads to problems

Bug #75185	lower_case_table_names=0 on windows leads to problems
Submitted:	11 Dec 2014 16:36	Modified:	21 Aug 2015 17:24
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6.21, 5.6.22	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Although it is clearly documented this is a *bad idea* , please either make it work better, or prevent the option being set to 0 when it can cause trouble..

The testcase puts innodb into a flat spin, inserting data from who knows where endlessly.

show engine innodb status\G

---TRANSACTION 1448215, ACTIVE 14 sec inserting
mysql tables in use 2, locked 2
42973 lock struct(s), heap size 5038288, 2774121 row lock(s), undo log entries 1385718
MySQL thread id 1, OS thread handle 5556, query id 11 localhost 127.0.0.1 root Sending data
insert into a select a from A

How to repeat:
#start windows server with --lower-case-table-names=0 then:

select @@global.lower_case_table_names,@@global.lower_case_file_system,version();
drop table if exists a,A;
create table a(a int)engine=innodb;
insert into a values(1);
insert into a select a from A; ##hangs ....

Suggested fix:
on trunk we luckily get a warning already... Should be prevent server to start, or automatically set lower_case_table_names to a supported value??

[Warning] You have forced lower_case_table_names to 0 through a command-line option, even though your file system 'F:\git\mysql-trunk\bld\sql\data\' 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

Hello Shane,

Thank you for the report and test case.
Verified as described with 5.6.22 GA release.

Thanks,
Umesh

// 5.6.22 also warns

D:\ushastry\MySQL\mysql-advanced-5.6.22-winx64>bin\mysqld --defaults-file=./my.ini --lower-case-table-names=0
2014-12-11 22:35:08 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-12-11 22:35:08 23496 [Warning] You have forced lower_case_table_names to 0 through a command-line option, even though your file system 'D:\ushastry\MySQL\mysql-advanced-5.6.22-winx64\data\' 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> use test
Database changed
mysql> select @@global.lower_case_table_names,@@global.lower_case_file_system,version();
+---------------------------------+---------------------------------+---------------------------------------+
| @@global.lower_case_table_names | @@global.lower_case_file_system | version()                             |
+---------------------------------+---------------------------------+---------------------------------------+
|                               0 |                               1 | 5.6.22-enterprise-commercial-advanced |
+---------------------------------+---------------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists a,A;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> create table a(a int)engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into a values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into a select a from A;

^^ hangs

------------
TRANSACTIONS
------------
Trx id counter 1319
Purge done for trx's n:o < 1319 undo n:o < 0 state: running but idle
History list length 14
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2, OS thread handle 0x5b68, query id 12 localhost 127.0.0.1 root init
show engine innodb status
---TRANSACTION 1290, ACTIVE 86 sec fetching rows
mysql tables in use 2, locked 2
286251 lock struct(s), heap size 33502760, 18480772 row lock(s), undo log entries 9231441
MySQL thread id 1, OS thread handle 0x5058, query id 10 localhost 127.0.0.1 root Sending data
insert into a select a from A
--------

see http://bugs.mysql.com/bug.php?id=75167

Posted by developer:
 
Fixed as of the upcoming 5.5.46, 5.6.27, 5.7.9, 5.8.0 releases, and here's the changelog entry:

When the server is started with --lower_case_tables_names=0 on a
case-insensitive file system such as Windows, an INSERT INTO ... SELECT
... FROM tbl_name operation could result in a hang condition if the wrong
case is used when specifying the table name. To help avoid this problem,
an error message is now printed, which states that, 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. 

Thank you for the bug report.

Posted by developer:
 
The changelog entry was revised as follows:

Setting lower_case_table_names=0 on a case-insensitive file system could
result in a hang condition when running an INSERT INTO ... SELECT ... FROM
tbl_name operation with the wrong tbl_name letter case. An error message
is now printed and the server exits if you attempt to start the server
with --lower_case_table_names=0 on a case-insensitive file system. The
error message states, 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. 

Updates were also applied to:

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_lower_case_fil...