MySQL Bugs: #11336: FULLTEXT and ft_min_word_len causes table crash on DELETE

Bug #11336	FULLTEXT and ft_min_word_len causes table crash on DELETE
Submitted:	15 Jun 2005 2:39	Modified:	18 Dec 2005 22:38
Reporter:	Lachlan Mulcahy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.12	OS:	Any (Any)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	corruption, myisam

Description:
Setting ft_min_word_len=1 in your my.cnf and then attempting to do a large DELETE from a table with a FULLTEXT indexed field causes the error:

ERROR 1034 (HY000): Incorrect key file for table 'tax_content'; try to repair it

Any REPAIR is successful but attempting the operation again results in the same error. 

Removing the ft_mind_word_len=1 line from the my.cnf stops the problem occurring.

Note: The DELETE does not have to even reference the field with the FULLTEXT index to cause the table crash.

How to repeat:
See internal info.

Suggested fix:
None.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi, thanks for reporting this bug.

Have you properly launched repair table right after changing
ft_min_word_len? An excerpt from manual:

The problem occurs because these parameters are known only by the
server.  They are not stored in `MyISAM' index files.  To avoid the
problem if you have modified the minimum or maximum word length or the
stopword file in the server, specify the same `ft_min_word_len',
`ft_max_word_len', and `ft_stopword_file' values to `myisamchk' that
you use for `mysqld'. For example, if you have set the minimum word
length to 3, you can repair a table with `myisamchk' like this:

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

I am currently experiencing a similar problem, also using MySQL Max V4.1.12 with an ft_min_word_len of 3. One of our tables, utilizing a FULLTEXT index, allows insertions, but update queries report an incorrect key file. Attempts to repair the table (using quick, extended and use_frm methods) do not fix the problem.

Further testing is currently in progress, as generating a duplicate of the problem table from a nightly dump produced a perfectly working copy - updates were successful - yet after dropping the old table and replacing it with the new copy, the issue returns. Clearing the query/key cache seems to make no difference.

More information as tests continue.

Rob Beckett
Santa Cruz Tech / Blogshares.com

Ooops, just hit me we're using V4.1.14. :x

I'm also seeing this problem with 4.1.1[234] on Linux 2.6 in a default install compiled from source. Seems like it's related to data being in UTF-8 since it's not occuring when the charset/collation is set to latin1. 
Is the bug to be resolved in a near future or will it keep hanging around just like #5686 which is similiar and possibly related?

Info update - The ft_min_word_len variable was left out altogether / set back to default, indexes rebuilt, and since then we've had a lot less problems. Now UPDATE's work beautifully, before they would cause incorrect keyfile errors.

Still trying to figure out some soft-crash problems, but no more 'hard' crashes where the server has to be restarted manually. :-)

Is anyone but me getting errors like "Key in wrong position at page 2617344" or "Can't find key for index: 3" after setting ft_min_word=4 under [mysqld] and rebuilding the indexes with 4.1.14/4.1.15?

Currently this bug is in heavy progress. We're doing our best to fix this bug.
I was able to simplify a testcase for this bug (it is about 400K now).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/220

Fixed in 4.1.17

"DELETE could report fulltext index corruption (Invalid key for table ...) if the index was built with repair-by-sort, the data in the fulltext index used UCA collation, and some word appeared in the data terminated by 0xC2A0 character as well as by other non-letter characters"

Noted in 4.1.17 changelog.