Bug #24741 existing cascade clauses disappear when adding foreign keys
Submitted: 1 Dec 2006 3:19 Modified: 18 Jun 2010 23:09
Reporter: John Smrekar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1.14-BK, 5.1.12-beta OS:Linux (Linux)
Assigned to: Marko Mäkelä CPU Architecture:Any

[1 Dec 2006 3:19] John Smrekar
Description:
New Innodb tables can be created with multiple cascading foreign key clauses, but when adding a new foreign key with an ALTER statement, the new foreign key has the cascade clause as expected but all other foreign keys lose their cascade clause.  

This bug only appears to be present in 5.1.12.  When tested against 5.1.11, correct behavior was observed.

How to repeat:
CREATE TABLE `parent` (
  `parent_id` int(5) NOT NULL,
  `myfield` int(1) NOT NULL,
  PRIMARY KEY (`parent_id`),
  KEY `myfield` (`myfield`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `child` (
  `child_id` int(5) NOT NULL,
  `parent_id` int(5) NOT NULL,
  `myfield` int(1) NOT NULL,
  PRIMARY KEY (`child_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

alter table child add FOREIGN KEY (`myfield`) REFERENCES `parent` (`myfield`) ON DELETE CASCADE ON UPDATE CASCADE;

the result with the missing cascade clause:

CREATE TABLE `child` (
  `child_id` int(5) NOT NULL,
  `parent_id` int(5) NOT NULL,
  `myfield` int(1) NOT NULL,
  PRIMARY KEY (`child_id`),
  KEY `parent_id` (`parent_id`),
  KEY `myfield` (`myfield`),
  CONSTRAINT `child_ibfk_2` FOREIGN KEY (`myfield`) REFERENCES `parent` (`myfield`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Suggested fix:
make the cascade clauses work properly when added by an alter table statement
[1 Dec 2006 14:44] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.1.14-BK on Linux:

openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.14-beta Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `parent` (
    ->   `parent_id` int(5) NOT NULL,
    ->   `myfield` int(1) NOT NULL,
    ->   PRIMARY KEY (`parent_id`),
    ->   KEY `myfield` (`myfield`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `child` (
    ->   `child_id` int(5) NOT NULL,
    ->   `parent_id` int(5) NOT NULL,
    ->   `myfield` int(1) NOT NULL,
    ->   PRIMARY KEY (`child_id`),
    ->   KEY `parent_id` (`parent_id`),
    ->   CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent
`
    -> (`parent_id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table child add FOREIGN KEY (`myfield`) REFERENCES `parent` (`myfi
eld`) ON
    -> DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `child_id` int(5) NOT NULL,
  `parent_id` int(5) NOT NULL,
  `myfield` int(1) NOT NULL,
  PRIMARY KEY (`child_id`),
  KEY `parent_id` (`parent_id`),
  KEY `myfield` (`myfield`),
  CONSTRAINT `child_ibfk_2` FOREIGN KEY (`myfield`) REFERENCES `parent` (`myfiel
d`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`pare
nt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
[14 Dec 2006 9:49] Bogdan Enache
I have the same bug on Windows, MySQL server 5.0.27.

All On Delete Cascade and On Update Cascade changed overnight to Restrict, without anyone noticing, in over 18 tables. I don't know how many of those tables got their structure changed by programmers lately. But an upgrade was made to 5.0.27  3 days ago.
[14 Dec 2006 10:42] Bogdan Enache
After digging deeper in the database, I saw the bug is in MySQL Admin for Windows. Ignore my previous comment.
[2 Jan 2007 13:09] Heikki Tuuri
http://bugs.mysql.com/bug.php?id=25356 is probably a duplicate of this.
[8 Jan 2007 15:50] Heikki Tuuri
The bug was introduced in 5.1.12 when we saved space in the foreign struct:
http://bugs.mysql.com/bug.php?id=20877
n_fields is only 10 bits, though we used bits 24 - 31 in it in dict0load.c to store foreign->type.

Ultimately, the bug was due to bad documentation in dict0mem.h and bad coding style in InnoDB code written in 2002. We should use bits 24 - 31 ONLY in the disk format of the 32-bit variable, and not misuse the memory struct field foreign->n_fields for storing also foreign->type for a while!

Fix: in, dict0load.c, do:

ulint n_fields_and_type;
...

n_fields_and_type = mach_read_from_4(
                rec_get_nth_field_old(rec, 5, &len));
ut_a(len == 4);
foreign->n_fields = n_fields_and_type & 0x3FF;
foreign->type = n_fields_and_type >> 24;

dict0mem.h in 5.1:
struct dict_foreign_struct{
        mem_heap_t*     heap;           /* this object is allocated from
                                        this memory heap */
        char*           id;             /* id of the constraint as a
                                        null-terminated string */
        unsigned        n_fields:10;    /* number of indexes' first fields
                                        for which the the foreign key
                                        constraint is defined: we allow the
                                        indexes to contain more fields than
                                        mentioned in the constraint, as long
                                        as the first fields are as mentioned */
        unsigned        type:6;         /* 0 or DICT_FOREIGN_ON_DELETE_CASCADE
                                        or DICT_FOREIGN_ON_DELETE_SET_NULL */

dict0load.c in 5.1:

        foreign = dict_mem_foreign_create();

        foreign->n_fields = mach_read_from_4(
                rec_get_nth_field_old(rec, 5, &len));

        ut_a(len == 4);

        /* We store the type to the bits 24-31 of n_fields */

        foreign->type = foreign->n_fields >> 24;
        foreign->n_fields = foreign->n_fields & 0xFFFFFFUL;
[8 Jan 2007 15:52] Heikki Tuuri
This bug also causes MySQL/InnoDB-5.1.12 to forget the CASCADE clauses of any table it loads from the InnoDB data dictionary at a startup.

Asssigning this bug to Marko.
[9 Jan 2007 7:41] Marko Mäkelä
I just committed Heikki's suggested fix as well as a test case to our private InnoDB repository. After Heikki's approval, the patch will be sent to MySQL.
[10 Jan 2007 13:28] Heikki Tuuri
Approved by Marko and Heikki.
[16 Jan 2007 22:25] [ name withheld ]
I have the same problem on 5.0.27.
On update cascade is ok, but on delete turns to "restrict". How can I solve it?
[17 Jan 2007 9:15] Marko Mäkelä
Bug #20877, which caused this bug, was only fixed in 5.1.

If there is a similar bug in 5.0, we would like to have a repeatable test case for it. Please note an earlier comment to this bug report from December 14, 2006, saying that there was a bug in MySQL Admin for Windows.
[17 Jan 2007 9:16] Marko Mäkelä
Sorry, I meant to say: Bug #20877, whose fix caused this bug, was only fixed in 5.1.
[17 Jan 2007 12:48] [ name withheld ]
Thanks for your answer.
I have seen this comment, and I have the link you gave me but I didn't understood what I have to do. (Sorry for my ignorance, I'm quite new). I installed 5.1.14, so what I have to do now? Remove mySQL Admin?
[17 Jan 2007 13:02] Marko Mäkelä
If you can trigger the bug in 5.0 from the command-line client (not MySQL Admin), then please send the test case. Bug #24741 should not exist in MySQL 5.0 or earlier.

If the bug happens only in connection with MySQL Admin, then you should report the bug in MySQL Admin. Sadly, Bogdan Enache did not mention if the bug in MySQL Admin was reported or fixed. I do not use MySQL Admin.
[18 Jan 2007 10:58] Bogdan Enache
The bug in Mysql Administrator is fixed.
[18 Jan 2007 13:20] [ name withheld ]
In which version? I have 1.2.6 and I still have this problem.
[20 Jan 2007 0:24] Bogdan Enache
Bug solved in MySQL Administrator 1.28 for Windows, the one bundled in the mysql-gui-tools-5.0-r8-win32.
[29 Jan 2007 9:55] Sveta Smirnova
Bug #25899 can be correlated with this one
[5 Feb 2007 13:26] Sveta Smirnova
Bug #26083 was marked as duplicate of this one
[14 Feb 2007 15:13] Chad MILLER
Available in 5.1.16-beta.
[20 Feb 2007 20:23] Paul Dubois
Noted in 5.1.16 changelog.

ALTER TABLE caused loss of CASCADE clauses for InnoDB tables.
[5 May 2010 15:18] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:31] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:57] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:26] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:54] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 15:23] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:59] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:39] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:25] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)