<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<font face="DejaVu Sans Mono"><small>I've SQL query logging onto a
file rt.log. While running searches I'm suffixing "grep
AttachmentsIndex"<br>
since there is no much of other log data. <br>
<br>
I've noticed, for every "successful" string search(a search that
returns expected matching results)<br>
from RT webUI there are 2 sets of SQL queries performed on
'AttachmentsIndex'. One begins with<br>
'SELECT COUNT(DISTINCT main.id)...' and other 'SELECT DISTINCT
main.*...'. Please see the output below<br>
from the logs for the search query 'fulltext:uniqops2reply1'
where </small></font><font face="DejaVu Sans Mono"><small><big><font
face="DejaVu Sans Mono"><small>'uniqops2reply1'</small></font></big><i>
</i>was the only<br>
content of a particular ticket that was indexed.<br>
<i><br>
</i><i>SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN
Transactions Transactions_1 ON <br>
( Transactions_1.ObjectType = 'RT::Ticket' ) AND (
Transactions_1.ObjectId = main.id ) <br>
JOIN Attachments Attachments_2 ON (
Attachments_2.TransactionId = Transactions_1.id ) <br>
JOIN AttachmentsIndex AttachmentsIndex_3 ON (
AttachmentsIndex_3.id = Attachments_2.id ) <br>
WHERE (main.Status != 'deleted') AND ( ( (
AttachmentsIndex_3.query =
'uniqops2reply1;limit=10000;maxmatches=10000' ) ) <br>
AND ( main.Status = 'new' OR main.Status = 'open' OR
main.Status = 'stalled' ) ) AND (main.Type = 'ticket') <br>
AND (main.EffectiveId = main.id) ;
(/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1183)</i><br>
<b><br>
</b><i>SELECT DISTINCT main.* FROM Tickets main JOIN
Transactions Transactions_1 ON <br>
( Transactions_1.ObjectType = 'RT::Ticket' ) AND (
Transactions_1.ObjectId = main.id ) <br>
JOIN Attachments Attachments_2 ON (
Attachments_2.TransactionId = Transactions_1.id ) <br>
JOIN AttachmentsIndex AttachmentsIndex_3 ON (
AttachmentsIndex_3.id = Attachments_2.id ) <br>
WHERE (main.Status != 'deleted') AND ( ( (
AttachmentsIndex_3.query =
'uniqops2reply1;limit=10000;maxmatches=10000' ) ) <br>
AND ( main.Status = 'new' OR main.Status = 'open' OR
main.Status = 'stalled' ) ) <br>
AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)
ORDER BY main.id ASC <br>
LIMIT 50; (/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1183)</i><i><br>
</i></small><br>
<small>Next, I made a 2nd reply to the same ticket with the
content as 'uniqops2reply2'. I ran 'indexer' with <br>
--rotate and was successful. For clarity, I issued CLI search of
sphinx and it exactly matched <br>
the string </small></font><font face="DejaVu Sans Mono"><small>'uniqops2reply2'
in the same ticket. But on querying </small></font><font
face="DejaVu Sans Mono"><small>'fulltext:uniqops2reply2' on <br>
web-UI it failed with 0 results! I checked the logs to see that
only 1 out of the 2 SQL queries<br>
were executed i.e. only the below was seen for the failed web-UI
search:<br>
<br>
</small></font><font face="DejaVu Sans Mono"><small><i>SELECT
COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions
Transactions_1 ON <br>
( Transactions_1.ObjectType = 'RT::Ticket' ) AND (
Transactions_1.ObjectId = main.id ) <br>
JOIN Attachments Attachments_2 ON (
Attachments_2.TransactionId = Transactions_1.id ) <br>
JOIN AttachmentsIndex AttachmentsIndex_3 ON (
AttachmentsIndex_3.id = Attachments_2.id ) <br>
WHERE (main.Status != 'deleted') AND ( ( (
AttachmentsIndex_3.query =
'uniqops2reply2;limit=10000;maxmatches=10000' ) ) <br>
AND ( main.Status = 'new' OR main.Status = 'open' OR
main.Status = 'stalled' ) ) AND (main.Type = 'ticket') <br>
AND (main.EffectiveId = main.id) ;
(/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1183)<br>
<br>
</i>The </small></font><font face="DejaVu Sans Mono"><small><big><font
face="DejaVu Sans Mono"><small><i>'SELECT DISTINCT...' </i>query<i>
</i>was missing.</small></font></big> I re-ran 'indexer'
again but the same story repeated. </small></font><font
face="DejaVu Sans Mono"><small><br>
I executed this missing query by hand on the MySQL server and it
returned the matching result!<br>
<br>
Testing further, another 3rd reply was made to the same ticket
with content '</small></font><font face="DejaVu Sans Mono"><small>uniqops2reply3'.
<br>
I <b>did not </b>run 'indexer'. Then I ran the previous failed
query '</small></font><font face="DejaVu Sans Mono"><small>fulltext:uniqops2reply2'.<br>
Surprisingly it returned the results! Also on checking rt.log
both the SQL queries </small></font><br>
<font face="DejaVu Sans Mono"><small><i>'SELECT COUNT...' </i>and '</small></font><font
face="DejaVu Sans Mono"><small><i>SELECT DISTINCT...' </i>were
executed!<br>
<br>
I ran 'indexer' to index the 3rd reply. Same story; </small></font><font
face="DejaVu Sans Mono"><small>'fulltext:uniqops2reply3' failed
with 0 <br>
results and with only </small></font><font face="DejaVu Sans
Mono"><small><big><font face="DejaVu Sans Mono"><small><i>'SELECT
COUNT...'</i></small></font></big> being executed. I
created a new ticket instead <br>
of replying to the same ticket. I issued </small></font><font
face="DejaVu Sans Mono"><small>the previously failed
'fulltext:uniqops2reply3' again,<br>
but this time it returned the expected result with both SQL
queries executed. <br>
<br>
So it seems that after an update is made and is indexed, it
requires another update for the previously<br>
updated content to be available from RT's webUI search OR
putting it in more specific terms: it requires<br>
another update so that </small></font><font face="DejaVu Sans
Mono"><small><big><font face="DejaVu Sans Mono"><small>'</small></font><font
face="DejaVu Sans Mono"><small><i>SELECT DISTINCT...' </i>is
also executed on AttachmentsIndex while searching <br>
for previously indexed content. What could possibly be the
problem here?<br>
</small></font></big> <br>
Thanks,<br>
</small></font>
</body>
</html>