Bug #27165 Existing cascade clauses disappear over the night
Submitted: 15 Mar 2007 8:20 Modified: 19 Mar 2007 21:12
Reporter: Kamal Joshi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:MySQL 5.1.15- beta OS:Linux (Linux Redhat )
Assigned to: Heikki Tuuri CPU Architecture:Any
Tags: regression

[15 Mar 2007 8:20] Kamal Joshi
Description:
DB Details

-- Start ---
Two MySQL database servers doubly sync in cluster
About 140 tables in database
All tables are of type innodb
DB size is more than 30 GB
-- End ---

Added the foreign key constraint with "on delete cascade on update cascade" clause in many innodb type tables, it was added successfully and checked after some time it was there in both servers.

But when checked again next day it was disappeared. This happened many times.

Please suggest......

How to repeat:
CREATE TABLE `user_group` (
  `group_id` int(10) unsigned NOT NULL auto_increment,
  `school_id` int(10) unsigned NOT NULL,
  `member_id` int(10) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  `type` varchar(10) NOT NULL,
  `cdate` timestamp NULL default CURRENT_TIMESTAMP,
  `active` int(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (`group_id`),
  KEY `school_id` (`school_id`),
  KEY `member_id` (`member_id`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Adding the constraint

ALTER TABLE `user_group`
  ADD CONSTRAINT `user_group_ibfk_3` FOREIGN KEY (`school_id`) REFERENCES `school` (`school_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `user_group_ibfk_4` FOREIGN KEY (`member_id`) REFERENCES `member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE;

This was disappeared next day.
[15 Mar 2007 8:30] Valeriy Kravchuk
Thank you for a problem report. How you checked that they disappeared? Take a look at Bug #27140 also.
[15 Mar 2007 10:30] Kamal Joshi
I have checked this three ways

1. Using the SHOW CREATE TABLE syntax
2. Using the PHPMYADMIN GUI Interface
3. This can also be verified, while exception handler through an exception in code regarding this

Cannot delete or update a parent row: a foreign key constraint fails (#databasename#.#tablename#, CONSTRAINT `subject_mapping_ibfk_5` FOREIGN KEY (`mis_subject_id`) REFERENCES `mis_subject` (`mis_subject_id`))

If I drop the constraint and add it again using the "alter table Add constraint", it was then showing using the SHOW CREATE or PHPMYADMIN interface and in code also it works fine. But same problem arose next day.

Important:
This problem was not in MySQL 5.0.19- Standard version. We recently upgrade our MySQL to 5.1.15 - beta, after that this problem encountered.
[15 Mar 2007 11:53] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.16, and inform about the results.
[15 Mar 2007 17:19] Heikki Tuuri
This is probably a duplicate of http://bugs.mysql.com/bug.php?id=24741
[19 Mar 2007 21:12] Kamal Joshi
Thanks to every one!
We have now upgraded our MySQL database to 5.1.16 and it's working fine now.