MySQL Bugs: #5528: Repair table corruption on fulltext indexes with icc binaries

Bug #5528	Repair table corruption on fulltext indexes with icc binaries
Submitted:	11 Sep 2004 13:04	Modified:	17 Nov 2004 12:00
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.21	OS:	Linux (Linux)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
REPAIR TABLE and OPTIMIZE TABLE operations on tables containing fulltext indexes results in corruption with icc compiled binaries( mysql-stanard-4.0.21-pc-linux-i686-icc)

It does not occur with gcc compiled binaries(mysql-standard-4.0.21-pc-linux-i686)

Glibc version in use is glibc 2.3.2

How to repeat:
CREATE TABLE `event` (
  `id` int(11) NOT NULL auto_increment,
  `startDate` date default NULL,
  `endDate` date default NULL,
  `heading` text,
  `description` text,
  `linkHref` text,
  `linkText` text,
  `note` text,
  PRIMARY KEY  (`id`),
  KEY `startDateIdx` (`startDate`),
  KEY `endDateIdx` (`endDate`),
  FULLTEXT KEY `heading` (`heading`,`description`),
  FULLTEXT KEY `heading_2` (`heading`,`description`)
) TYPE=MyISAM AUTO_INCREMENT=228 ;

INSERT INTO `event` VALUES (3, '2003-10-29', NULL, 'Jesses', 'Hasse Ling and his syncopators of Swing', NULL, NULL, NULL);

REPAIR TABLE event;

I was able to repeat this on my RH 9 box:
Linux booty 2.4.21 #12 SMP Thu Aug 14 00:49:40 EDT 2003 i686 i686 i386 GNU/Linux

I noticed that the second repair succeeds.  Use this to repeat:

CREATE TABLE `event` (
  `id` int(11) NOT NULL auto_increment,
  `startDate` date default NULL,
  `endDate` date default NULL,
  `heading` text,
  `description` text,
  `linkHref` text,
  `linkText` text,
  `note` text,
  PRIMARY KEY  (`id`),
  KEY `startDateIdx` (`startDate`),
  KEY `endDateIdx` (`endDate`),
  FULLTEXT KEY `heading` (`heading`,`description`),
  FULLTEXT KEY `heading_2` (`heading`,`description`)
) TYPE=MyISAM AUTO_INCREMENT=228 ;

INSERT INTO `event` VALUES (3, '2003-10-29', NULL, 'Jesses', 'Hasse Ling and his
syncopators of Swing', NULL, NULL, NULL);

REPAIR TABLE event;

CHECK TABLE event;

-- notice how the second repair succeeds
REPAIR TABLE event;

-- and now again the third fails
REPAIR TABLE event;

-- now the fourth succeeds
REPAIR TABLE event;

Also occurs with mysql-standard-4.0.22-pc-linux-i686-icc-glibc23

table corruption is also with 4.0.22 icc build for linux

(1)truncate table story_fulltext
(2)ALTER TABLE story_fulltext DROP INDEX `title`, DROP INDEX `summary`
(3)insert into story_fulltext select storyid, categoryid, title, summary from stories
(4)ALTER TABLE story_fulltext ADD FULLTEXT (`title`), ADD FULLTEXT (`summary`)

Query 1 through 3 runs fine. 4 completes but the data goes from 500K+ rows to only 4. 

I tried to run 1-3, then use phpmyadmin to add fulltext to only the title field and same corruption happens to the story_fulltext table.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

It's apparently a bug in icc - in the interprocedural (ip) optimizations.
Somehow it breaks in the myisam/mi_check.c, sort_ft_key_read() function.

For now we'll turn this optimization off.

next release - 4.0.23 - should be ok