MySQL Bugs: #7437: myisamchk seems to be corrupting full text indexes

Bug #7437	myisamchk seems to be corrupting full text indexes
Submitted:	20 Dec 2004 17:48	Modified:	21 Jun 2005 19:32
Reporter:	Greg Whalin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.22	OS:	Linux (Fedora Core2 Linux on Opteron)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
Here is how I reproduce (100% of time on all of our tables w/ Full Text):

- shut down server
- run myisamchk on tables (myisamchk -r chapter.MYI)
- start server
- show slave status and notice that table is showing corrupted index (Slave: Error 'Incorrect key file for table: 'chapter'. Try to repair it' on query.) Table corrupts immediatly on 1st write.

At this point, I have tried:
- repair table. This churns for a bit and then segfaults (in logs: mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.)
- shut down and run myisamchk -r (have tried -o as well). This notices table index is corrupt and fixes it, but same thing happens when I restart server on first write to table.

Only fix is to run: alter table chapter TYPE=MyISAM;

This is only happening on our Opteron box w/ 4.0.22 (was not happening prior to 4.0.20) and is 100% reproducable on any of our tables that have a full text index.

How to repeat:
Here is how I reproduce (100% of time on all of our tables w/ Full Text):

- shut down server
- run myisamchk on tables (myisamchk -r chapter.MYI)
- start server
- show slave status and notice that table is showing corrupted index (Slave: Error 'Incorrect key file for table: 'chapter'. Try to repair it' on query.) Table corrupts immediatly on 1st write.

At this point, I have tried:
- repair table. This churns for a bit and then segfaults (in logs: mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.)
- shut down and run myisamchk -r (have tried -o as well). This notices table index is corrupt and fixes it, but same thing happens when I restart server on first write to table.

Only fix is to run: alter table chapter TYPE=MyISAM;

This is only happening on our Opteron box w/ 4.0.22 (was not happening prior to 4.0.20) and is 100% reproducable on any of our tables that have a full text index.

Suggested fix:
I honestly have no idea. Seems myisamchk is corrupting full text indexes?

Also, I want to mention that this is happening on multiple installs on different machines (all Opterons).  Not happening on our Xeon boxes or on our Athlon boxes.

does this happen with the x86_64 builds or with the regular x86 binaries?

With mysql-standard-4.0.22-unknown-linux-x86_64-glibc23.tar.gz.  I have not tried with standard x86 binaries, nor have I tested with 4.1 yet.

This is still happening as of mysql 4.1.9 on our opteron cluster and is 100% reproducable.  Any ideas?

I have this same problem, but I have an Athlon MP system and (obviously) using 32bit. (4.0.23 on RHEL 3)

I found a problem with MySQL crashing tables on AMD machines when inserting just a sigle query (in some cases) into a table with FTS indicies. I haven't really noticed myisamchk doing so, however.

I did find a solution (bug 8613) in my case. I found that MySQL versions in the last year when run on AMD machines and using the NPTL thread library (default in RedHat Linux since RH9) was the magic needed to cause this issue. So you might want to try using LD_ASSUME_KERNEL=2.4.19 when starting MySQL on your machines (the slave in this case). It is something to try at least...

I was unable to repeat this on this machine:
Linux melody 2.6.5-7.97-smp #1 SMP Fri Jul 2 14:21:59 UTC 2004 x86_64 x86_64 x86_64 GNU/Linux

running SuSE enterprise server 9.

Here is the test that I did, please also try this:
create table fttest (id int not null primary key auto_increment, name char(20), info text, fulltext index ft_info (info));
insert into fttest (name, info) values ("Matt", "matt went to the store and got some cookies for sidney");
insert into fttest (name, info) values ("Carly", "carly went and had some food with her friends");
insert into fttest (name, info) values ("Sidney", "sidney went to see the Yankees play with his dad on father's day");
select * from fttest;
/* shutdown mysqld */
myisamchk -r fttest
/* startup mysqld */
insert into fttest (name, info) values ("Sidney", "sidney went to see the Yankees play with his dad on father's day again");
check table fttest;

Best Regards

I'm sorry, I forgot to mention that I was using 4.1.12.