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

[Subin]

I've SQL query logging onto a file rt.log. While running searches I'm 
suffixing "grep AttachmentsIndex"
since there is no much of other log data.

I've noticed, for every "successful" string search(a search that returns 
expected matching results)
from RT webUI there are 2 sets of SQL queries performed on 
'AttachmentsIndex'. One begins with
'SELECT COUNT(DISTINCT main.id)...' and other 'SELECT DISTINCT 
main.*...'. Please see the output below
from the logs for the search query 'fulltext:uniqops2reply1' where 
'uniqops2reply1'//was the only
content of a particular ticket that was indexed.
/
//SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions 
Transactions_1  ON
( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( 
Transactions_1.ObjectId = main.id )
JOIN Attachments Attachments_2  ON ( Attachments_2.TransactionId = 
Transactions_1.id )
JOIN AttachmentsIndex AttachmentsIndex_3  ON ( AttachmentsIndex_3.id = 
Attachments_2.id )
WHERE (main.Status != 'deleted') AND ( (  ( AttachmentsIndex_3.query = 
'uniqops2reply1;limit=10000;maxmatches=10000' )  )
AND  ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 
'stalled' ) ) AND (main.Type = 'ticket')
AND (main.EffectiveId = main.id) ; 
(/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1183)/
*
*/SELECT DISTINCT main.* FROM Tickets main JOIN Transactions 
Transactions_1  ON
( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( 
Transactions_1.ObjectId = main.id )
JOIN Attachments Attachments_2  ON ( Attachments_2.TransactionId = 
Transactions_1.id )
JOIN AttachmentsIndex AttachmentsIndex_3  ON ( AttachmentsIndex_3.id = 
Attachments_2.id )
WHERE (main.Status != 'deleted') AND ( (  ( AttachmentsIndex_3.query = 
'uniqops2reply1;limit=10000;maxmatches=10000' )  )
AND  ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 
'stalled' ) )
AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ORDER BY 
main.id ASC
LIMIT 50; (/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1183)//
/
Next, I made a 2nd reply to the same ticket with the content as 
'uniqops2reply2'. I ran 'indexer' with
--rotate and was successful. For clarity, I issued CLI search of sphinx 
and it exactly matched
the string 'uniqops2reply2' in the same ticket. But on querying 
'fulltext:uniqops2reply2' on
web-UI it failed with 0 results! I checked the logs to see that only 1 
out of the 2 SQL queries
were executed i.e. only the below was seen for the failed web-UI search:

/SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions 
Transactions_1  ON
( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( 
Transactions_1.ObjectId = main.id )
JOIN Attachments Attachments_2  ON ( Attachments_2.TransactionId = 
Transactions_1.id )
JOIN AttachmentsIndex AttachmentsIndex_3  ON ( AttachmentsIndex_3.id = 
Attachments_2.id )
WHERE (main.Status != 'deleted') AND ( (  ( AttachmentsIndex_3.query = 
'uniqops2reply2;limit=10000;maxmatches=10000' )  )
AND  ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 
'stalled' ) ) AND (main.Type = 'ticket')
AND (main.EffectiveId = main.id) ; 
(/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1183)

/The /'SELECT DISTINCT...' /query//was missing. I re-ran 'indexer' again 
but the same story repeated.
I executed this missing query by hand on the MySQL server and it 
returned the matching result!

Testing further, another 3rd reply was made to the same ticket with 
content 'uniqops2reply3'.
I *did not *run 'indexer'. Then I ran the previous failed query 
'fulltext:uniqops2reply2'.
Surprisingly it returned the results! Also on checking rt.log both the 
SQL queries
/'SELECT COUNT...' /and '/SELECT DISTINCT...' /were executed!

I ran 'indexer' to index the 3rd reply. Same story; 
'fulltext:uniqops2reply3' failed with 0
results and with only /'SELECT COUNT...'/ being executed. I created a 
new ticket instead
of replying to the same ticket. I issued the previously failed 
'fulltext:uniqops2reply3' again,
but this time it returned the expected result with both SQL queries 
executed.

So it seems that after an update is made and is indexed, it requires 
another update for the previously
updated content to be available from RT's webUI search OR putting it in 
more specific terms: it requires
another update so that '/SELECT DISTINCT...' /is also executed on 
AttachmentsIndex while searching
for previously indexed content. What could possibly be the problem here?

Thanks,
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20130206/e6cb7d15/attachment.htm>