| Bug #86378 | fulltext search fails to return results shortly after matching rows modified | ||
|---|---|---|---|
| Submitted: | 18 May 2017 21:11 | Modified: | 19 Jun 2017 1:10 |
| Reporter: | teo teo | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S1 (Critical) |
| Version: | OS: | Debian | |
| Assigned to: | CPU Architecture: | Any | |
[18 May 2017 21:52]
MySQL Verification Team
Thank you for the bug report. Please fill the Version field and provide a repeatable test case script with create table, insert data, query with real result and expected one. Thanks.
[18 May 2017 22:01]
teo teo
I obviously can't provide a repeatable test case, as this happens randomly and I don't know what triggers it. Also it seems likely that it only surfaces with huge tables, perhaps under high load. I forgot to mention (though I think it was quite implicit) that after a while, the same query returns the expected result. The version is 5.6.20
[19 May 2017 1:10]
MySQL Verification Team
Thank you for the feedback. If you are not able to provide a repeatable test case how we can expect something to verify?. Also your server is quite older 5.6.20 current version is 5.6.36 so please try it. Thanks.
[20 Jun 2017 1:00]
Bugs System
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".
Description: I have a query like this: SELECT * FROM thread AS thread WHERE MATCH(thread.title) AGAINST ('+XXXXXXXXX*' IN BOOLEAN MODE) ORDER BY lastpost DESC LIMIT 300 The table "thread" has a fulltext index on the "title" column, and there are a couple of rows with the word XXXXXXXXX in the value of that column. Usually this works fine. However, a few times I have observed that, if the query is performed a few minutes after the matching rows have been modified, no result is returned, even though the rows contained the word both before and after being modified. The documentation says NOTHING about any kind of issues related to the time it takes to update fulltext indexes when a row is modified, and even if that was the case, you would expect the query to wait for a lock and take slightly longer (in the order of milliseconds), but certainly not to return a wrong result, especially when the word was already present in the row before it being updated. How to repeat: I have a query like this: SELECT * FROM thread AS thread WHERE MATCH(thread.title) AGAINST ('+XXXXXXXXX*' IN BOOLEAN MODE) ORDER BY lastpost DESC LIMIT 300 The table "thread" has a fulltext index on the "title" column, and there are a couple of rows with the word XXXXXXXXX in the value of that column. Usually this works fine. However, a few times I have observed that, if the query is performed a few minutes after the matching rows have been modified, no result is returned, even though the rows contained the word both before and after being modified. The documentation says NOTHING about any kind of issues related to the time it takes to update fulltext indexes when a row is modified, and even if that was the case, you would expect the query to wait for a lock and take slightly longer (in the order of milliseconds), but certainly not to return a wrong result, especially when the word was already present in the row before it being updated.