| 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: | |
| 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 | CPU Architecture: | Any |
[8 Oct 2008 13:59]
Andrii Nikitin
[8 Oct 2008 16:06]
Valeriy 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)
