MySql crash: Incorrect key file for table

corruptionmyisamMySQL

We have a MySql 5.5 database running InnoDB on Linux. One table (named table_search) is running MyISAM to be able to perform full text searches. The table has 823 368 rows.

Yesterday we had a server crash, to get it back up again we moved the raw data to a new server and now we are running on the new server.

It worked fine at first, but after an hour we got something like this in the error logs:

1. Duplicate entry 'X-Y-Z' for key index_titles (where index_titles is an index in table_search)

2. After this there where lots of errors: Incorrect key file for table './database/table_search.MYI'; try to repair it

3. After this there where lots of errors: Table './database/table_search' is marked as crashed and should be repaired

We tried to repair it by using:

myisamchk table_search.MYI

We got error like this:

myisamchk: error: Keypointers and record positions doesn't match
MyISAM-table 'table_search.MYI' is corrupted

Then we tried:

myisamchk -r -o table_search.MYI

Now everything was working again until an hour ago when the same thing happened again. So we used myisamchk again and it is working.

But it doesn't feel that stable as it happened again. What can we do to make sure it won't happen again? Why is it corrupted? What is the solution?

Best Answer

The solution was something else. We had restarted the server using:

ft_min_word_len=3

(sorry for not mentioning this in the question)

By this document at MySql we learned that:

If you use myisamchk to perform an operation that modifies table indexes (such as repair or analyze), the FULLTEXT indexes are rebuilt using the default full-text parameter values for minimum and maximum word length and the stopword file unless you specify otherwise. This can result in queries failing.

This was the solution: 

shell> myisamchk --recover --ft_min_word_len=3 table_search.MYI

Now the server has been running fine for almost 24 hours.

Related Topic