MySQL Bugs: #41676: Table names are case insensitive in locking?

Bug #41676	Table names are case insensitive in locking?
Submitted:	22 Dec 2008 15:10	Modified:	18 Jun 2010 13:01
Reporter:	Marko Mäkelä	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1, 6.0 bzr	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
The function get_share() was added to ha_innodb.cc by someone at MySQL. The last change according to "bzr blame" was 2119.297.1 by aivanov:

revno: 2119.297.1
committer: aivanov@mysql.com
timestamp: Thu 2006-03-30 00:57:27 +0400
message:
  Null-merge

The problem with this function is that it uses a hash table initialized by hash_init(&innobase_open_tables, system_charset_info, ...) and system_charset_info is case insensitive while InnoDB tables are not.

Other engines may suffer from the same bug. At least ndbcluster contains this:

(void) hash_init(&ndbcluster_open_tables,system_charset_info,32,0,0,
                   (hash_get_key) ndbcluster_get_key,0,0);

How to repeat:
create table t(a int)engine=innodb;
create table T(a int)engine=innodb;
select * from t;
select * from T;

Note that hash_search() called from ha_innodb.cc will return the same object for both test/t and test/T.

Suggested fix:
Use a case-sensitive collation for comparisons on systems where table names are case sensitive (all systems except Windows). The hash_sort() can continue to be case insensitive.

I stumbled upon this some time ago while working on Bug#25164. It would be interesting to get this sorted out.

Note that Mac OS X filesystem (HFS+) is a case preserving, case insensitive filesystem.

I stumbled on this when I developed a patch that removes the ha_innodb.cc dependency on the MySQL <hash.h>. The symbol name of some function declared in <hash.h> was changed in the GA release of MySQL 5.1. We want maximum binary compatibility in the InnoDB plugin.

Thank you for the report.

I can not repeat described behavior on Solaris. Please indicate which operating system do you run.

To repeat this again, I downloaded and compiled http://bazaar.launchpad.net/%7Emysql/mysql-server/mysql-5.1/ revision 2705.

I set a breakpoint in get_share() in storage/innobase/handler/ha_innodb.cc:7004 and observed the return value of my_hash_search() when feeding the SQL from the "How to repeat" section from one client connection:

Breakpoint 1, get_share (table_name=0x88ea780 "./test/T")
    at handler/ha_innodb.cc:7004
(gdb) s
my_hash_search (hash=0x87957a0, key=0x88ea780 "./test/T", length=8)
    at hash.c:192
Current language:  auto; currently c
(gdb) fin
Run till exit from #0  my_hash_search (hash=0x87957a0, 
    key=0x88ea780 "./test/T", length=8) at hash.c:192
0x0840a3e1 in get_share (table_name=0x88ea780 "./test/T")
    at handler/ha_innodb.cc:7004
Value returned is $2 = (uchar *) 0x88dea78 ""
Current language:  auto; currently c++

The tables t and T should be distinct. However, the invocation on get_share("./test/T") will return the same INNOBASE_SHARE object that was returned by get_share("./test/t").

Please show the exact steps that you used if you still can't repeat this bug.

Oh, sorry, I forgot to mention the operating system: Debian GNU/Linux 4.0, kernel 2.6.24-etchnhalf.1-686, gcc 4.1.1-15, GNU libc 2.3.6.ds1-13etch8.

I didn't see anything in the MySQL source code that would make system_charset_info case-sensitive on some systems and case-insensitive on others. I expected it to be case-insensitive on Windows and possibly Mac OS X, but not on other systems.

Marko,

thank you for the feedback.

Verified as described:

Breakpoint 3, get_share (table_name=0x9f5f238 "./test/t") at handler/ha_innodb.cc:7004
7004            if (!(share=(INNOBASE_SHARE*) hash_search(&innobase_open_tables,
(gdb) s
my_hash_search (hash=0x8875460, key=0x9f5f238 "./test/t", length=8) at hash.c:192
192       return my_hash_first(hash, key, length, &state);
Current language:  auto; currently c
(gdb) fin
Run till exit from #0  my_hash_search (hash=0x8875460, key=0x9f5f238 "./test/t", length=8) at hash.c:192
0x084713d2 in get_share (table_name=0x9f5f238 "./test/t") at handler/ha_innodb.cc:7004
7004            if (!(share=(INNOBASE_SHARE*) hash_search(&innobase_open_tables,
Value returned is $11 = (uchar *) 0x0
Current language:  auto; currently c++
(gdb) c
Continuing.

Breakpoint 3, get_share (table_name=0xa483110 "./test/T") at handler/ha_innodb.cc:7004
7004            if (!(share=(INNOBASE_SHARE*) hash_search(&innobase_open_tables,
(gdb) s
my_hash_search (hash=0x8875460, key=0xa483110 "./test/T", length=8) at hash.c:192
192       return my_hash_first(hash, key, length, &state);
Current language:  auto; currently c
(gdb) fin
Run till exit from #0  my_hash_search (hash=0x8875460, key=0xa483110 "./test/T", length=8) at hash.c:192
0x084713d2 in get_share (table_name=0xa483110 "./test/T") at handler/ha_innodb.cc:7004
7004            if (!(share=(INNOBASE_SHARE*) hash_search(&innobase_open_tables,
Value returned is $12 = (uchar *) 0xa47ff88 ""
Current language:  auto; currently c++
(gdb) c

Marko,
why do you regard it as a bug in the server?
It's a bug in InnoDB, isn't it?
You should be looking at the way the functionality is implemented in MySQL table cache, and do the same. In a nutshell, you must use a case sensitive collation in your hash. In case MySQL lowercases table names, a normalized table name is supplied as input to all hashes, which ensures correct behavior with case-sensitive hashes.
This is also true for NDB.
See also Bug#37700.

Konstantin, thanks for pointing this out. A binary strcmp() will work just fine. I verified that when lower_case_table_names=1, the table name will be passed to InnoDB in lower case.

This bug was fixed in the InnoDB Plugin 1.0.3, which was released on March 11, 2009. As I am not sure if we should fix it in MySQL 5.1, I will leave this bug open.

Updating to "Patch pending", because the fix has been released in InnoDB Plugin 1.0.3 but it is not yet in the built-in InnoDB.

Close this one. The bug has been fixed in the plugin and it will not be fixed in the 5.1 builtin.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

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)

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)

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)