Bug #39932 create table fails if column for FK is in different case than in corr index
Submitted: 8 Oct 2008 13:59 Modified: 7 Oct 2010 23:01
Reporter: Andrii Nikitin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:4.1, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: Magne Mæhre
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[8 Oct 2008 13:59] Andrii Nikitin
Description:
set foreign_key_checks=0;

CREATE TABLE `z` (`id` int, `pid` int, 
KEY `fk` (`pid`),
CONSTRAINT `fk` FOREIGN KEY (`PID`) REFERENCES `z`(`id`));

got
ERROR 1061 (42000): Duplicate key name 'fk'

How to repeat:
see description. if replace `PID` with `pid` no error shown

Suggested fix:
perform case insensitive check
[8 Oct 2008 16:06] Valerii Kravchuk
Thank you for a problem report. What exect OS and MySQL version, 5.0.x, you had used?
[9 Oct 2008 2:08] Andrii Nikitin
Reproduced on WinXP with

5.0.36-enterprise-gpl-nt-log
5.0.67-community-log
5.0.68-enterprise-gpl-nt
[9 Oct 2008 6:51] Sveta Smirnova
Thank you for the report.

Verified as described. If if replace KEY `fk` (`pid`) with KEY `fk` (`PID`) no error shown as well.
[19 Apr 2009 12:30] Konstantin Osipov
Dmitri, this should be fixed in 6.1-fk, could you please verify/add test case/close?
[20 Apr 2009 10:28] Dmitry Lenev
Unfortunately it has turned out that this bug also affects new foreign key implementation (i.e. I was able to reproduce it in --foreign-key-all-engines mode for server built from mysql-6.1-fk development tree).
[20 Apr 2009 11:18] Dmitry Lenev
Investigation shows that due to difference in case of field names used we don't detect properly that supporting index created for foreign key is redundant because of existing index, and this leads to an error as we try to derive name for supporting index from the constraint name.
[20 Apr 2009 17:55] 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/72524

2714 Dmitry Lenev	2009-04-20
      Fix for bug #39932 "create table fails if column for FK is in different
      case than in corr index".
       
      In both --foreign-key-all-engines=0 and 1 modes server was unable to 
      find existing or explicitly created supporting index for foreign key 
      if corresponding statement clause used field names in case different
      than one used in key specification and created yet another supporting
      index. In cases when name of constraint (and thus name of generated
      index) was the same as name of existing/explicitly created index this
      led to duplicate key name error.
      Also in --foreign-key-all-engines=1 mode in similar situation server
      was unable to find primary/unique key in the parent table.
      
      The problem was that unlike all other code Key_part_spec::operator==()
      compared field names in case sensitive fashion. As result routines
      responsible for getting rid of redundant generated supporting indexes
      for foreign key and for finding unique index in parent table were
      not working properly for versions of field names using different
      cases.
      
      This fix simply makes field name comparison in this operator case-
      insensitive, like it is in the rest of the server.
      
      QQ: Does it makes sense to fix this problem in some earlier version
          of server ?
     @ mysql-test/r/foreign_key_all_engines.result
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ mysql-test/t/foreign_key_all_engines.test
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ sql/sql_class.cc
        Make field name comparison case-insensitive like it is
        in the rest of server.
[28 Jul 2009 12:57] 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/79404

2832 Dmitry Lenev	2009-07-28
      Fix for bug #39932 "create table fails if column for FK is in different
      case than in corr index".
      
      Server was unable to find existing or explicitly created supporting
      index for foreign key if corresponding statement clause used field
      names in case different than one used in key specification and created
      yet another supporting index.
      In cases when name of constraint (and thus name of generated index)
      was the same as name of existing/explicitly created index this led
      to duplicate key name error.
      
      The problem was that unlike all other code Key_part_spec::operator==()
      compared field names in case sensitive fashion. As result routines
      responsible for getting rid of redundant generated supporting indexes
      for foreign key were not working properly for versions of field names
      using different cases.
      
      This fix simply makes field name comparison in this operator case-
      insensitive, like it is in the rest of the server.
     @ mysql-test/r/innodb_mysql.result
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ mysql-test/t/innodb_mysql.test
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ sql/sql_class.cc
        Make field name comparison case-insensitive like it is
        in the rest of server.
[28 Jul 2009 13:43] 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/79414

2756 Dmitry Lenev	2009-07-28
      Fix for bug #39932 "create table fails if column for FK is in different
      case than in corr index".
      
      In both --foreign-key-all-engines=0 and 1 modes server was unable to
      find existing or explicitly created supporting index for foreign key
      if corresponding statement clause used field names in case different
      than one used in key specification and created yet another supporting
      index. In cases when name of constraint (and thus name of generated
      index) was the same as name of existing/explicitly created index this
      led to duplicate key name error.
      Also in --foreign-key-all-engines=1 mode in similar situation server
      was unable to find primary/unique key in the parent table.
      
      The problem was that unlike all other code Key_part_spec::operator==()
      compared field names in case sensitive fashion. As result routines
      responsible for getting rid of redundant generated supporting indexes
      for foreign key and for finding unique index in parent table were
      not working properly for versions of field names using different
      cases.
      
      This fix simply makes field name comparison in this operator case-
      insensitive, like it is in the rest of the server.
     @ mysql-test/r/foreign_key_all_engines.result
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ mysql-test/r/innodb_mysql.result
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ mysql-test/t/foreign_key_all_engines.test
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ mysql-test/t/innodb_mysql.test
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ sql/sql_class.cc
        Make field name comparison case-insensitive like it is
        in the rest of server.
[28 Jul 2009 13:46] 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/79415

2757 Dmitry Lenev	2009-07-28 [merge]
      Merged changes from mysql-6.1-fk-stage tree with patch for bug bug #39932
      "create table fails if column for FK is in different case than in corr index".
[7 Oct 2009 22:01] Jimmy Yang
Please note this issue also manifests in case described in bug #45307, and the fix in sql_class.cc also fixes the repro case in #45307, which has been closed as dup of this CR.

Thanks
Jimmy
[9 Oct 2009 15:19] 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/86403

2900 Dmitry Lenev	2009-10-09
      Fix for bug #39932 "create table fails if column for FK is in different
      case than in corr index".
      
      Server was unable to find existing or explicitly created supporting
      index for foreign key if corresponding statement clause used field
      names in case different than one used in key specification and created
      yet another supporting index.
      In cases when name of constraint (and thus name of generated index)
      was the same as name of existing/explicitly created index this led
      to duplicate key name error.
      
      The problem was that unlike all other code Key_part_spec::operator==()
      compared field names in case sensitive fashion. As result routines
      responsible for getting rid of redundant generated supporting indexes
      for foreign key were not working properly for versions of field names
      using different cases.
     @ mysql-test/r/innodb_mysql.result
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ mysql-test/t/innodb_mysql.test
        Added test case for bug #39932 "create table fails if column for FK
        is in different case than in corr index".
     @ sql/sql_class.cc
        Make field name comparison case-insensitive like it is
        in the rest of server.
[9 Oct 2009 15: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/86416

3633 Dmitry Lenev	2009-10-09 [merge]
      Merge of corrected version of fix for bug #39932 "create table
      fails if column for FK is in different case than in corr index"
      into mysql-6.0-codebase-runtime tree.
[9 Oct 2009 15:48] 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/86417

3633 Dmitry Lenev	2009-10-09 [merge]
      Merge of corrected version of fix for bug #39932 "create table
      fails if column for FK is in different case than in corr index"
      into mysql-6.0-codebase-runtime tree.
[9 Oct 2009 15:51] Dmitry Lenev
Fix for this bug was pushed into mysql-next-mr-runtime tree.
It is going to appear in 5.5.0 version.
[12 Oct 2009 15:14] Paul Dubois
Noted in 5.5.0 changelog.
[3 Nov 2009 7:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:dlenev@mysql.com-20091009154725-epv2f6jb3519wz4x) (merge vers: 6.0.14-alpha) (pib:13)
[3 Nov 2009 17:56] Paul Dubois
Noted in 6.0.14 changelog.
[8 Nov 2009 21:43] Roel Van de Paar
Summary: bug fix will be included in 5.5.0 and 6.0.14

Changelog entry:

CREATE TABLE failed if a column name in a FOREIGN KEY clause was
given in a lettercase different from the corresponding index
definition.
[12 Nov 2009 8:18] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)
[29 Aug 2010 12:22] Magne Mæhre
Started backporting to 5.1
[29 Aug 2010 12:53] 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/117078

3494 Magne Mahre	2010-08-29
      Bug#39932 "create table fails if column for FK is in different
                case than in corr index".
            
      Server was unable to find existing or explicitly created supporting
      index for foreign key if corresponding statement clause used field
      names in case different than one used in key specification and created
      yet another supporting index.
      In cases when name of constraint (and thus name of generated index)
      was the same as name of existing/explicitly created index this led
      to duplicate key name error.
            
      The problem was that unlike all other code Key_part_spec::operator==()
      compared field names in case sensitive fashion. As result routines
      responsible for getting rid of redundant generated supporting indexes
      for foreign key were not working properly for versions of field names
      using different cases.
      
      (backported from mysql-trunk)
     @ sql/sql_class.cc
        Make field name comparison case-insensitive like it is
        in the rest of server.
[1 Sep 2010 17:40] 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/117363

3498 Magne Mahre	2010-09-01
      Bug#39932 "create table fails if column for FK is in different
                case than in corr index".
            
      Server was unable to find existing or explicitly created supporting
      index for foreign key if corresponding statement clause used field
      names in case different than one used in key specification and created
      yet another supporting index.
      In cases when name of constraint (and thus name of generated index)
      was the same as name of existing/explicitly created index this led
      to duplicate key name error.
            
      The problem was that unlike all other code Key_part_spec::operator==()
      compared field names in case sensitive fashion. As result routines
      responsible for getting rid of redundant generated supporting indexes
      for foreign key were not working properly for versions of field names
      using different cases.
      
      (backported from mysql-trunk)
     @ sql/sql_class.cc
        Make field name comparison case-insensitive like it is
        in the rest of server.
[28 Sep 2010 15:39] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)
[28 Sep 2010 15:41] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)
[28 Sep 2010 15:44] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)
[1 Oct 2010 17:48] Paul Dubois
Noted in 5.5.7, 5.6.1 changelogs.

CREATE TABLE failed if a column referred to in an index defintion and
foreign key definition was in different lettercases in the two
definitions. 

Setting report to Need Merge pending push to 5.1.x.
[3 Oct 2010 1:24] Paul Dubois
Setting report to Need Merge pending push to 5.1.x.
[7 Oct 2010 23:01] Paul Dubois
Noted in 5.1.52 changelog.
[1 Nov 2010 19:00] Bugs System
Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)