MySQL Bugs: #71407: Letter case inconsistency wiht schema/database names

Bug #71407	Letter case inconsistency wiht schema/database names
Submitted:	17 Jan 2014 12:12	Modified:	6 Jun 2014 21:34
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.6.15 (at least(	OS:	Windows (probably any)
Assigned to:		CPU Architecture:	Any

Description:
With lower_case_tables_names = 2 on Windows and = 0 on Linux, metadata for database are sometimes returned as lowercase and sometimes as UPPERCASE. 

How to repeat:
-- on Windows
SHOW VARIABLES LIKE 'lower_case_table_names'; -- 2
CREATE DATABASE `D`;
DELIMITER $$
CREATE PROCEDURE `D`.`P`()
    BEGIN
	-- nothing
    END$$
DELIMITER ;
SHOW DATABASES LIKE 'D'; -- 'D' is returned in uppercase
SELECT `ROUTINE_SCHEMA` FROM `information_schema`.`ROUTINES`; -- 'd' is returned in lowercase

Suggested fix:
I think best options is to store in I_S with the lettercase specified by client/user. Alternatively SHOW DATABASES should return lowercase when l_c_t_n is set to a case insensitive value..

If you write a client that shall work with all settings for l_c_t_n you will have to handle case sensitive database names.  What means that SELECT queries from I_S need to ".. COLLATE utf8_bin". However when doing so and when comparing with output from SHOW DATABASES in the client/application this inconsistence cases a problem. 

This may result in no match found with case insensitive setting for l_c_t_n when there actually is a (case insensitive) match.  

(workaround is of course to check for the condition and include or omit ".. COLLATE utf8_bin" or covert to lowercase in the application depending on the l_c_t_n setting)

Also create a Table and an Event in the same database 'D' created in Uppercase.

SELECT `ROUTINE_SCHEMA` FROM `information_schema`.`ROUTINES`;
-- returns 'd' in lower case.

SELECT `ROUTINE_SCHEMA` FROM `information_schema`.`TABLES`;
-- returns 'D' in upper case.

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

Let me elaborate a little: If you from an application execute SHOW DATABASES for retrieving all database names and next from comparison with the result from a query to I_S try to identify to which database each routine belongs there are two cases that may go wrong:

1) If you compare case insensitive, some routines may be identified as belonging to a wrong database in cases where identically named databases except for lettercase exist (what is possible with lowercase_table_names = 0 of systems with case sensitive file system).
2) If you compare case sensitive, the routine will not be identified as belonging to any database in case the queries SHOW DATABASES and SELECT FROM I_S.ROUTINES return the database in different lettercase.

This is how we discovered this (after a bug report to us where it was point 2 above that affeccted our application). We will now query the status of lower_case_tablenames in order to decide how we should handle case sensitivity of data from I_S or not. Our application is supposed to work with all settings of lowercase_tables_names and even 2 servers with different settings at the same time (for copying, data and schema synchronization etc. between them).

BTW. I think this also could be a problem with various backlup/restore routines between servers running different settings for lower_case_table_names. If you are dumping from a database  (named 'A') with routine from a server with lowercase_table_names = 2 and creating the database to another with lower_case_table_names = 0 and where there is already another existing database named 'a', it may very well happen that the routine on the target server gets created in database 'a' and not 'A' (if you rely on lettercase in I_S and generate CREATE PROCEDURE|FUNCTION statements using that lettercase for the database). If this is not noticed when it happens, the routine may not be executed when it should and data corruption may evolve as a result.

See also bug #71059

And bug #57830

Not repeatable on Linux. One more bug which can be related: bug #35084

Thank you for the bug report.

c:\mysql-5.5.38-winx64>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> SHOW VARIABLES LIKE 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 2     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE `D`;
Query OK, 1 row affected (0.02 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE `D`.`P`()
    ->     BEGIN
    ->  -- nothing
    ->     END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SHOW DATABASES LIKE 'D';
+--------------+
| Database (D) |
+--------------+
| D            |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT `ROUTINE_SCHEMA` FROM `information_schema`.`ROUTINES`;
+----------------+
| ROUTINE_SCHEMA |
+----------------+
| d              |
| test           |
+----------------+
2 rows in set (0.06 sec)