Bug #449 Table corruption when adding or deleting table with fulltext search
Submitted: 19 May 2003 5:08 Modified: 8 Aug 2003 1:24
Reporter: David Mackay Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:mysql-4.1.0-alpha (Official MySQL RPM) OS:Linux (Redhat 9 linux distro)
Assigned to: CPU Architecture:Any

[19 May 2003 5:08] David Mackay
Description:
I have a database with a large number of documents using fulltext search.  When I try and add or delete rows, the table gets corrupted.

How to repeat:
If you'll give me a snailmail address and a case identifier, I'll have the cdrom with the database (over 300MB compressed) mailed to you.  Uncompress it in the /var/lib/mysql directory.  Then:
use ocrtext;
delete from documents where bates like "FGF%";

Suggested fix:
If you rebuild the table without fulltext, make the additions/deletions, then alter table to add the fulltext indexing, it works.
[20 May 2003 5:15] Alexander Keremidarski
David,

Is it possible to prepare smaller test case? Snailmail is not reliable solution as we are located in lot of different countries and as you know for fixing bugs for several developers have to be involved.

You can use our secret drop down area at ftp://support.mysql.com/pub/mysql/secret/

Maybe there is chance to repeat it with random data so you can send us your tables structure without data as well as queries causing corruption.
[19 Jul 2003 8:01] [ name withheld ]
I experience the same problem on W2000 pro and WXP pro with mysql 4.1.0 and mysql 3.23. I think it wouldn't make any difference if one used random data.

my table is created as follows : create table notices (ID INT NOT NULL PRIMARY KEY, ISBD TEXT, ISBN VARCHAR(250), titre VARCHAR(250), annee VARCHAR(250), auteur VARCHAR(250), editeur VARCHAR(250), collection VARCHAR(250), FULLTEXT(ISBD))
the data in the column ISBD (Fulltext indexed) are about 1 Ko each row) and it is starting to crash beyond 30000 rows.

It seems (but it's hard to be sure as it's a pretty long test) that it crashes mainly when you have droped then created the index and then add rows while the index exists (it doesn't crash (?) when you add rows while the index is dropped or when it has benn created from the very beginning)
[21 Jul 2003 5:59] Sergei Golubchik
Could you please confirm that MySQL 3.23 does, indeed, exhibit this behaviour ?

As for 4.1.0 - yes, it had table corruption bug related to Fulltext indexes - and it was fixed  several months ago.
[8 Aug 2003 1:24] Lenz Grimmer
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.