MySQL Bug with FLUSH TABLES and Fulltext Indexes in > MySQL 4.1

It seems that there’s a bug with MyISAM tables in any version of MySQL since 4.1 running fulltext indexes.

If you do a FLUSH TABLES (without replication running) and then copy these tables to another DB server (which is essentially a mysqlhotcopy) replication will eventually break with:

Error 'Incorrect key file for table './blogindex/FOO.MYI'; try to repair it' on query. Default database: 'mydb'. Query: 'DELETE FROM FOO WHERE FOO.DATE_FOUND < '2006-12-12 22:35:33'', Error_code: 126

This works just fine for regular MyISAM tables and only breaks for tables which have fulltext indexes. It seems that maybe the index isn’t correctly being written to disk?

  1. Augusto Bott

    If you move tables with fulltext indexes between servers and they have different “ft_min_word_len” values OR have a different list of stopwords you must rebuild the indexes before using the table (REPAIR TABLE xyz USE_FRM;).

    There are other situations when you must apply such repair operations but I can’t remember all of them right now :-)

  2. Augusto……

    Thanks…… that’s probably EXACTLY what it is!!! You rule!


%d bloggers like this: