Description:
I spent about 2 hours trying to figure out why I could create and read from a table on a
debian machine, but not on my mac OS X machine. When I created the table on my mac OS
machine the table would show up in 'show tables' but I would get an error saying the table
did not exist if I tried to do an insert/select on it.

Once I did some testing with different table name I discovered if I used all lower-case
letters for the table name then the table would work fine.

How to repeat:
create table Test1
(
id int not null auto_increment,
someKey int not null, primary key(id, someKey)
) engine=InnoDB
partition by list(someKey) ( partition p1 values in (1), partition p2 values in(2));

select * from Test1 -> Table test1 doesn't exist

but if you change the table name from Test1 to test1 it works fine.

Thank you for the report.

Verified as described.

Bug is not repeatable with MyISAM.

This is yet another upper case / lower case file name problem on OS X.

Are you using a case-insensitive file system or case-sensitive?

A workaround is to create all table names lower case or put lower-case-table-names to
my.cnf.

Regards,

Heikki

I suspect this is a bug in MySQL partitioning code. InnoDB does not convert table name
case on OS X any more. Only on Windows, InnoDB "normalizes" table name to lower case.

If partitioning code on OS X for some reason converts partition table names to lower case
in some cases, and does not convert them in some cases, then InnoDB cannot find partition
tables from its internal, case-sensitive data dictionary.

ha_innodb.cc in 5.0:

Normalizes a table name string. A normalized name consists of the
database name catenated to '/' and table name. An example:
test/mytable. On Windows normalization puts both the database name and the
table name always to lower case. */
static
void
normalize_table_name(
/*=================*/
        char*           norm_name,      /* out: normalized name as a
                                        null-terminated string */
        const char*     name)           /* in: table name string */
{
        char*   name_ptr;
        char*   db_ptr;
        char*   ptr;

        /* Scan name from the end */

        ptr = strend(name)-1;

        while (ptr >= name && *ptr != '\\' && *ptr != '/') {
                ptr--;
        }

        name_ptr = ptr + 1;

        DBUG_ASSERT(ptr > name);

        ptr--;

        while (ptr >= name && *ptr != '\\' && *ptr != '/') {
                ptr--;
        }

        db_ptr = ptr + 1;

        memcpy(norm_name, db_ptr, strlen(name) + 1 - (db_ptr - name));

        norm_name[name_ptr - db_ptr - 1] = '/';

#ifdef __WIN__
        innobase_casedn_str(norm_name);
#endif
}

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

2673 Mattias Jonsson	2008-06-29
      Bug#37402: Mysql cant read partitioned table with capital letter in the name
      
      Problem was that ha_partition had HA_FILE_BASED flag set
      (since it uses a .par file), but after open it uses the first partitions
      flags, which results in different case handling for create and for
      open.
      
      Solution was to change the underlying partition name so it was consistent.
      (Only happens when lower_case_table_names = 2, i.e. Mac OS X and storage
      engines without HA_FILE_BASED, like InnoDB and Memory.)

The above patch also fixes a similar bug in Memory storage engine for
lower_case_table_names = 2:
It finds the table, but is empty on select. (Can be verified with test
mysql-test/suite/parts/partition_mgm_lc2_memory.test without the ha_partition.cc patch.)

After renaming "check_lowercase_names", I'm happy with this patch, #2673.

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

2673 Mattias Jonsson	2008-07-08
      Bug#37402: Mysql cant read partitioned table with capital letter in the name
      
      Problem was that ha_partition had HA_FILE_BASED flag set
      (since it uses a .par file), but after open it uses the first partitions
      flags, which results in different case handling for create and for
      open.
      
      Solution was to change the underlying partition name so it was consistent.
      (Only happens when lower_case_table_names = 2, i.e. Mac OS X and storage
      engines without HA_FILE_BASED, like InnoDB and Memory.)
      
      (Recommit after adding rename of check_lowercase_names to
      get_canonical_filename)

If a partitioned table was created when lower_case_table_names = 2 in a non patched
version, it will be left in a strange state (i.e. frm file name AND innodb data dictionary
name in Mixed case, just like lower_case_table_names = 0).

While using the non patched version, it is OK to rename it to a all lower case name, and
then after upgrading, one can rename it back using upper case names (or simply drop the
table).

But if one has already upgraded the table it is in upper case name in both the frm
filename as well as the InnoDB directory name. Then one have to move the files to a case
sensitive file system (using newfs_hfs -s, when creating it) start the server using (it
will then default to lower_case_table_names = 0) rename the table to lower case (It will
now find both the frm file and the table in the InnoDB data dictionary), stop the server,
move the files back to the original disk and start the server again and rename them back
to its upper case name (which now will be in upper case in the frm file name, and in lower
case in the InnoDB data dictionary.)

Just to make the above notice clearer, even after upgrading, it is OK to stop the server
and restart using the non patched version and do the renaming and the stop. And finally
start with the updated server agian for renaming the tables to what the should be.

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

2673 Mattias Jonsson	2008-07-11
      Bug#37402: Mysql cant read partitioned table with capital letter in the name
      
      Problem was that ha_partition had HA_FILE_BASED flag set
      (since it uses a .par file), but after open it uses the first partitions
      flags, which results in different case handling for create and for
      open.
      
      Solution was to change the underlying partition name so it was consistent.
      (Only happens when lower_case_table_names = 2, i.e. Mac OS X and storage
      engines without HA_FILE_BASED, like InnoDB and Memory.)
      
      (Recommit after adding rename of check_lowercase_names to
      get_canonical_filename, and moved it from handler.h to mysql_priv.h)
      
      NOTE: if a mixed case name for a partitioned table was created when
      lower_case_table_name = 2 it should be renamed or dropped before using
      the updated version (See bug#37402 for more info)

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

2693 Mattias Jonsson	2008-08-12 [merge]
      manual merge of bug#37402

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

2695 Mattias Jonsson	2008-08-13
      Bug#37402: Mysql cant read partitioned table with capital letter in the name
      
      Post push fix (compiler warning)

Pushed into mysql-6.0-bugteam and mysql-5.1-bugteam

(Looks like a ndb bug in parts.partition_mgm_lc*, will investigate and report.)

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

2759 Marc Alff	2008-08-13 [merge]
      Merge 5.1-bugteam -> 6.0-bugteam
      
      This is a NULL merge to resolve bzr conflicts,
      the file contents were already merged separately

-- 
MySQL Code Commits Mailing List
For list archives: http://lists.mysql.com/commits
To unsubscribe:    http://lists.mysql.com/commits?unsub=commits@bugs.mysql.com

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

2759 Marc Alff	2008-08-13 [merge]
      Merge 5.1-bugteam -> 6.0-bugteam
      
      This is a NULL merge to resolve bzr conflicts,
      the file contents were already merged separately

 2758 Marc Alff	2008-08-13 [merge]
      Merge 5.1-bugteam -> 6.0-bugteam
      
      Manual merge of sql_yacc.yy

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

2761 Mattias Jonsson	2008-08-13
      Post push fix for bug#35161 and bug#37402

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

2761 Mattias Jonsson	2008-08-13
      Post push fix for bug#35161 and bug#37402

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

2696 Mattias Jonsson	2008-08-13
      Bug#20129: ALTER TABLE ... REPAIR PARTITION ... complains that
      partition is corrupt
      
      Post push fix
      
      an DBUG_ASSERT broke the embedded server, fixed by initializing
      it in the embedded version of Protocol_text::prepare_for_resend

Pushed to 5.1.28

Documented fix in 5.1.27 changelog as follows:

        The server sometimes could not read partitioned tables whose
        names contained uppercase letters.

        Incompatible Change: Partitioned tables using mixed case names should be 
        renamed or dropped before upgrading to this version of the server.

Pushed into 6.0.7-alpha  (revid:mattiasj@mysql.com-20080710231413-c54ylnwo720w0fux)
(version source revid:john.embretsen@sun.com-20080808091208-ht48kyzsk7rim74g) (pib:3)