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

[6 Sep 2005 6:04] Yair dfwerfw
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:
?
[8 Sep 2005 22:15] MySQL Verification Team
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.
[11 Sep 2005 15:03] Yair dfwerfw
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 ?
[11 Sep 2005 15:19] Yair dfwerfw
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
[19 Sep 2005 10:40] Valeriy Kravchuk
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.
[30 Sep 2005 15:43] MySQL Verification Team
Testcase for losing InnoDB table comments on 4.0.20a-nt

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

[30 Sep 2005 16:24] MySQL Verification Team
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.
[30 Sep 2005 16:42] Heikki Tuuri
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
[30 Sep 2005 20:11] Yair dfwerfw
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...
[30 Sep 2005 20:18] Yair dfwerfw
Please note problem was observed at some of our Linux customer deployments as well, although so far was not reproduced in our Linux lab
[3 Oct 2005 13:58] Bugs System
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
[3 Oct 2005 14:06] Bugs System
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
[3 Oct 2005 14:33] Bugs System
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
[3 Oct 2005 14:41] Bugs System
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
[6 Oct 2005 3:08] Marko Mäkelä
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.
[6 Oct 2005 14:55] Paul DuBois
Noted in 4.0.27, 4.1.15, 5.0.15 changelogs.
[18 Oct 2005 12:43] Martijn van den Burg
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.
[15 May 2006 20:24] Kevin Switzer
Does anyone know if this bug affects mysqldump as well?  Also, are there any reports that this bug was observed on Solaris?
[17 May 2006 8:18] Marko Mäkelä
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).