MySQL Bugs: #1804: Repair table, then rows are missing

Bug #1804	Repair table, then rows are missing
Submitted:	11 Nov 2003 6:52	Modified:	13 Dec 2003 13:05
Reporter:	ANDRE Cyril	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.0.15a	OS:	Linux (Linux Debian - kernel 2.4.20)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
Hello, 

Mysql server 4.0.15a is running under Linux Debian ( kernel 2.4.20) and I don't know why, but tables are often corrupted so I wrote my query as:

mysql_query("select ... ") or repair(table);
and the function repair() will repair the table.

This function repair tables but sometimes when I repair it, rows are missing.
***
031111 11:23:24  read_const: Got error 127 when reading table ./bdd/forums_cli
ents'
031111 11:23:24  Note: Found 200 of 201 rows when repairing './bdd/forums_clie
nts'
031111 15:27:08  Note: Found 971 of 972 rows when repairing './bdd/news'
***

How can I fix this problem and how to know why table are corrupted ? I saw the documentation but I don't find anythink which can help me.

Best regards,
Cyril ANDRE

How to repeat:
I don't know how repeat it, but I've tried with an other server and the problem was the same.

Are those directories where database files are located soflinked under the datadir directory? If two links from $datadir point to same target directory with datafiles, then that's the reason why they are corrupted.

Are your tables MyISAM type and compressed? If so, 4.0.14 and 4.0.15 has has a bug, so upgrade.

Did you modify the tables or run myisamchk while mysqld was running? If so, you know you were supposed to shutdown mysqld first of to flush table cache first?

Flush table cache a post :

myisamchk -dvv /data/$table.MYI output.

Thanks for your fast answer!

***
Are those directories where database files are located soflinked under
the datadir directory? If two links from $datadir point to same target
directory with datafiles, then that's the reason why they are
corrupted.
***

My *.MYI, *.frm, *.MYD are in /home/mysql/my_DB/

I have indicated it in the my.cnf file without any links. Nevertheless maybe you want to talk about somethink else ?

***
Are your tables MyISAM type and compressed? If so, 4.0.14 and 4.0.15
has has a bug, so upgrade.
***
My tables are MyISAM table but not read-only, so they can't be compressed.

***
Did you modify the tables or run myisamchk while mysqld was running?
***
I don't modify the tables but I create new tables. Is-it a problem ?

Best regards

Can you help me please ?

an idea?

Sorry, but you haven't included enough information for us to know where the problem should be.

Please spend a couple if minutes and read http://www.mysql.com/doc/en/Bug_reports.html

In the case you get crashed tables, you should first ensure if this is becasue mysqld dies in the middle of some operation.  If this is NOT the case, then you should be able to follow the instructions in the manual of how to generate at test case: http://www.mysql.com/doc/en/Reproduceable_test_case.html

Note that this public systems is only meant for repeatable bugs.  If you can't repeat it then you can either ask for help on the public MySQL email lists for help or consider buying MySQL support.