MySQL Bugs: #76357: Fulltext index randomly fails

Bug #76357	Fulltext index randomly fails
Submitted:	17 Mar 2015 18:36	Modified:	30 Apr 2018 11:01
Reporter:	Marek Lemanczyk	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	5.6.23-log Community Server (GPL)	OS:	Windows (7/8)
Assigned to:		CPU Architecture:	Any

Description:
For some reason fulltext search constantly stops returning any rows. The issue is random, but it always occurs at some point. At that time all the records get score = 0. This happens on various computers and operating systems.

I've the required indexes. Re-building the indexes sometimes help, sometimes it doesn't. The table is very simple, just few columns. In the basic case it has just few rows.

How to repeat:
Create the table.

CREATE TABLE `units` (
  `UnitId` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(15) COLLATE utf8_general_ci NOT NULL,
  `ShortName` VARCHAR(5) COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`UnitId`) USING BTREE,
  UNIQUE KEY `IX_Name` (`Name`) USING BTREE,
  UNIQUE KEY `IX_ShortName` (`ShortName`) USING BTREE,
  FULLTEXT KEY `UNITS_NAME_FTS_IDX` (`Name`),
  FULLTEXT KEY `UNITS_SHORTNAME_FTS_IDX` (`ShortName`)
) ENGINE=InnoDB
AUTO_INCREMENT=31 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

Insert and delete few records multiple times.
'26', 'Kilometer', 'km'
'27', 'Meter', 'm'
'28', 'Liter', 'l'
'29', 'Kilogram', 'kg'
'30', 'Gram', 'g'

Run the query.

SELECT
`Extent1`.`UnitId`, 
`Extent1`.`Name`, 
`Extent1`.`ShortName`
FROM `units` AS `Extent1`
 WHERE 
 ((MATCH(`Extent1`.`Name`) AGAINST ('kilometer*' IN BOOLEAN MODE)) > 0) OR 
 ((MATCH(`Extent1`.`ShortName`) AGAINST ('kilometer*' IN BOOLEAN MODE)) > 0)
LIMIT 20;

You may need to run it multiple times on different days to get the failure. Repeat insert/deletes/query until you get the failures. They always happen for us at some point.

Suggested fix:
The query should consistently return same rows for the same data.

Still exists in 5.6.26 community edition.

I did the test in loop 1000 times and cannot reproduce - are you able to create fully reproducible test case script maybe?

for i in {1..1000}; do mysql test -e "delete from units; insert into units values ('26', 'Kilometer', 'km'), ('27', 'Meter', 'm'), ('28', 'Liter', 'l'), ('29', 'Kilogram', 'kg'), ('30', 'Gram', 'g'); SELECT Extent1.UnitId,  Extent1.Name,  Extent1.ShortName FROM units AS Extent1  WHERE   ((MATCH(Extent1.Name) AGAINST ('kilometer*' IN BOOLEAN MODE)) > 0) OR ((MATCH(Extent1.ShortName) AGAINST ('kilometer*' IN BOOLEAN MODE)) > 0) LIMIT 20;"|wc -l >> q.out; done

$ uniq q.out 
2

I couldn't repeat too. If you are able to provide a repeatable test case please do. Thanks in advance.