MySQL Bugs: #10449: Foreign key On Delete directives disappear

Bug #10449	Foreign key On Delete directives disappear
Submitted:	8 May 2005 15:31	Modified:	16 Apr 2006 13:10
Reporter:	Yair dfwerfw	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.20a	OS:	Windows (Windows 2003 5.2, x86)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
We have a customer log showing the disappearance of On Delete directives from some table definitions.
As far as we can tell, there was no meaningful db activity at the time these directives disappeared.
A log is attached, showing table status before and after the disappearance. See for example the table sbv_services

How to repeat:
Do not know

Suggested fix:
Do not know

Hi!

Did you upgrade mysqld in between?

Does SHOW CREATE TABLE still show the correct definition for sbv_services?

Regards,

Heikki

There was definitely no change to mysqld.
The db was mostly idle (except for a few periodic select queries) during the one hour span in which the change in SHOW TABLE STATUS occurred.
MySQL's error file is clean.

I'm afraid I don't have the result of SHOW CREATE TABLE because the customer's database has been recreated

The problem was detected because our application misbehaved in a way that was clearly due to cascade deletes on these tables not working.
Since we log the result of SHOW TABLE STATUS every hour, I searched back in our logs and indeed found out a moment in which a change had occurred in its output.

We use MySQL extensively and have never seen this happen before.
If you'd like us to periodically log anything else in addition to
SHOW CREATE TABLE we'll be most happy to oblige.

Hi!

I am putting this to the 'Can't repeat' status.

If you can repeat, please add a new comment to this bug report.

--Heikki

This still happens. I have 2 more documented cases such as the above. After uptime of ~14 days the constraints are suddenly and inexplicably lost.  This is a very severe issue which causes data corruption, as cascade deletes are not being invoked from that point on. A restart of the db server brings things back to normal. Please see another bug report http://bugs.mysql.com/bug.php?id=12299

I think Marko fixed a bug last fall that could cause this symptom. Is the bug still present in 5.0.19?

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".