Bug #56751 case sensitivity
Submitted: 13 Sep 2010 14:56 Modified: 14 Sep 2010 7:19
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.1.50, 5.6.4 OS:Windows
Assigned to: Assigned Account CPU Architecture:Any
Tags: qc

[13 Sep 2010 14:56] Peter Laursen
Description:
I believe there is a regression in the Server here:

How to repeat:
As regards 2:

show global variables like 'lower_case_table_names';
/* returns
Variable_name           Value 
----------------------  ------
lower_case_table_names  2 
*/

create view V98 as select * from tttt; -- any dummy table
 
show create view V98;
-- 
-- returns
-- CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v98` AS select `tttt`.`id` AS
-- 
note: `V98` became `v98`

Suggested fix:
If the behavior is expected the page at:
http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
.. should list VIEWS explicitly as not-case sensitive (and some *verbosity* on VIEWs would be nice under any circumstance).

BTW: is this site under MySQL/Oracle control:
http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/identifier-case-sensitivity.html
(it is not updated properly)
[13 Sep 2010 15:09] Peter Laursen
I meant:

I believe there is a regression in the Server here as regards lower_case_table_names = 2:
[13 Sep 2010 15:24] Tonci Grgin
IMO, this is related to Bug#56526, Bug#56224, Bug#56510 and Bug#34921.
[13 Sep 2010 15:26] Peter Laursen
Yes .. lots of issues with 'lower_case_table_names'.  It worked fine in MySQL up to and including 4.0.
[14 Sep 2010 7:19] Valeriy Kravchuk
Verified as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.50-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 2     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> create view V98 as select * from t1;
Query OK, 0 rows affected (0.56 sec)

mysql> show create view V98\G
*************************** 1. row ***************************
                View: v98
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `v98` AS select `t1`.`a` AS `a` from `t1`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.06 sec)
[21 Mar 2012 7:06] Valeriy Kravchuk
Bug #64712 was marked as a duplicate of this one.