Bug #49329 example (and other) engines use wrong collation for open tables hash
Submitted: 2 Dec 2009 9:21 Modified: 11 Dec 2009 9:50
Reporter: Sergei Golubchik Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Storage Engines Severity:S3 (Non-critical)
Version:5.1.42-bzr OS:Any
Assigned to: CPU Architecture:Any

[2 Dec 2009 9:21] Sergei Golubchik
Description:
Example storage engine uses a hash - example_open_tables - to map table names to a open tables.

It uses case insensitive collation - system_charset_info - for that. It is incorrect, as it cannot distinguish between different tables which names differ only in letter case.

Other engines NDB, blackhole, csv, InnoDB are also affected. Third party could be affected too, as they typically start from an example or innodb handler.

Federated uses my_charset_bin which probably leads to an opposite problem.

first reported here: http://lists.mysql.com/internals/37573

How to repeat:
for example, CSV engine stores

  THR_LOCK lock;

in the TINA_SHARE. That means that tables which names differ only in letter case will share a table lock. Indeed:

create table t1 (a int not null) engine=csv;
create table T1 (a int not null) engine=csv;
insert t1 values (1+sleep(10));

and from another connection:

insert T1 values (1);

the second insert will wait for the first to finish. According to SHOW PROCESSLIST it will wait for a table lock.

Suggested fix:
use table_alias_charset, as archive engine does or - better - use TABLE_SHARE::ha_data
[2 Dec 2009 10:31] Valeriy Kravchuk
Verified just as described using CSV example with latest 5.1.42 from bzr on Linux.
[11 Dec 2009 7:01] 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/93621

3260 V Narayanan	2009-12-11
      Bug#49329 example (and other) engines use wrong collation for open tables hash
      
      This fix changes the character set used within the
      IBMDB2I handler to hash table names to information
      about open tables. Previously, tables with names
      that differed only in letter case would hash to the
      same data structure. This caused incorrect behavior
      or errors when two such tables were in use simultaneously.
     @ mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_49329.result
        Bug#49329 example (and other) engines use wrong collation for open tables hash
        
        Result file for the test case.
     @ mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_49329.test
        Bug#49329 example (and other) engines use wrong collation for open tables hash
        
        Test case for the bug fix.
     @ storage/ibmdb2i/ha_ibmdb2i.cc
        Bug#49329 example (and other) engines use wrong collation for open tables hash
        
        change the character set used within the IBMDB2I
        handler to hash table names to information about
        open tables.
[11 Dec 2009 9:49] V Venkateswaran
The commit http://lists.mysql.com/commits/93621 fixes the issue for
the IBMDB2I engine only.
[19 Dec 2009 8:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:32] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091211164058-ycpe0f20d1c4h1gl) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:36] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[15 Jan 2010 8:58] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:joro@sun.com-20091211140719-1jfv6bt5v0xticng) (merge vers: 5.1.42) (pib:16)
[12 Mar 2010 14:15] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:31] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:47] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)