Bug #87 Multiple fulltext index will not rebuild under truncate, insert into select
Submitted: 20 Feb 2003 16:18 Modified: 28 Jun 2003 5:15
Reporter: Xing Li Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.10 standard OS:Linux (RedHat 7.3)
Assigned to: Sergei Golubchik CPU Architecture:Any

[20 Feb 2003 16:18] Xing Li
On a MyISAM table with 210K rows with 3 columns plus one pimary and two fulltext index

storyid-primary int11
title-fulltext varchar 255
summary-fulltext varchar 255

Have an cron script to periodically dump the table and then repopulated with new data:

sql>truncate table;
sql>insert into table select * from another_table_with_same_columns;

Everyone works except that after the select insert, phpMyAdmin-2.4.0rc1 reports that the fulltext indices have cardinality of null and a simple full-text match() query reports that no full-text index is available exist for the column when it does. 

Please note that I use the exact same cron script on a table with one primary but only one full-text index and everything works for that table. It seems to be related to insert select for full-index when the number of full-text index goes about 1.


How to repeat:
Have a table with one pimary index and 2 full-text indices populated with data so everything including the full-text indices are operating normally.

do a truncate table;

then insert into table select * from another_table_with_same_columns

Suggested fix:
Do not have a suggestion.
[20 Feb 2003 16:22] Xing Li
When performing: 

sql>insert into table select * from table_with_same_columns

table_with_same_columns is a innodb table.
[20 Feb 2003 16:30] Xing Li
Make a mistake in categorization...This is not a critical problem.
[25 Mar 2003 1:26] Sergei Golubchik
from email correspodence
I have isolated the problem and have dozens of repeat success with new
test case:

table structure

CREATE TABLE story_fulltext (
   storyid int(11) NOT NULL default '0',  
   categoryid int(11) NOT NULL default '0', 
   title varchar(255) NOT NULL default '',
   summary varchar(255) NOT NULL default '',
   PRIMARY KEY  (storyid),
   KEY categoryid (categoryid),
   FULLTEXT KEY title (title),
   FULLTEXT KEY summary (summary)

1) sql > truncate table table;

This is not part of the problem as far as I can see.

2) sql> insert into table select * from another_table_with_same_columns

3) This is the crucial step: while step 2 is performing, about 20
seconds for my setup of about 210K rows, perform a few fulltext
searches on the  summary field. (searches on the title should also get
the same result but I did most of my test on the summary for simplicity

4) After step 2 completes in the course of getting bombarded with a
some fulltext searches, either the title and/or summary will get
corrupted and subsequent searches will report no fulltext index found
on column and  cardinally on columns will report as "none" on
[28 Jun 2003 5:15] Sergei Golubchik
I tried this, but was not able to repeat the bug - after insert ... select is finished, both fulltext indexes are fully functional, and produce correct results for fulltext searches