| Bug #46138 | Foreign keys: unjustified error when dropping primary key | ||
|---|---|---|---|
| Submitted: | 12 Jul 2009 19:43 | Modified: | 28 Jul 2009 16:40 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 6.1.0-alpha-debug-log | OS: | Linux (SUSE 11.1 64-bit) |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[13 Jul 2009 11:32]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[27 Jul 2009 10:03]
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/79319 2751 Dmitry Lenev 2009-07-27 Fix for bug #46138 "Foreign keys: unjustified error when dropping primary key". In --foreign-key-all-engines=1 mode it was impossible to drop PRIMARY or UNIQUE key which also served as supporting key for some foreign key. The problem was that we disallowed dropping of key which served as supporting for some foreign key (if there were no other suitable keys). Indeed, this also meant that users were unable to get rid of PRIMARY or UNIQUE constraints which were implemented by keys which also served as supporting keys for some foreign key. This patch solves the problem by ensuring that in cases when supporting PRIMARY or UNIQUE key is dropped a non-unique supporting key is created automatically as replacement. @ mysql-test/t/foreign_key_all_engines.test Added test for bug #46138 "Foreign keys: unjustified error when dropping primary key". @ sql/fk_dd.cc Introduced fk_is_supporting_key() function which allows to check if particular can serve as supporting for the foreign key. @ sql/fk_dd.h Introduced fk_is_supporting_key() function which allows to check if particular can serve as supporting for the foreign key. @ sql/sql_table.cc Allow dropping of PRIMARY/UNIQUE keys which serve as supporting key for some foreign key. As a replacement automatically create non-unique supporting key.
[28 Jul 2009 16:37]
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/79435 2759 Dmitry Lenev 2009-07-28 Fix for bug #46138 "Foreign keys: unjustified error when dropping primary key". In --foreign-key-all-engines=1 mode it was impossible to drop PRIMARY or UNIQUE key which also served as supporting key for some foreign key. The problem was that we disallowed dropping of key which served as supporting for some foreign key (if there were no other suitable keys). Indeed, this also meant that users were unable to get rid of PRIMARY or UNIQUE constraints which were implemented by keys which also served as supporting keys for some foreign key. This patch solves the problem by ensuring that in cases when supporting PRIMARY or UNIQUE key is dropped a non-unique supporting key is created automatically as replacement. @ mysql-test/t/foreign_key_all_engines.test Added test for bug #46138 "Foreign keys: unjustified error when dropping primary key". @ sql/fk_dd.cc Introduced fk_is_supporting_key() functions which allows to check if particular can serve as supporting for the foreign key. @ sql/fk_dd.h Introduced fk_is_supporting_key() functions which allow to check if particular can serve as supporting for the foreign key. @ sql/share/errmsg.txt Added warning to be emitted when we automatically create non-unique supporting key as a replacement for PRIMARY/UNIQUE key being dropped. @ sql/sql_table.cc Allow dropping of PRIMARY/UNIQUE keys which serve as supporting key for some foreign key. As a replacement automatically create non-unique supporting key.
[28 Jul 2009 16:40]
Dmitry Lenev
Fix for this bug was pushed into mysql-6.1-fk-stage tree. Since this issue was reported against tree which is not publicly available yet I am simply closing this bug.
Description: I'm using mysql-6.1-fk-stage. I start the server with --foreign-key-all-engines=1. I create a parent table and a child table. I try to remove a primary key of the child table. I get a message that there is a foreign key reference. But the foreign key doesn't reference what I'm trying to drop. How to repeat: drop database d; create database d; use d create table t1 (s1 varchar(5) primary key) engine=innodb; create table t2 (s1 varchar(5), foreign key (s1) references t1 (s1), primary key (s1)) engine=innodb; alter table t2 drop primary key;