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

[20 Dec 2004 17:48] Greg Whalin
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?
[20 Dec 2004 17:49] Greg Whalin
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.
[20 Dec 2004 20:51] Hartmut Holzgraefe
does this happen with the x86_64 builds or with the regular x86 binaries?
[20 Dec 2004 20:54] Greg Whalin
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.
[16 Feb 2005 15:04] Greg Whalin
This is still happening as of mysql 4.1.9 on our opteron cluster and is 100% reproducable.  Any ideas?
[18 Feb 2005 2:08] Steven Roussey
I have this same problem, but I have an Athlon MP system and (obviously) using 32bit. (4.0.23 on RHEL 3)
[22 Feb 2005 20:44] Steven Roussey
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...
[21 Jun 2005 19:31] Matthew Lord
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
[21 Jun 2005 19:32] Matthew Lord
I'm sorry, I forgot to mention that I was using 4.1.12.