Bug #37402 Mysql cant read partitioned table with capital letter in the name
Submitted: 13 Jun 2008 18:55 Modified: 20 Jun 2010 22:58
Reporter: Preston Price Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.24-rc OS:Mac OS X
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: D2 (Serious)

[13 Jun 2008 18:55] Preston Price
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.
[16 Jun 2008 12:16] Sveta Smirnova
Thank you for the report.

Verified as described.

Bug is not repeatable with MyISAM.
[17 Jun 2008 17:26] Heikki Tuuri
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
[23 Jun 2008 15:01] Heikki Tuuri
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
}
[29 Jun 2008 14:36] 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/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.)
[29 Jun 2008 14:38] Mattias Jonsson
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.)
[8 Jul 2008 16:27] Chad MILLER
After renaming "check_lowercase_names", I'm happy with this patch, #2673.
[8 Jul 2008 20:28] 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/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)
[10 Jul 2008 15:20] Mattias Jonsson
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.)
[10 Jul 2008 15:46] Mattias Jonsson
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.
[10 Jul 2008 23:14] 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/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)
[12 Aug 2008 10:26] 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/51403

2693 Mattias Jonsson	2008-08-12 [merge]
      manual merge of bug#37402
[13 Aug 2008 8:47] 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/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)
[13 Aug 2008 16:04] Mattias Jonsson
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.)
[13 Aug 2008 18:10] 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/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
[13 Aug 2008 18:13] 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/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
[13 Aug 2008 19:34] 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/51557

2761 Mattias Jonsson	2008-08-13
      Post push fix for bug#35161 and bug#37402
[13 Aug 2008 19:36] 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/51558

2761 Mattias Jonsson	2008-08-13
      Post push fix for bug#35161 and bug#37402
[13 Aug 2008 19:38] 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/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
[25 Aug 2008 10:42] Georgi Kodinov
Pushed to 5.1.28
[2 Sep 2008 12:27] Jon Stephens
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.
[13 Sep 2008 23:35] Bugs System
Pushed into 6.0.7-alpha  (revid:mattiasj@mysql.com-20080710231413-c54ylnwo720w0fux) (version source revid:john.embretsen@sun.com-20080808091208-ht48kyzsk7rim74g) (pib:3)
[5 May 2010 15:19] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 9:54] Jon Stephens
Also documented fix in the 6.0.7 changelog.

Closed.
[28 May 2010 5:50] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:19] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:47] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[30 May 2010 1:07] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:51] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:28] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:16] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)