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,
--
Help improve RT by taking our user survey:
https://www.surveymonkey.com/s/N23JW9T
