Description:
I have a table in a database that has a few fulltext indexes on some columns.
At least one of these fulltext indexes has stopped being updated. What I mean by that is:
- If I do a trivial MATCH ... AGAINST (... IN BOOLEAN MODE) query, any row where the relevant columns have been recently updated will never show up in the results, even if they do match
- other rows, that haven't been recently updated, and that do match the query, do show up in results even for the same queries
- I can take an "old" row that does match and does show up in results, modify it in such a way that it still matches, and it will stop showing up in results for the same query even if it still matches.
All this suggests that:
- the index somehow stopped being updated at some point
- stuff that had already been indexed is found in queries
- when a row gets updated, somehow it does get "unindexed", but the new record is not indexed.
But the most outrageous thing is that there's nothing in the error log regarding that table, neither when I attempt to query it, nor when I update some row, whose new value obviously is failing to be indexed.
Also, mysqlcheck says the table is ok.
If anything is wrong with the table, I expect to see some errors in the output of mysqlcheck. If indexing a new record or a new value for an updated record fails for whatever reason, I expect to see something in the error log.
Besides the error log and mysqlcheck, I don't see any other tools in the documentation for debugging issues with indexes.
The issue in our case went undetected for ages, with users being unable to find the data they were searching for and just assuming it wasn't there.
How to repeat:
I have no idea how to reproduce it. It has happened on a database that has been in use for many years. Other databases with the exact same structure on the same server are unaffected.
