Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Did you run myisamchk against table_search.MYI with mysqld still running? –  RolandoMySQLDBA May 9 '12 at 17:18
    
No but see my answer below, there was something like this. –  Martin May 10 '12 at 12:42

2 Answers 2

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.

share|improve this answer

This sounds as your table is corrupted and is beyond what myisamchk is able to recover.

I guess your best try is to recover the table from a backup or, if only your search index is affected, re-create and re-populate this table.

share|improve this answer
    
Thanks for your answer! There was another problem, please see my answer below. –  Martin May 10 '12 at 13:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.