MySQL Bugs: #65512: FULLTEXT index not being properly maintained on innoDB table

Bug #65512	FULLTEXT index not being properly maintained on innoDB table
Submitted:	4 Jun 2012 17:20	Modified:	23 Aug 2012 18:07
Reporter:	Ryan Carpenter	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	5.6.5-m8	OS:	Windows (Windows 7 Enterprise)
Assigned to:		CPU Architecture:	Any
Tags:	5.6.5

Description:
Full-text searches are not working properly on one of my innoDB tables. I have detected numerous errors while searching on one of the two fulltext indices (the other index may have problems as well, but I've been able to detect issues with the second index because of the small and discreet set of data it contains).

I can run the following query:
select count( * )
from Recipe
where SOURCE like '%Roasting%' order by RECIPE_ID

and this returns 37 rows. However, when running a similar query using the fulltext index no rows are returned:

select count( * )
from Recipe
where Match (SOURCE) Against ('Roasting') order by RECIPE_ID

Similar problems can be identified with other terms, some of which will return a couple rows (so the term apparently is in the index), but not all of the expected rows. For example, I can search for 'Feb' using the fulltext search and get two rows, but using the equivalent LIKE search 13 rows are found in the table with the term.

I am using the default stopwords file so the faulty searches in question are not being performed against stopwords. Also, I have verified that the terms I am search for exist in well under 50% of the records in the table. The same problem is occurring when I search using Boolean mode.

The following is the result of show create table:

CREATE TABLE `recipe` (
  `RECIPE_ID` decimal(17,0) NOT NULL,
  `NAME` varchar(100) NOT NULL,
  `ALREADY_MADE` char(1) NOT NULL,
  `DATE_LAST_MADE` date DEFAULT NULL,
  `RATING` int(11) DEFAULT NULL,
  `COOKING_METHOD_ID` char(3) DEFAULT NULL,
  `SOURCE` varchar(100) DEFAULT NULL,
  `SERVINGS` varchar(100) DEFAULT NULL,
  `MY_COMMENTS` text,
  `SOURCE_TEXT` text,
  `RECIPE_TEXT` text,
  PRIMARY KEY (`RECIPE_ID`),
  UNIQUE KEY `NAME` (`NAME`),
  KEY `COOKING_METHOD_ID` (`COOKING_METHOD_ID`),
  FULLTEXT KEY `IDX_FULLTEXT_RECIPE_CONTENT` (`NAME`,`MY_COMMENTS`,`SOURCE_TEXT`,`RECIPE_TEXT`),
  FULLTEXT KEY `IDX_FULLTEXT_RECIPE_SOURCE` (`SOURCE`),
  CONSTRAINT `recipe_ibfk_1` FOREIGN KEY (`COOKING_METHOD_ID`) REFERENCES `cooking_method` (`COOKING_METHOD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

How to repeat:
I have been unable to reproduce this problem with a new table. I have created a new schema and used the mysql dump utility to create a dump of the db, and then used the mysql utility to load the dump in the new schema. As a result, I have an exact copy of the faulty DB that is populated with the exact same data. However, on this newly created table, the full text search appears to be working fine. So it appears that the fulltext index was not being properly maintained or was corrupted somehow for the old schema.

Thank you for the report.

If you use option --innodb-file-per-table you can make either partial backup using MySQL Enterprise Backup which will include shared tablespace required to properly restore backup or just send us *ibd and *frm files and we hack shared tablespace on our side.

But probably more useful would be try to debug on your side first.

Please send us logical dump of the table, then results of following test on problematic server:

CHECK TABLE recipe;

set global innodb_ft_aux_table='YOUR_DBNAME/recipe';
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;

Thank you for the feedback.

Looks like we had not received the file.

Thank you for the data provided.

In my environment mysqld dies when run full text query. Could you please send us your error log file too?

Thank you for the feedback.

Verified using file provided.

To repeat one only need original ibdata1 file:

1. Create 5.6.5 MySQL Sandbox
2. Stop it
3. Copy ibdata1 from archive
4. Start sandbox server (no configuration changes needed)
5. Load dump into new empty database (say, test)
6. Ensure both queries work correct
7. Stop server
8. Start server
9. Run queries again
10. Notice query which uses fulltext index returns 0

Ryan, thanks for the report. The FTS index is apparently missing indexed record.

Can you in some way to reproduce the scenario? Is there a crash of server in between?

I have not been able to reproduce the problem with new entries. I can continue to reproduce the issue with pre-existing records which I know already exhibit the behavior, but whenever I create a new record and then search for the newly entered record, it always returns as expected. This also works even if the term I search for is one of the terms that is missing results. For example, the term "roasting" currently has no results when matching against (source), but if I add a new record with "roasting" in the source column, it will return as expected. The missing records continue to not return as expected, though.

I have never experienced a server crash that I can recall. Certainly, the server never crashed right as I entered new data, as I would have definitely noticed that. Is it possible that this could be caused by an improper shut-down? I am running Windows 7 Enterprise, and as the DB is on my personal workstation it gets rebooted periodically, and because it's Windows, every once in a while I've got to flat-out kill the machine (while the MySQL service is running). This is never done at the point in time at which any transactions are being executed on the DB, though.

Could this be at all related to the multiple full text indices on the table? As far as I can recall, all the problems have been with the second of the two indices on the table. I also have observed that at least some of the records for which searches are failing on the 2nd index appear to be working fine on the first index. For instance, I can locate a record which is not being returned as it should when matching on the 2nd (source) index, but the record is returning as expected when I search against any number of terms in the 1st index. So it appears that while that record had it's entries created fine in the first index, it was only the 2nd index that is missing it's entries.

One thing I have also noticed is that the searches appear to work for all search terms, or for none of them. They never partially work. For example, if I have a record with a column value of 'one two three four five' that has a full-text index on the column, either all the search terms work, or none of them do. If I can search for 'one' and get a result, I will also properly receive results when searching for 'two', or 'five', or '+three +four +one', etc. Similarly, if one fails, they will all fail. If searching for 'one' fails to return the record, searching for 'two', 'three', 'four', and 'five' will also all fail to return the record. So as far as I can tell, the problem appears to not be with individual search terms for the record, but with all entries in the index with any given record. (And note again, it always appears to be only the 2nd full-text index on the source column which has these problems.

Thank you for the feedback.

> Is it possible that this could be caused by an improper shut-down? I am running Windows 7 Enterprise, and as the DB is on my personal workstation it gets rebooted periodically, and because it's Windows, every once in a while I've got to flat-out kill the machine (while the MySQL service is running). This is never done at the point in time at which any transactions are being executed on the DB, though.

Yes, improper shutdown can make a failure. But strange this happens why there was not running transaction.

Setting status to "Need Feedback" in case if you could repeat situation when it happens.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".