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

Alex Vandiver alexmv at bestpractical.com
Tue May 13 11:11:17 EDT 2014

On 05/13/2014 10:39 AM, digorgonzola wrote:
> Thanks for posting so much detail on your problem.
> I thought that this might be the answer for me as I am experiencing exactly
> the same issue!
> I'm running rt 4.2.3 with MariaDB and Sphinx.
> Searching ticket content from bash using the "search" command returns plenty
> of results but the web search function returns 0!
> I've enabled the statement logging as suggested and disable query caching to
> see if this might make a difference but to no avail.

I expect you're running into one of the situations where MySQL chooses
to not use the magic index that causes it to talk to the Sphinx indexer,
and instead looks at the contents of the AttachmentsIndex table
row-by-row -- which is empty.  You can determine if this is the case by
first determining the SQL your query produces; first, go to the
"Advanced" tab in RT, and copy the TicketSQL you're running.

Then, from the command line, feed it to the following snippet to show
what RT is generating for the database; adjust the last line to match
your TicketSQL query:

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

Take the SQL that generates, and feed it to MySQL, prefixing with EXPLAIN:

          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
                 = 'moose;limit=10000;maxmatches=10000' ) );

This will return something like (omitting most columns for brevity):

    | table              | key     |
    | AttachmentsIndex_3 | query   |
    | Attachments_2      | PRIMARY |
    | Transactions_1     | PRIMARY |
    | main               | NULL    |

If the AttachmentsIndex table does _not_ list "query" as the chosen key,
then MySQL's query planner has decided to not use the magic Sphinx index.

> I'm struggling to work out what my next step would be and if it wasn't so
> hard I'd be tempted to migrate over to PostgreSQL (that seems even more hard
> work).
> Do you guys have any suggestions?

If you were on plain MySQL, and not MariaDB, I'd suggest the new
4.2/mysql-native-fts branch that is currently in testing, as described
at http://gossamer-threads.com/lists/rt/devel/122451  I do not know how
it will work in the context of MariaDB, however.

 - Alex

More information about the rt-users mailing list