Bug #25630 INFORMATION_SCHEMA collation wrong for filesystems with case sensitive filenames
Submitted: 15 Jan 2007 15:59 Modified: 23 Jul 2009 22:12
Reporter: Kristian Koehntopp Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0.33-standard-log/5.1BK OS:Linux (Centos 4.x)
Assigned to: CPU Architecture:Any
Tags: bfsm_2007_01_18, information_schema, lower_case_table_names

[15 Jan 2007 15:59] Kristian Koehntopp
Description:
The Query

mysql> select table_schema, table_name, count(*) as anz from tables group by table_schema, table_name having anz > 1;

should never return any rows on a noncorrupt server.

How to repeat:
mysql> select table_schema, table_name, count(*) as anz from tables group by table_schema, table_name having anz > 1;
+--------------+------------+-----+
| table_schema | table_name | anz |
+--------------+------------+-----+
| bp           | CreditCard |   2 |
+--------------+------------+-----+
1 row in set (0.00 sec)

[root@mc01bizdb-01 bp]# ls -1b Cred*frm
Creditcard.frm
CreditCard.frm

Note the spelling variants for Creditcard/CreditCard.

Suggested fix:
1. Fix collation for information_schema. ( table_schema, table_name ) is supposed to be a primary key for I_S.TABLES:
2. Fix the handling of tablename case sensitivity so that we are finally compliant with the SQL standard.
[15 Jan 2007 19:08] Peter Gulutzan
Does the MySQL Reference Manual say that MySQL
uses a special collation for information_schema.tables
that corresponds to the platform?

If not, I don't understand how this is a "bug".
[16 Jan 2007 8:07] Kristian Koehntopp
In I_S, I tried to join STATISTICS to TABLES (and later COLUMNS) to get a list of types used in indices in large tables.

The join between these two tables does not work if you have two tables bp.CreditCard and bp.Creditcard in your system and the system does not use lower_case_table_names and is on a Unix filesystem. It will join the TABLES row bp.CreditCard to statistics row bp.Creditcard. 

To fix this, you have to either

a) cast this manually to BINARY on system with the above filesystem condition (i.e. workaround exists)

b) fix INFORMATION_SCHEMA to provide the proper collation for the table name and schema name, which is dependent on the above filesystem condition

or

c) fix MySQL to treat table and schema names according to the SQL standard

I prefer solution c.
[17 Jan 2007 14:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18259

ChangeSet@1.2382, 2007-01-17 17:59:45+04:00, gluh@mysql.com +6 -0
  Bug#25630 INFORMATION_SCHEMA collation wrong for filesystems with case sensitive filenames
  Binary CS is used for SCHEMA_NAME & TABLE_NAME fields for I_S
[14 Sep 2009 6:30] Roel Van de Paar
See bug #46771
[21 Jan 2010 19:01] Paul DuBois
See http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html.