[rt-users] ticket content not displaying via RT's web 'Search' but shows up with sphinx's 'search' binary

[Subin]

On Friday 08 February 2013 12:20 AM, Thomas Sibley wrote:
> On 02/07/2013 03:13 AM, Subin wrote:
>>> .... 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."
> Thanks for your detailed investigation and description (which I've
> trimmed from below).  It sounds very much like you're hitting a cache on
> the SELECT COUNT query that isn't expired until you create a new ticket
> (or presumably after some longer amount of time).  RT doesn't cache
> database queries this way, so I suspect it's MySQL's standard query
> cache or, perhaps more likely, some cache in the Sphinx engine for MySQL
> (not the sphinxd server itself).  Modifying one of the tables involved
> in the query, as you do by creating a new ticket or replying to an
> existing ticket, then triggers the cache expiry.
>
> If you're interested in further tracking this down, I'd investigate
> caching at the MySQL and Sphinx level.  However, it may be moot in a
> production environment where the caching is beneficial and there's
> enough database activity to keep it from being noticeably stale.
>
>
Spot on! It was the mysql query cache that was responsible.
I disabled it and search just works fine without need to update
tables. All along I thought RT did some caching in this situation.

Well I guess this query caching can be left ON anyway since on
a busy production environment the likeliness of the cache to remain
state is very less.

Thank you for your effort and continued support.