MySQL Bugs: #13002: Constraint loss

Bug #13002	Constraint loss
Submitted:	6 Sep 2005 6:04	Modified:	6 Oct 2005 14:55
Reporter:	Yair dfwerfw	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.20a-nt	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
After several days of uptime (14 to 17 days in cases we've seen) some or all tables lose their constraints.
 
Loss is sudden and seems unrelated to any particular database activity.

When loss occurs, results of SHOW CREATE TABLE and SHOW TABLE STATUS indicate no constraints.

More significantly, cascade deletes are no longer being invoked.

A restart of the server brings lost constraints back to life.

Problem has been observed on both windows & linux.

How to repeat:
Leave an idle server running for a long time, with a query of SHOW TABLE STATUS FROM every hour. At some point after 14 to 17 days, table constraints are lost.

Suggested fix:
?

Could you please provide the table schema and if is necessary for a valid
test with a dump file for insert data. I would like to avoid to left a machine
for a long time with my own database schema and for this reason to get an invalid
test.

Thanks in advance.

Thank you for your response. I can send you the output of mysqldump that includes our schema & demo data. I will also send you our my.ini file.
Is there an email address I can send it to as zip file ?

Unfortunately I cannot get authorization to send schema nor data. Please let me know if there's any other way I can help. I will send you our my.ini file

Thank you for the bug report. Sorry, but you "how to repeat" description makes it impoossible to verify this bug really, so I have to ask several questions.

1. What are the exact symptoms of the reported bug? 

2. Is the foreign key information missing from SHOW CREATE TABLE output? 

3. Does InnoDB allow operations that would violate the foreign key constraint?

4. Have you tried to use newer version (4.0.26)? Your 4.0.20a is a bit dated...
If no, please, try to repeat the described behaviour on 4.0.26.

Please, send the results of 'df -k' command on Linux when constrains are getting lost. Your my.cnf file content would be useful too.

Testcase for losing InnoDB table comments on 4.0.20a-nt

Attachment: bug13002.zip (application/zip, text), 5.26 KiB.

Unzip and run the SQL attached in bug13002.zip.

Verified to cause dissappearance of table comments on 4.0.20a-nt.
Test case ran fine on using mysql-standard-4.0.20-pc-linux-i686.tar.gz.
Test case ran fine on 4.1.14-nt.
Test case ran fine on 4.0.24-nt.
Test case ran fine on 4.0.26-nt.

The tmpfile() function on Windows is rather limited, so newer versions of InnoDB have code to work around those problems.

Shane,

does the bug in 4.0.20a mean that FOREIGN KEY constraints are NOT forced, or does it just mean that the printout is missing from the comment in SHOW TABLE STATUS?

Regards,

Heikki

We managed to reproduce in our lab with 4.0.20a.
The constraints remain in force and are invoked correctly, only the output of SHOW CREATE TABLE and the comment of SHOW TABLE STATUS lost the constraints data at some point. A restart resolves the printout back to normal.
Of course, if one drops the table and relies on a defective SHOW CREATE TABLE to recreate the table as we did, the constraints are gone for good...

Please note problem was observed at some of our Linux customer deployments as well, although so far was not reproduced in our Linux lab

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/internals/30633

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/internals/30635

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/internals/30641

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/internals/30642

The bug was that SHOW CREATE TABLE would not display any FOREIGN KEY clauses if a temporary file could not be created. After the fix, SHOW CREATE TABLE will display an error message in an SQL comment. The fix will be in 4.0.27, 4.1.15, and 5.0.15.

Noted in 4.0.27, 4.1.15, 5.0.15 changelogs.

I would really like to know what could cause the error ('unable to create temporary file') in the first place.

Has it to do with TMPDIR? Because we have enough of that available.

Does anyone know if this bug affects mysqldump as well?  Also, are there any reports that this bug was observed on Solaris?

InnoDB uses the MySQL function create_temp_file() only for diagnostic messages (SHOW CREATE TABLE, SHOW TABLE STATUS, SHOW INNODB STATUS, last foreign key error, etc.) It is not fatal if a temporary file cannot be created after InnoDB has started.

As far as I know, the MySQL function create_temp_file() does not obey the temporary variable TMPDIR. Instead, it is controlled by the MySQL configuration variable tmpdir (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html).