MySQL Bugs: #44738: fill_schema_table_from_frm opens tables without lowercasing table name

Bug #44738	fill_schema_table_from_frm opens tables without lowercasing table name
Submitted:	8 May 2009 7:04	Modified:	12 Nov 2009 19:22
Reporter:	Jørgen Løland	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any (powermacg5)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
On platforms where lower_case_file_names>0, the server API for opening tables assumes that the table and database names are in lower case. fill_schema_table_from_frm() does not honor this lowercase assumption when opening tables. 

Note that the problem is only encountered on lcfn=2 platforms (powermacg5). 

How to repeat:
CREATE DATABASE `UcAsE`;
USE `UcAsE`;
CREATE TABLE `TaBlE`(rint INT);

SHOW OPEN TABLES;
SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES;
SHOW OPEN TABLES;

Result from running the above:
------------------------------
mysql>CREATE DATABASE `UcAsE`;
mysql>USE `UcAsE`;
mysql>CREATE TABLE `TaBlE`(rint INT);

mysql>SHOW OPEN TABLES;
Database        Table   In_use  Name_locked
mysql   servers 0       0
mysql   time_zone_transition_type       0       0
mysql   time_zone       0       0
mysql   backup_history  0       0
mysql   ndb_binlog_index        0       0
mysql   db      0       0
mysql   user    0       0
mysql   tables_priv     0       0
mysql   procs_priv      0       0
mysql   help_keyword    0       0
mysql   help_category   0       0
mysql   help_relation   0       0
mysql   func    0       0
mysql   slow_log        0       0
mysql   columns_priv    0       0
mysql   host    0       0
mysql   general_log     0       0
mysql   proc    0       0
mysql   backup_progress 0       0
mysql   help_topic      0       0
mysql   time_zone_leap_second   0       0
mysql   time_zone_name  0       0
mysql   event   0       0
mysql   time_zone_transition    0       0
mysql   plugin  0       0

mysql>SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES;
(...)

mysql>SHOW OPEN TABLES;
(...)
UcAsE   TaBlE   0       0      <- This is the problem; should be lowercase (ppc)
(...)

Suggested fix:
Make sure fill_schema_table_from_frm opens tables with lower case names if lower_case_file_names>0

Triage: since 6.0-beta level bug depends on this bug,
it should be triaged accordingly.

Code inspection shows that this bug should also affect 5.1 branch.

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/74136

2777 Dmitry Lenev	2009-05-15
      Fix for bug #44738 "fill_schema_table_from_frm() opens tables without
      lowercasing table name".
      
      In lower_case_table_names > 0 mode some queries to I_S left entries
      with incorrect key in table definition cache. This wasted memory and
      in combination with similar problem in CREATE TABLE (which also has 
      peeked into table definition cache using non-normalized key) led to 
      to spurious ER_TABLE_EXISTS_ERROR errors when one tried to create a 
      table with the same name as a previously existing but dropped table
      (assuming that table name contained characters in upper case).
      
      This problem occured due to fact that fill_schema_table_from_frm()
      was not properly normalizing (lowercasing) database and table names 
      which it used for lookups in table definition cache.
      
      This fix adds proper normalization to this function. It also solves
      similar problem in CREATE TABLE's code by ensuring that it uses 
      properly normalized version of table name when it peeks into table
      definition cache instead of non-normalized one.
     @ mysql-test/r/lowercase_table2.result
        Added test for #44738 "fill_schema_table_from_frm() opens tables
        without lowercasing table name".
     @ mysql-test/t/lowercase_table2.test
        Added test for #44738 "fill_schema_table_from_frm() opens tables
        without lowercasing table name".
     @ sql/sql_show.cc
        Normalize database and table name before using them for looking
        up entry in table definition cache.
     @ sql/sql_table.cc
        Ensure that CREATE TABLE uses properly normalized version of table
        name when it peeks into table definition cache.

Pushed into 6.0.12-alpha (revid:alik@sun.com-20090516083658-bo81fcfqxpqoocvb) (version source revid:dlenev@mysql.com-20090515080035-pvl5bz5gbc870npz) (merge vers: 6.0.12-alpha) (pib:6)

Noted in 6.0.12 changelog.

For settings of lower_case_table_names greater than 0, some queries
for INFORMATION_SCHEMA tables left entries with incorrect lettercase
in the table definition cache.

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/86041

2886 Dmitry Lenev	2009-10-07
      Fix for bug #44738 "fill_schema_table_from_frm() opens tables without    
      lowercasing table name".
      
      In lower_case_table_names > 0 mode some queries to I_S left entries      
      with incorrect key in table definition cache. This wasted memory and      
      caused some of the further queries to I_S to produce stale results
      in cases when table definition was changed by a DDL statement.
      Also in combination with similar problem in CREATE TABLE (which also 
      has peeked into table definition cache using non-normalized key) this
      issue led to to spurious ER_TABLE_EXISTS_ERROR errors when one tried 
      to create a table with the same name as a previously existing but 
      dropped table (assuming that table name contained characters in upper 
      case).
      
      This problem occured due to fact that fill_schema_table_from_frm()
      was not properly normalizing (lowercasing) database and table names      
      which it used for lookups in table definition cache.
      
      This fix adds proper normalization to this function. It also solves      
      similar problem in CREATE TABLE's code by ensuring that it uses       
      properly normalized version of table name when it peeks into table      
      definition cache instead of non-normalized one.
     @ mysql-test/r/lowercase_table2.result
        Added test for #44738 "fill_schema_table_from_frm() opens tables        
        without lowercasing table name".
     @ mysql-test/t/lowercase_table2.test
        Added test for #44738 "fill_schema_table_from_frm() opens tables        
        without lowercasing table name".
     @ sql/sql_show.cc
        Normalize database and table name before using them for looking
        up entry in table definition cache.
     @ sql/sql_table.cc
        Ensure that CREATE TABLE uses properly normalized version of table
        name when it peeks into table definition cache.

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/86295

2888 Dmitry Lenev	2009-10-09
      Fix for bug #44738 "fill_schema_table_from_frm() opens tables without
      lowercasing table name".
      
      In lower_case_table_names > 0 mode some queries to I_S left entries
      with incorrect key in table definition cache. This wasted memory and
      caused some of the further queries to I_S to produce stale results
      in cases when table definition was changed by a DDL statement.
      Also in combination with similar problem in CREATE TABLE (which also
      has peeked into table definition cache using non-normalized key) this
      issue led to to spurious ER_TABLE_EXISTS_ERROR errors when one tried
      to create a table with the same name as a previously existing but
      dropped table (assuming that table name contained characters in upper
      case).
      
      This problem occured due to fact that fill_schema_table_from_frm()
      was not properly normalizing (lowercasing) database and table names
      which it used for lookups in table definition cache.
      
      This fix adds proper normalization to this function. It also solves
      similar problem in CREATE TABLE's code by ensuring that it uses
      properly normalized version of table name when it peeks into table
      definition cache instead of non-normalized one.
     @ mysql-test/r/lowercase_table2.result
        Added test for #44738 "fill_schema_table_from_frm() opens tables
        without lowercasing table name".
     @ mysql-test/t/lowercase_table2.test
        Added test for #44738 "fill_schema_table_from_frm() opens tables
        without lowercasing table name".
     @ sql/sql_show.cc
        Normalize database and table name before using them for looking
        up entry in table definition cache.
     @ sql/sql_table.cc
        Ensure that CREATE TABLE uses properly normalized version of table
        name when it peeks into table definition cache.

Fix for this bug is going to be pushed in 5.5.0.

Noted in 5.5.0 changelog.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@ibmvm-20091009114750-utkhs9029vewkluf) (merge vers: 6.0.14-alpha) (pib:13)

Already fixed in earlier 6.0.x release.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)