MySQL Bugs: #197: table corruption

Bug #197	table corruption
Submitted:	27 Mar 2003 0:39	Modified:	18 Jul 2003 6:43
Reporter:	[ name withheld ]	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.0.10-4.0.12	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The Mysql-Server (installed from you RPMs) corrupts tables. It seems like this 
happens due to an error in the stop-routine. 
 
This is what i can see in the mysql-error log: 
 
030323 17:24:39  /usr/sbin/mysqld: Forcing close of thread 999  user: 'db344047' 
 
030323 17:24:39  /usr/sbin/mysqld: Forcing close of thread 998  user: 'db344047' 
 
030323 17:24:39  /usr/sbin/mysqld: Forcing close of thread 797  user: 'db344047' 
 
030323 17:24:42  /usr/sbin/mysqld: Shutdown Complete 
 
030323 17:24:42  mysqld ended 
 
030323 17:24:47  mysqld started 
/usr/sbin/mysqld: ready for connections 
030323 17:25:14  /usr/sbin/mysqld: Normal shutdown 
 
030323 17:25:15  /usr/sbin/mysqld: Forcing close of thread 42  user: 'db344047' 
 
030323 17:25:15  /usr/sbin/mysqld: Forcing close of thread 35  user: 'db344047' 
 
030323 17:25:15  /usr/sbin/mysqld: Forcing close of thread 34  user: 'db344047' 
 
030323 17:25:15  /usr/sbin/mysqld: Forcing close of thread 25  user: 'db344047' 
 
030323 17:25:15  /usr/sbin/mysqld: Forcing close of thread 21  user: 'db344047' 
 
030323 17:25:15  /usr/sbin/mysqld: Forcing close of thread 20  user: 'db344047' 
 
030323 17:25:15  /usr/sbin/mysqld: Forcing close of thread 19  user: 'db344047' 
 
030323 17:25:15  /usr/sbin/mysqld: Forcing close of thread 11  user: 'db344047' 
 
030323 17:25:15  /usr/sbin/mysqld: Forcing close of thread 5  user: 'db344047' 
 
030323 17:25:15  /usr/sbin/mysqld: Shutdown Complete 
 
030323 17:25:15  mysqld ended 
 
and after this the tables are corrupted and myisamchk puts out the following: 
 
Checking MyISAM file: nuke_priv_msgs.MYI 
Data records:    5655   Deleted blocks:      22 
- check file-size 
- check key delete-chain 
- check record delete-chain 
myisamchk: warning: Not used space is supposed to be: 1668 but is: 1548 
myisamchk: error: record delete-link-chain corrupted 
- check index reference 
- check data record references index: 1 
- check data record references index: 2 
- check data record references index: 3 
- check record links 
myisamchk: error: Record-count is not ok; is 5654         Should be: 5655 
myisamchk: warning: Found 749876 record-data and 22696 unused data and 1688 
deleted-data 
myisamchk: warning: Total 774260, Should be: 774400 
myisamchk: warning: Found         23 deleted blocks       Should be: 22 
myisamchk: warning: Found       7275 parts                Should be: 7277 parts 
MyISAM-table 'nuke_priv_msgs.MYI' is corrupted 
Fix it using switch "-r" or "-o" 
 
--------- 
 
Checking MyISAM file: nuke_whoiswhere.MYI 
Data records:      40   Deleted blocks:      42 
myisamchk: warning: Table is marked as crashed 
- check file-size 
- check key delete-chain 
- check record delete-chain 
myisamchk: error: record delete-link-chain corrupted 
- check index reference 
- check record links 
myisamchk: warning: Found         41 deleted blocks       Should be: 42 
myisamchk: warning: Found        119 parts                Should be: 120 parts 
MyISAM-table 'nuke_whoiswhere.MYI' is corrupted 
Fix it using switch "-r" or "-o" 
 
 

How to repeat:
This is the table-defintion of the tables: 
 
CREATE TABLE nuke_whoiswhere ( 
  username varchar(25) NOT NULL default '', 
  time varchar(14) NOT NULL default '', 
  host_addr varchar(48) NOT NULL default '', 
  guest int(1) NOT NULL default '0', 
  module varchar(30) NOT NULL default '', 
  url varchar(255) NOT NULL default '' 
) TYPE=MyISAM; 
 
CREATE TABLE nuke_priv_msgs ( 
  msg_id int(10) NOT NULL auto_increment, 
  msg_image varchar(100) default NULL, 
  subject varchar(100) default NULL, 
  from_userid int(10) NOT NULL default '0', 
  to_userid int(10) NOT NULL default '0', 
  msg_time varchar(20) default NULL, 
  msg_text text, 
  read_msg tinyint(10) NOT NULL default '0', 
  PRIMARY KEY  (msg_id), 
  KEY msg_id (msg_id), 
  KEY to_userid (to_userid) 
) TYPE=MyISAM; 
 
I can send you the whole destroyed tables (about 1MB) if you need them. 
 
 
 

Suggested fix:
no idea

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Need a test case.

I can send you a tarball including the crashed tables if you like. What else would be appropriate to help you?

The corrupted tables aren't that useful for tracking down the bug - we would rather need a way to reproduce the corruption on our servers (i.e., something along the lines of "query X on the given data Y causes the tables to be corrupted").

I tried to figure out which special query corrupts the table. 
 
I activated the logfile and let myisamchk run every minute 
and log the results. There were no queries in the logs that 
did not occur 1000 times before the crash. The whole action 
is complicated because these are higly used tables. 
 
I don't know what causes this problem: But corruption 
happens - in different databases on different tables. 
 
We replaced Hardware with a change. Harddisk has no errors 
as far as i can see. The error occurs not only on one servers, 
but on 3. All of them experiencing heavy database load. 
 
Shall i run debug-version of Mysql?

My last comment stated that we changed hardware with a 
change. 
 
This is of course an error. We replaced it and the error is still 
there.

Hundreds, probably thousands of people are having these problems.  google a search for "table corruption mysql 4.0.12" and you guys will see how many people out their are having this problem.  I am having to repair databases daily for some clients.  It seams that the corruption occurs mostly on forums, but i assume thats just because of the high usuage.

I did a check on google according to the suggestion from this poster
and there is not many references of corruption with MySQL 4.0.12; Of
the found 200 links, most of them points to the MySQL manual and to
the MySQL public email lists, without any specific references to
4.0.12 or even to table corruption.  (4.0.12 is a mentioned in many
context as this is currently the latest MySQL 4.0 release).

We have a lot of customers running 4.0.12 and we have not got any
indication that 4.0.12 should not be resonable stable.

This doesn't mean that 4.0.12 is bug free but that it's very likely
that your problems are triggered by something that is relatively
unique for you and we would need much more information from you to be
able to help you find out what could be wrong.

This bug report is not a repeatable bug that is in the realm of this
bug find program, which is directed to find critical bugs that should
be fixed in the next release MySQL.

To get help for this, we recommend you to consider MySQL support
(http://www.mysql.com/support-and-consulting.html).

http://www.vbulletin.com/forum/showthread.php?s=3f7a4402c2ba41e9425d2a0011702683&threadid=...
http://www.vbulletin.com/forum/showthread.php?s=3f7a4402c2ba41e9425d2a0011702683&threadid=...
http://forums.cpanel.net/showthread.php?s=&threadid=8740&perpage=15&highlight=table%20corr...

These are just a cupple places with people having troubles.  Any information you can provide me for debuging the problem will be appriciated.

We had so many problems we finally downgraded to version 3.23.56 since we have not had one single problem. Before with version 4.0.12 it was a daily basis to have corrupt tables and databases as many as 12 in one day. Went on for 3 weeks we finally changed. To Bad!!!

We are having the same problem too. We have a heay used forum (vb) where we expirence up to 2 table corruptions per day. We run CHECK TABLES every 5 minutes and repair the corrupt tables when nessecary.
It is not possible to repeat the bug, it just happens from time to time.
We are running Linux and MySQL 4.0.13.

Here the output from the CHECK TABLE and REPAIR TABLE:
 
Wed, 21 May 2003 19:10:01 +0200:
        n_forum.thread  check   error   record delete-link-chain corrupted
        n_forum.thread  check   error   Corrupt
        repairing: n_forum.thread
        n_forum.thread  repair  info    Wrong bytesec: 0-0-0 at 16996600; Skipped
        n_forum.thread  repair  info    Wrong bytesec: 65-112-114 at 16996624; Skipped
        n_forum.thread  repair  info    Wrong bytesec: 0-0-0 at 16996664; Skipped
        n_forum.thread  repair  info    Delete link points outside datafile at 16996676
        n_forum.thread  repair  warning Number of rows changed from 183669 to 183666
        n_forum.thread  repair  status  OK

http://www.google.com/search?q=mysql+error+1016

thousands of people have found this bug!
I don't belive that downgrade to 3.x is the only solution... bad impression...

Thanks for the hint - the Google references will hopefully help us to find a pattern here. As it only seems to be happening when using MyISAM tables, we would need a detailed log of what's happening when the corruption occurs. To investigate this, we need:

a) An _intact_ copy of the MyISAM table before the corruption occurs
b) The MyISAM log file up to the point where the table gets corrupted (and the
   Error 1016 occurs). You can configue mysqld to write this log file by using
   the command line parameter "--log-isam[=file]"

This will hopefully give us enough info to investigate how the corruption occurs. Bundle these files into a zip or tar archive and upload them to ftp://support.mysql.com/pub/mysql/secret/ (write only access), possibly with
a reference to this bug number in the file name (e.g. myisamcrash_bug197.zip)

Some additional info:
We are experiencing this same problem, from atleast MySQL 4.0.10 - 4.0.13
RedHat versions 7.2 and 7.3, using rpm's from MySQL AB 

* the system is stable, no crashes, no external source that causes corruption.
* Occurs in master/slave setup, in 20% of the cases, the corruption is replicated, indicating that this is probably a locical fault in the mysqld, using the same sequence of queries. I'll try to package up some files and logs, but I suspect it wouldn't help to much since it happens only on big, highly volatile tables, f.ex we have 2 or 3 corruptions in the scope of 25000000 queries, on tables with 10000 or more rows.

I'm dedicating time to this problem, so the next time it happens, I'll try to extract more info about it, hopefully being able to provide a replication binlog that can produce the fault (although providing all the necessary info to MySQL is probably a tarball of 200-300 MB ... )

This bug has been reported more than once - I am closing this one as a duplicate
now - please follow the progress of this one here:

http://bugs.mysql.com/bug.php?id=563

This is happening two-three times a day on XMB 1.8 SP1, PHP 4.2.3, mysql 4.0.12 and now 4.0.13, CPanel6 7.2.1 STABLE #85, Apache 1.3.27 running on RedHat Linux 7.3 with the latest security fixes.

It is losing us our popular threads. Loss of data is a critical issue for us, and unacceptable for a database product. It has been affecting us for a while (I'll try and find the earliest post I made to our forum. I am willing to help troubleshoot this issue, but time has nearly run out - I can't lose any more data or have much more downtime.

I will be migrating us to Postgres on August 1 if there is no solution. 

Andrew

Can you still reproduce this behaviour with MySQL-4.0.14?
We have received serveral reports, that this version fixed the table corruption
problems for them.

4.0.14 fixed it for us.

I just reported that to the new bug because this one was closed as a dupe.

Maybe you have ever noticed the new Tesla Secret product? 
 
There are certainly statements that you can get 
free power by making use of this Tesla Secret thing through using quick blueprints and building a power generator just for a few us dollars. 
 
I am not sure if it's the real deal or not, but here is a webpage which would seem to explain significantly better : [url=http://teslasecretaccess.com]tesla secret access[/url]