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

[digorgonzola]

Morning. Should have provided more detail for you.

Here is the ticket SQL for my search:

"( Content LIKE 'sace' ) AND ( Status = 'new' OR Status = 'open' OR Status =
'stalled' )"

I then piped this into the perl command as per your instructions:

 perl -I/opt/rt4/lib -MRT=-init -le '$t=RT::Tickets->new(RT->SystemUser);
$t->FromSQL("@ARGV"); print $t->BuildSelectQuery;' \ "Content LIKE 'sace'
AND (  Status = 'new' OR Status = 'open' OR Status = 'stalled' )"

Which gave me the following SQL query:

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.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type =
'ticket') AND ( ( AttachmentsIndex_3.query =
'sace;limit=10000;maxmatches=10000' )  AND  ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) )

I put this query into MySQL with "explain" on the front as such:

Mariadb [(rt4)]> explain 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.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type =
'ticket') AND ( ( AttachmentsIndex_3.query =
'sace;limit=10000;maxmatches=10000' )  AND  ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) );

Any clues in there?

I turned transaction logging on and syslog doesn't show any errors:

May 14 23:20:54 localhost RT: [4994] SQL(0.001437s): 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.IsMerged IS NULL)
AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND ( (  (
AttachmentsIndex_3.query = 'sace;limit=10000;maxmatches=10000' )  )  AND  (
main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' ) ) ;
(/opt/rt/rt4-mysql/sbin/../lib/RT/Interface/Web.pm:1292)



--
View this message in context: http://requesttracker.8502.n7.nabble.com/ticket-content-not-displaying-via-RT-s-web-Search-but-shows-up-with-sphinx-s-search-binary-tp52602p57378.html
Sent from the Request Tracker - User mailing list archive at Nabble.com.