MySQL Bugs: #45862: lower_case_table_names=2, IS doesn't reflect change if table name is uppercase

Bug #45862	lower_case_table_names=2, IS doesn't reflect change if table name is uppercase
Submitted:	30 Jun 2009 17:20	Modified:	30 Jun 2009 18:09
Reporter:	Shannon Wade	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.35	OS:	MacOS
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
with lower_case_table_names (OSX) and an upper case table name, changes to the table are not correctly updated with information schema. OSX uses hfs which is case insensitive, and mysql uses lower_case_tables_names=2 by default on this platform.

From docs:
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. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

How to repeat:
mysql (root@localhost)::(none) > select version();
+------------------------------------+
| version()                          |
+------------------------------------+
| 5.1.35-enterprise-gpl-advanced-log | 
+------------------------------------+
1 row in set (0.00 sec)

mysql (root@localhost)::(none) > drop database if exists testdb1;
Query OK, 1 row affected (0.01 sec)

mysql (root@localhost)::(none) > create database testdb1;
Query OK, 1 row affected (0.00 sec)

mysql (root@localhost)::(none) > USE testdb1;
Database changed
mysql (root@localhost)::testdb1 > drop table if exists testdb1.T1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql (root@localhost)::testdb1 > CREATE TABLE testdb1.T1 (
    ->       t1_field bigint not null
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql (root@localhost)::testdb1 > select * from information_schema.columns where TABLE_SCHEMA = 'testdb1'\G
*************************** 1. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: testdb1
              TABLE_NAME: T1
             COLUMN_NAME: t1_field
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(20)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
1 row in set (0.00 sec)

mysql (root@localhost)::testdb1 > ALTER TABLE testdb1.T1 MODIFY COLUMN t1_field DATETIME DEFAULT NULL; 
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql (root@localhost)::testdb1 > show create table T1\G
*************************** 1. row ***************************
       Table: T1
Create Table: CREATE TABLE `T1` (
  `t1_field` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql (root@localhost)::testdb1 > select * from information_schema.columns where TABLE_SCHEMA = 'testdb1'\G
*************************** 1. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: testdb1
              TABLE_NAME: T1
             COLUMN_NAME: t1_field
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(20)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
1 row in set (0.01 sec)

mysql (root@localhost)::testdb1 > flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql (root@localhost)::testdb1 > select * from information_schema.columns where TABLE_SCHEMA = 'testdb1'\G
*************************** 1. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: testdb1
              TABLE_NAME: T1
             COLUMN_NAME: t1_field
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: datetime
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: datetime
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
1 row in set (0.01 sec)

mysql (root@localhost)::testdb1 > show global variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    | 
| lower_case_table_names | 2     | 
+------------------------+-------+
2 rows in set (0.01 sec)

Note, changing the create table from:
CREATE TABLE testdb1.T1 (
to
CREATE TABLE testdb1.t1 (
OR quoting `testdb1.T1`

problem does not occur.....

mysql (root@localhost)::testdb1 > select version();
+------------------------------------+
| version()                          |
+------------------------------------+
| 5.1.35-enterprise-gpl-advanced-log | 
+------------------------------------+
1 row in set (0.00 sec)

mysql (root@localhost)::testdb1 > drop database if exists testdb1;
Query OK, 1 row affected (0.00 sec)

mysql (root@localhost)::(none) > create database testdb1;
Query OK, 1 row affected (0.00 sec)

mysql (root@localhost)::(none) > USE testdb1;
Database changed
mysql (root@localhost)::testdb1 > drop table if exists testdb1.t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql (root@localhost)::testdb1 > 
mysql (root@localhost)::testdb1 > CREATE TABLE testdb1.t1 (
    ->       t1_field bigint not null
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql (root@localhost)::testdb1 > 
mysql (root@localhost)::testdb1 > select * from information_schema.columns where TABLE_SCHEMA = 'testdb1'\G
*************************** 1. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: testdb1
              TABLE_NAME: t1
             COLUMN_NAME: t1_field
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(20)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
1 row in set (0.01 sec)

mysql (root@localhost)::testdb1 > 
mysql (root@localhost)::testdb1 > ALTER TABLE testdb1.t1 MODIFY COLUMN t1_field DATETIME DEFAULT NULL;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql (root@localhost)::testdb1 > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `t1_field` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql (root@localhost)::testdb1 > select * from information_schema.columns where TABLE_SCHEMA = 'testdb1'\G
*************************** 1. row ***************************
           TABLE_CATALOG: NULL
            TABLE_SCHEMA: testdb1
              TABLE_NAME: t1
             COLUMN_NAME: t1_field
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: datetime
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: datetime
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
1 row in set (0.00 sec)

Suggested fix:
Not sure, seems flushing tables fixes, however could leave some things out of sync otherwise. Though a change should be correctly represented by IS, seems as if something here is case sensitive though docs state otherwise.

Forgot to add, notice above how the output from information_schema doesn't change for that column after the alter table. Before bigint, alter change to datetime, IS still shows bigint until a flush tables is ran.

Hi,

This behaviour is exactly how lower case table names of 2 should work and how Mac OS filesystems function.

However, as it is not documented properly, for Mac OS only, I am changing it to documentation bug.

Thank you for your report.