MySQL Bugs: #13712: MyISAM table keeps crashing with "Can't find key for index: 3" (ut-8 related?)

Bug #13712	MyISAM table keeps crashing with "Can't find key for index: 3" (ut-8 related?)
Submitted:	3 Oct 2005 11:57	Modified:	18 Dec 2005 22:55
Reporter:	noah williamsson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.16	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	corruption, myisam

Description:
A table with utf8 charset and utf8_swedish_ci collation and a FULLTEXT index over three TEXT fields crashes several times a day. 
CHECK TABLE says "Record at: 198459508  Can't find key for index:  3".
A REPAIR fixes the issue but it breaks after a couple of hours.

This error has occured on 4.1.13, 4.1.14 and 4.1.15 from the BK repo from 2005-09-30.

Previously I got "Incorrect key file for table 'art_main'; try to repair it" (with 4.1.{12,13,14}), as reported by several users here that used utf8 and fulltexts, and one suggested fix was to add "ft_min_word = 4" under [mysqld] in my.cnf.
After adding this to my.cnf this problem went away but instead I started getting "Can't find key for index:  3".

I've tried to drop the database and recreate it using dumps with no success.

Unfortunately I haven't been able to isolate the problem. Rerunning the queries that were executed before the table crashed after the table has been repair doesn't seem to crash the table again.
I'm gonna try to take a fresh dump of the database and then run try to apply the binlog against it.

The system is a Linux 2.6 system with grsec and NPTL running on a P4 system.
MySQL has been compiled from source with and without optimizations and with and without --enable-assembler using GCC 3.3.5 with no difference.

A couple of UTF8/Fulltext bugs seem to have been fixed in the recent 4.1 releases but none of them seem to have fixed this problem.

How to repeat:
No idea, yet.

Thank you for a problem report. Please, send the usual SQL statements used when this problem encountered. Is there something in the error log during this "crashes"? How many rows are in the table, what kind of data they usually contain? You my.cnf file content masy be of some use too.

I am asking all these questions to be able to create a repeatable test case. I'll appreciate one from you too.

Changed severity also, because, if I understood you right, you fond a temporary workaround for this problem.

Thank you for a detailed description of the issue.

As for error 145, it is simple:

[openxs@Fedora 4.1]$ bin/perror 145
MySQL error code 145: Table was marked as crashed and should be repaired

As for 'CHECK TABLE art_main EXTENDED'.... Do you run simple CHECK TABLE art_main before that and analyze the results? 

As for this question:

"Is there a possibility that the table check could interfer with UPDATE by the
scripts that import articles?"

only you know the answer... If there are several (bunch of) scripts, then cron does not guarantee you that one finished before the other start. Check it (are there any logs maintained by your scripts?). I'd better create one "nightly job" script, that imports news and then check everything you need.

As the amount of news vary, their import may take different time. And surely CHECK TABLE should not overlap with UPDATES of the same table, or you'll get corrupted table.

So, check all these and inform about the results.

As you know the problem occurs at specific time when you run maintainance scripts you can try to restart mysqld before that with general log enabled (and check tables to be sure they're ok, and the corruption indeed happens at that time and is not only discovered then). General log may tell you something (or it may tell us something).

of course, error 145 in the log is next to useless, as it only tells the table was corrupted before that, but doesn't tell when.

Another idea is to try to repeat the corruption with MySQL 5.0 - it reports MyISAM corruptions more aggressively, you should be able to pinpoint the query that causes the corruption.

general log is switched on with --log command-line option.
it's plain-text log, not binary

Would you guys please have a look at the bugreport for #5686?
There's a repeatable case that works with the latest 4.1 releases.

Thank you for a useful note. Developers are working on fix for the bug http://bugs.mysql.com/bug.php?id=5686. Looks like you may have a similar problem.

If you do not want to investigate yuor problem further and get those kinds of feedbacks you did not liked, I can mark this report as duplicate of 5686. What do you think about this idea?

Duplicate of http://bugs.mysql.com/bug.php?id=5686, as suggested by the reporter.

I reopen this bug, because bug 5686 is fixed but customer's problem still present.

reproduced it with data , submitted by customer and  testcase I wrote (attached  here) against latest mysql 4.1.16 (from bk tree, mean with patch for bug 5686).

testcase. must be run against table submitted by customer

Attachment: bugtest.pl (application/octet-stream, text), 2.24 KiB.

Alexsey, i just saw that your finding on #5686 was uploaded as a patch.
I'm curious whether your script crashes the table i gave you, or not, after applying this patch since I experienced a bunch of crashes even after patching the second place in ft_parser.c.

noah, yes, this script (I attached) crashes indexes of the table you sent me, after all patches that we have for now.

This appears to be the same bug as bug #11336.  I am setting this bug as a duplicate, and asking the bug submitter to verify that this problem does not exist when using the new code.

Regards,

Timothy