| Bug #46136 | Foreign keys: adding primary keys causes dangling reference | ||
|---|---|---|---|
| Submitted: | 12 Jul 2009 19:34 | Modified: | 28 Jul 2009 19:33 |
| 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:41]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[14 Jul 2009 12: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/78643 2741 Dmitry Lenev 2009-07-14 Fix for bug #46136 "Foreign keys: adding primary keys causes dangling reference". In --foreign-key-all-engines=1 mode one was allowed to ADD PRIMARY KEY on columns which participated as children in a foreign key. Adding a primary key changed this columns to be NOT NULL and replaced NULL values in them with type's default value possibly creating dangling references. This fix tries to solve this problem by simply prohibiting addition of primary key on columns which participate in a foreign key as children. Such limitation is in line with already existing restriction which says that altering attributes of any columns participating in a foreign key is disallowed. @ mysql-test/r/foreign_key_all_engines.result Added test for bug #46136 "Foreign keys: adding primary keys causes dangling reference". @ mysql-test/t/foreign_key_all_engines.test Added test for bug #46136 "Foreign keys: adding primary keys causes dangling reference". @ sql/sql_table.cc Prohibiting addition of primary key on columns which participate in a foreign key as children, since this can lead to creation of dangling references.
[28 Jul 2009 19:32]
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/79456 2760 Dmitry Lenev 2009-07-28 Fix for bug #46136 "Foreign keys: adding primary keys causes dangling reference". In --foreign-key-all-engines=1 mode one was allowed to ADD PRIMARY KEY on columns which participated as children in a foreign key. Adding a primary key changed this columns to be NOT NULL and replaced NULL values in them with type's default value possibly creating dangling references. This fix tries to solve this problem by simply prohibiting addition of primary key on nullable columns which participate in a foreign key as children. Such limitation is in line with already existing restriction which says that altering attributes of any columns participating in a foreign key is disallowed. @ mysql-test/r/foreign_key_all_engines.result Added test for bug #46136 "Foreign keys: adding primary keys causes dangling reference". @ mysql-test/t/foreign_key_all_engines.test Added test for bug #46136 "Foreign keys: adding primary keys causes dangling reference". @ sql/share/errmsg.txt Adjusted error message to make it usable in new scenario. @ sql/sql_table.cc Prohibiting addition of primary key on nullable columns which participate in a foreign key as children, since this can lead to creation of dangling references.
[28 Jul 2009 19:33]
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 report.
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 add a primary key to the child table. (This has the effect of changing NULLs to ''s.) Now there is a row in the child which is not in the parent. How to repeat: drop database d; create database d; use d create table t1 (s1 varchar(5) primary key) engine=innodb partition by key(s1); create table t2 (s1 varchar(5) references t1 (s1)) engine=innodb; insert into t1 values ('a'),('b'),('c'); insert into t2 values ('a'),('b'),('c'),(null); alter table t2 add column s2 varchar(5) first; alter table t2 add column (s3 varchar(5) references t1(s1)); alter table t2 add index i (s1 desc); alter table t2 add primary key (s1); /* changes NULL to ''! */ select * from t1; select * from t2;