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:
None 
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

[12 Jul 2009 19:34] Peter Gulutzan
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;
[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.