MySQL Bugs: #87: Multiple fulltext index will not rebuild under truncate, insert into select

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:	None
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

Description:
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.

xing@mac.com

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.

When performing: 

sql>insert into table select * from table_with_same_columns

table_with_same_columns is a innodb table.

Make a mistake in categorization...This is not a critical problem.

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)
) TYPE=MyISAM;

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
sake)

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
phpmyadmin.

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