MySQL Bugs: #12299: show create tables lost referense information accidentally

Bug #12299	show create tables lost referense information accidentally
Submitted:	1 Aug 2005 15:39	Modified:	2 Sep 2005 7:54
Reporter:	yue gong	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.20	OS:	Windows (win2000)
Assigned to:		CPU Architecture:	Any

Description:
the correct sql statement is :

CREATE TABLE `ge_usergroupmembers` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `employee` int(10) unsigned default NULL,
 `groupMember` varchar(128) default NULL,
 `userGroup` varchar(255) NOT NULL default '',
 `start` datetime NOT NULL default '0000-00-00 00:00:00',
 `end` datetime NOT NULL default '0000-00-00 00:00:00',
 `role` varchar(127) NOT NULL default '',
 `remark` varchar(255) NOT NULL default '',
 `parse` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `groupMember_2` (`groupMember`,`userGroup`,`start`,`parse`),
 UNIQUE KEY `employee_2` (`employee`,`userGroup`,`start`,`parse`),
 KEY `parse` (`parse`),
 KEY `userGroup` (`userGroup`),
 CONSTRAINT `ge_usergroupmembers_ibfk_7` FOREIGN KEY (`employee`) REFERENCES `employee` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `ge_usergroupmembers_ibfk_8` FOREIGN KEY (`groupMember`) REFERENCES `ge_usergroups` (`name`) ON UPDATE CASCADE,
 CONSTRAINT `ge_usergroupmembers_ibfk_9` FOREIGN KEY (`userGroup`) REFERENCES `ge_usergroups` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB
;

but when the server runs for few days, it report :
CREATE TABLE `ge_usergroupmembers` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `employee` int(10) unsigned default NULL,
 `groupMember` varchar(128) default NULL,
 `userGroup` varchar(255) NOT NULL default '',
 `start` datetime NOT NULL default '0000-00-00 00:00:00',
 `end` datetime NOT NULL default '0000-00-00 00:00:00',
 `role` varchar(127) NOT NULL default '',
 `remark` varchar(255) NOT NULL default '',
 `parse` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `groupMember_2` (`groupMember`,`userGroup`,`start`,`parse`),
 UNIQUE KEY `employee_2` (`employee`,`userGroup`,`start`,`parse`),
 KEY `parse` (`parse`),
 KEY `userGroup` (`userGroup`),
 ) TYPE=InnoDB
;

the CONSTRAINT clauses are lost , it must be restarted. 

How to repeat:
don't know,

Could you please write the definitions of tables - employee and ge_usergroups ?
Did you try to use 4.1.* version of mysql ?

We have the same problem with same version 4.0.20a

After running for a long time, SHOW TABLE STATUS suddenly loses the constraints on all tables. 
This is a critical problem, not just a display issue, as cascade deletes are not being invoked from that point on. 

We don't know how to reproduce, but it happens every few days to any one of our customers that running MySQL.

See my bug
 http://bugs.mysql.com/bug.php?id=10449

One more strange thing, on the 3 separate occasions for which I have documentation of constraint loss, the problem showed up after ~14 days of uptime

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".