MySQL Bugs: #35928: Commenting/uncommenting ft_stopword

Bug #35928	Commenting/uncommenting ft_stopword_file causes myisam index corruption
Submitted:	9 Apr 2008 9:36	Modified:	9 Apr 2008 21:51
Reporter:	Colin Smith	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Linux (Debian)
Assigned to:		CPU Architecture:	Any
Tags:	corruption, ft_stopword_file, myi

Description:
Simply uncommenting and commenting out ft_stopword_file between restarts of the mysql server causes index corruption.

Apr  9 11:20:51 4del03895-vm0 mysqld[29059]: 080409 11:20:51 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './test/test.MYI'; try to repair it
Apr  9 11:20:51 4del03895-vm0 last message repeated 7 times

How to repeat:
Below is a script I created to test the problem.

First comment out ft_stopword_file from the my.cnf file, restart mysqld and run the script with the the parameter "init". This creates a test database with a single table with a single text column and a fulltext index on that column. It also populates it with a little data.

e.g.
vi /etc/mysql/my.cnf ; /etc/init.d/mysql restart ; ./runtest.sh init

Now edit the my.cnf file and uncomment the stopword file. Restart mysqld and run the script *without* the init parameter. This performs some inserts and updates on the existing data in the column with the fulltext index.

vi /etc/mysql/my.cnf ; /etc/init.d/mysql restart ; ./runtest.sh

It will fail and corrupt the index with the following error noted in the description.

The stopword file has the following lines

mary
had
a

#!/bin/sh

COMMAND="$1"
STOPAT=5

if [ "$COMMAND" = "init" ]; then

echo "Mary had a little lamb" > data

mysql <<DBINIT
drop database if exists test;
create database test default charset utf8;
use test;
create table test (testcolumn text , fulltext index testindex (testcolumn)) engi                                                                                                               ne=myisam;
DBINIT

fi

COUNT=0
while [ $COUNT -le $STOPAT ];
do

DATA=`cat data`

mysql <<EOSQL
use test;
insert into test (testcolumn) values ('Mary had a little lamb, its fleece was wh                                                                                                               ite as snow');
update test set testcolumn="$DATA";
EOSQL

mysql --database=test --execute="select * from test;" > data

clear
echo "Loop count $COUNT"
echo -n "Data size " ; ls -sh data

COUNT=`expr $COUNT + 1`
done

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html:

To override the default stopword list, set the ft_stopword_file system variable. (See Section 5.1.3, “System Variables”.) The variable value should be the pathname of the file containing the stopword list, or the empty string to disable stopword filtering. After changing the value of this variable or the contents of the stopword file, restart the server and rebuild your FULLTEXT indexes.

This is also the expected result. When you create a fulltext index with your ft_stopword_file, the index won't contain words from the stopword list (it's only contain two words "little" and "lamb" in your example). Then you disable the list, and restart the server. Now when you try to access the table the server notices that (in your example) the index must've contained three words ("Mary", "little", "lamb"), but it only contains two - that is the index is corrupted.