.... the second query isn't run when the first returns 0.
This hint helped narrow down things. I figured out the pattern/situation under which search failure occurs, which is:
"If I create/update a ticket and immediately perform fulltext: search of the same content before reindexing is run(on delta), it will fail to show up on fulltext searches even after the content is later reindexed. In this situation if I run a 'search' from CLI(on delta index specifically) it returns successful hits. Running SELECT COUNT by hand also returns >0 integer at this point. If I do not search for newly created ticket-content before its reindexed, this won't occur. Things are normal in that case." Yes, it is bit awkward but that is the pattern I observe after 8-9 hit and trials ! I'll try clarify things in steps for you: 1. Created a new ticket. Subject - uniqsupport1 , Content - uniqsupport1tic. So I'll be searching for this 1 word content from web end. 2. Searched "fulltext:uniqsupport1tic" from RT's web end. Yes obviously it didn't return a result. 3. Executed SELECT COUNT query that I got from the logs for this search and it returned 0. All as expected. 4. Indexed delta index (rt_delta) with --rotate. From the command line, I did "search -i rt_delta uniqsupport1tic". Got a matching result ticket as expected. I executed the same SELECT COUNT query from Step 2 by hand and it returned 1. 5. I issued "fulltext:uniqsupport1tic" for 2nd time from RT's web end. Surprisingly, 0 matching result. Observing rt.log I find that only the SELECT COUNT was being executed in response to this 2nd time fulltext search. This was happening when the same SELECT COUNT returned 1 for the search of the same ticket content 'uniqsupport1tic'. After Step 5, I reindexed delta again but proved no use. In this situation I created a "new" ticket with some random content. I ran the previous failed search again (fulltext:uniqsupport1tic) for 3rd time and this time, it returned the exact matching result. On checking the logs, the 2 SQL queries were also executed consecutively. This also worked if I replied to ANY existing ticket instead of creating a new one! The interesting fact here is that had I NOT done 'Step 2' after updating or creating a ticket, this whole issue don't exist and I get exact matching results from fulltext search "without creating or updating a ticket". Yes. I redid Steps 1-5 with a new ticket "skipping only Step 2" and in the 5th step I got the exact matching result. Thanks, -- Help improve RT by taking our user survey: https://www.surveymonkey.com/s/N23JW9T
