MySQL Bugs: #912: Inserts causing table corruption

Bug #912	Inserts causing table corruption
Submitted:	24 Jul 2003 11:17	Modified:	12 Aug 2003 13:44
Reporter:	Gary Thornock	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.1 alpha	OS:	Linux (Linux - Intel)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
We have a large MyISAM table containing support documents for a client web site.  We recently upgraded to MySQL 4.1, because we need support for UTF-8.  Since the upgrade, we've experienced problems with the documents table becoming corrupted when new documents are inserted.

We've only been able to reproduce the problem using the entire dataset in the documents table.  An empty table or a partial dataset doesn't exhibit the problem.  However, we are able to duplicate the problem on any of our servers anytime we import the full dataset from a mysqldump file.

How to repeat:
Import the large dataset in the dump file "docdump-071903-0600.sql.bz2", which is available at ftp3.sento.com ( username: mysqldumpz password: Ph@tDZZ! )

Then, execute the insert statements in the dump file "chinadocs-corrupt.sql", attached to this bug report (also available on the above FTP site).

I need extra information to invistigate this.

What character set did you use with 4.0?
What is the default character set you're using with 4.1?
I.e. what are "configure --with-charset=..." and "mysqld --default-charset=..."
in both cases?

What program the last script is executed from? From "mysql" tool or
from some GUI application, or from some language application
(php/perl,java/c)?

We weren't using 4.0; this upgrade was from 3.23.  The character set in 3.23 was Latin1.  The 4.1 default character set on my system is utf8; however, we have experienced the same problem on another system which doesn't have a default character set specified in /etc/my.cnf at all.

The inserts in the last script are executed by "mysql" tool, although we have also experienced corruption when similar actions are done by a perl script (using a script that has run successfully several times under MySQL 3.23).

New information:  I ran the test again after dropping the three FULLTEXT indexes from the documents table, and the table was not corrupted.  Having successfully done that, I tested again without the FULLTEXT indexes, importing a much larger set of inserts, and was again successful.

I do not believe that we can operate our production system without the FULLTEXT indexes on that table, but this may help isolate the cause of the problem.

FULLTEXT index table corruption bug was fixed after 4.1.0 release.

If you will experience table corruption after upgrading to 4.1.1 (when it will be out) please reopen this bugreport.