Bug #18446 INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME always lowercase
Submitted: 23 Mar 2006 2:56 Modified: 28 Feb 2007 22:25
Reporter: Joel Bruick Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.0.22, 5.0.19 OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any
Triage: Triaged: D4 (Minor) / R5 (Severe) / E5 (Major)

[23 Mar 2006 2:56] Joel Bruick
Description:
INFORMATION_SCHEMA.REFERENCED_TABLE_NAME converts table names to lowercase on Windows even when lower_case_table_names=2.

How to repeat:
-- Insert the following schema into a database

CREATE TABLE `T1` (
  `id` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `T2` (
  `t1Id` tinyint(3) unsigned NOT NULL,
  KEY `t1Id` (`t1Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `T2`
  ADD CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1Id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Check REFERENCED_TABLE_NAME
--    Will be: t1
--    Should be: T1

SELECT REFERENCED_TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'T2';
[23 Mar 2006 4:00] Joel Bruick
corrected synopsis
[23 Mar 2006 4:02] Joel Bruick
"INFORMATION_SCHEMA.REFERENCED_TABLE_NAME" should say "INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME"
[23 Mar 2006 6:08] Joel Bruick
Just for clarification, the ADD CONSTRAINT statement should also say "REFERENCES `T1`" instead of "REFERENCES `t1`", but the result is still the same.
[28 Apr 2006 16:48] Valeriy Kravchuk
Thank you for a problem report. Sorry, but manual (http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html) says (for lower_case_table_names=2):

"Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. Note: This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1."

INFORMATION_SCHEMA tables re used for lookup. So, what is the bug here?
[28 Apr 2006 20:50] Joel Bruick
I understand how lower_case_table_names=2 works. It's a bug because everywhere else in INFORMATION_SCHEMA I've looked, columns that hold table names store those names in their original case (KEY_COLUMN_USAGE.TABLE_NAME, for example).
[11 Jun 2006 12:26] Valeriy Kravchuk
Verified just as described. Do not forget to set lower_case_table_names=2 explicitely to repeat. It is incosistent behaviour and, thus, a bug.
[1 Oct 2008 22:41] Konstantin Osipov
WL#2760
[1 Oct 2008 22:41] Konstantin Osipov
Sorry, WL#148 will fix this already.