[rt-users] Only some searches in RT fail (Sphinx)

Wed Jul 2 10:19:40 EDT 2014

Dear all,
I've managed to update from RT 3.8.8 to 4.2.5 (Ubuntu 14.04). So far so good. I sucessfully installed Sphinx 2.0.4 and searching works, at least almost.
It depends, on what I'm searching for. Searching for "something" (using the Simple Search), I get 3 results. I edit the search to omit the ticket status. Then I get 11 search results (since resolved tickets are also taken into account). Very well, except the fact that the ticket content is not analysed, just the subjects. All tickets containing "something in the content are not shown. On the command line, using
search -i 'rt' "something"
I get a lot more search results. So indexing seems to work.
But the main problem occurs, if I just search for "something" in the tickets' content (not the subjects). The webinterface tells me, there are 0 results. The apache error log states:

[4387] [Wed Jul  2 14:11:19 2014] [warning]: DBD::mysql::st execute failed: There was a problem processing the query on the foreign data source. Data source error: INTERNAL ERROR: 1st column must be bigint to accept 64-bit DOCID at /usr/local/share/perl/5.18.2/DBIx/SearchBuilder/Handle.pm line 589. (/usr/local/share/perl/5.18.2/DBIx/SearchBuilder/Handle.pm:589)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: RT::Handle=HASH(0x8897978) couldn't execute the query '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 = 'firmware;limit=20000;maxmatches=20000' )  )  ) ) ' at /usr/local/share/perl/5.18.2/DBIx/SearchBuilder/Handle.pm line 602.
        DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x8897978)', 'SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transac...') called at /usr/local/share/perl/5.18.2/DBIx/SearchBuilder.pm line 295
        DBIx::SearchBuilder::_DoCount('RT::Tickets=HASH(0x9311918)', 1) called at /opt/rt4/sbin/../lib/RT/SearchBuilder.pm line 994
        RT::SearchBuilder::_DoCount('RT::Tickets=HASH(0x9311918)', 1) called at /opt/rt4/sbin/../lib/RT/Tickets.pm line 2383
        RT::Tickets::_DoCount('RT::Tickets=HASH(0x9311918)', 1) called at /usr/local/share/perl/5.18.2/DBIx/SearchBuilder.pm line 1525
        DBIx::SearchBuilder::CountAll('RT::Tickets=HASH(0x9311918)') called at /opt/rt4/sbin/../lib/RT/Tickets.pm line 2293
        RT::Tickets::CountAll('RT::Tickets=HASH(0x9311918)') called at /opt/rt4/share/html/Search/Results.html line 149
        HTML::Mason::Commands::__ANON__('RowsPerPage', 50, 'OrderBy', 'id|||', 'SavedSearchId', 'new', 'Format', '\'   <b><a href="__WebPath__/Ticket/Display.html?id=__id__">_...', 'Order', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Component.pm line 138
        HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0x920ed20)', 'RowsPerPage', 50, 'OrderBy', 'id|||', 'SavedSearchId', 'new', 'Format', '\'   <b><a href="__WebPath__/Ticket/Display.html?id=__id__">_...', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1305
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1295
        HTML::Mason::Request::comp(undef, undef, undef, 'RowsPerPage', 50, 'OrderBy', 'id|||', 'SavedSearchId', 'new', ...) called at /opt/rt4/sbin/../lib/RT/Interface/Web.pm line 682
        RT::Interface::Web::ShowRequestedPage('HASH(0x8fa88a8)') called at /opt/rt4/sbin/../lib/RT/Interface/Web.pm line 370
        RT::Interface::Web::HandleRequest('HASH(0x8fa88a8)') called at /opt/rt4/share/html/autohandler line 53
        HTML::Mason::Commands::__ANON__('SavedChartSearchId', 'new', 'Query', '( ( Content LIKE \'firmware\' ) )', 'RowsPerPage', 50, 'OrderBy', 'id|||', 'SavedSearchId', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Component.pm line 138
        HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0x8f10fb0)', 'SavedChartSearchId', 'new', 'Query', '( ( Content LIKE \'firmware\' ) )', 'RowsPerPage', 50, 'OrderBy', 'id|||', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1300
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1295
        HTML::Mason::Request::comp(undef, undef, undef, 'SavedChartSearchId', 'new', 'Query', '( ( Content LIKE \'firmware\' ) )', 'RowsPerPage', 50, ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 484
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 484
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 436
        HTML::Mason::Request::exec('RT::Interface::Web::Request=HASH(0x8c5e388)') called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler.pm line 96
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler.pm line 96
        HTML::Mason::Request::PSGI::exec('RT::Interface::Web::Request=HASH(0x8c5e388)') called at /usr/local/share/perl/5.18.2/HTML/Mason/Interp.pm line 345
        HTML::Mason::Interp::exec(undef, undef, 'SavedChartSearchId', 'new', 'Query', '( ( Content LIKE \'firmware\' ) )', 'RowsPerPage', 50, 'OrderBy', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler.pm line 59
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler.pm line 59
        HTML::Mason::PSGIHandler::invoke_mason('HTML::Mason::PSGIHandler::Streamy=HASH(0x8c5df98)', 'HASH(0x9408b08)', 'HASH(0x93f4e20)') called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler/Streamy.pm line 52
        HTML::Mason::PSGIHandler::Streamy::__ANON__('CODE(0x93f6808)') called at /opt/rt4/sbin/../lib/RT/Interface/Web/Handler.pm line 313
        RT::Interface::Web::Handler::__ANON__('CODE(0x93f6808)') called at /usr/local/share/perl/5.18.2/Plack/Util.pm line 301
        Plack::Util::__ANON__('CODE(0x8db7ff8)') called at /usr/local/share/perl/5.18.2/Plack/Handler/FCGI.pm line 136
        Plack::Handler::FCGI::run('Plack::Handler::FCGI=HASH(0x8dcf220)', 'CODE(0x8e068e0)') called at /usr/local/share/perl/5.18.2/Plack/Loader.pm line 84
        Plack::Loader::run('Plack::Loader=HASH(0x8c5df20)', 'Plack::Handler::FCGI=HASH(0x8dcf220)') called at /usr/local/share/perl/5.18.2/Plack/Runner.pm line 277
        Plack::Runner::run('RT::PlackRunner=HASH(0x27ef928)') called at /opt/rt4/sbin/../lib/RT/PlackRunner.pm line 141
        eval {...} called at /opt/rt4/sbin/../lib/RT/PlackRunner.pm line 141
        RT::PlackRunner::run('RT::PlackRunner=HASH(0x27ef928)') called at /opt/rt4/sbin/rt-server.fcgi line 162 (/usr/share/perl/5.18/Carp.pm:103)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: Use of uninitialized value $num in numeric lt (<) at /usr/share/perl/5.18/Locale/Maketext.pm line 79. (/usr/share/perl/5.18/Locale/Maketext.pm:79)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: Use of uninitialized value $num in numeric gt (>) at /usr/share/perl/5.18/Locale/Maketext.pm line 79. (/usr/share/perl/5.18/Locale/Maketext.pm:79)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: Use of uninitialized value $num in int at /usr/share/perl/5.18/Locale/Maketext.pm line 79. (/usr/share/perl/5.18/Locale/Maketext.pm:79)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: Use of uninitialized value $num in numeric eq (==) at /usr/share/perl/5.18/Locale/Maketext.pm line 79. (/usr/share/perl/5.18/Locale/Maketext.pm:79)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: Use of uninitialized value $num in numeric eq (==) at /usr/share/perl/5.18/Locale/Maketext.pm line 64. (/usr/share/perl/5.18/Locale/Maketext.pm:64)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: Use of uninitialized value $ticketcount in numeric lt (<) at /opt/rt4/share/html/Search/Results.html line 201. (/opt/rt4/share/html/Search/Results.html:201)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: Use of uninitialized value $ticketcount in numeric lt (<) at /opt/rt4/share/html/Search/Results.html line 202. (/opt/rt4/share/html/Search/Results.html:202)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: DBD::mysql::st execute failed: There was a problem processing the query on the foreign data source. Data source error: INTERNAL ERROR: 1st column must be bigint to accept 64-bit DOCID at /usr/local/share/perl/5.18.2/DBIx/SearchBuilder/Handle.pm line 589. (/usr/local/share/perl/5.18.2/DBIx/SearchBuilder/Handle.pm:589)
[4387] [Wed Jul  2 14:11:19 2014] [warning]: RT::Handle=HASH(0x8897978) couldn't execute the query '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 = 'firmware;limit=20000;maxmatches=20000' )  )  ) ) ' at /usr/local/share/perl/5.18.2/DBIx/SearchBuilder/Handle.pm line 602.
        DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x8897978)', 'SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transac...') called at /usr/local/share/perl/5.18.2/DBIx/SearchBuilder.pm line 295
        DBIx::SearchBuilder::_DoCount('RT::Tickets=HASH(0x9552948)', 1) called at /opt/rt4/sbin/../lib/RT/SearchBuilder.pm line 994
        RT::SearchBuilder::_DoCount('RT::Tickets=HASH(0x9552948)', 1) called at /opt/rt4/sbin/../lib/RT/Tickets.pm line 2383
        RT::Tickets::_DoCount('RT::Tickets=HASH(0x9552948)', 1) called at /usr/local/share/perl/5.18.2/DBIx/SearchBuilder.pm line 1525
        DBIx::SearchBuilder::CountAll('RT::Tickets=HASH(0x9552948)') called at /opt/rt4/sbin/../lib/RT/Tickets.pm line 2293
        RT::Tickets::CountAll('RT::Tickets=HASH(0x9552948)') called at /opt/rt4/share/html/Elements/CollectionList line 54
        HTML::Mason::Commands::__ANON__('Query', '( ( Content LIKE \'firmware\' ) )', 'TotalFound', undef, 'AllowSorting', 1, 'OrderBy', 'id|||', 'Order', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Component.pm line 138
        HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0x92de998)', 'Query', '( ( Content LIKE \'firmware\' ) )', 'TotalFound', undef, 'AllowSorting', 1, 'OrderBy', 'id|||', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1305
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1295
        HTML::Mason::Request::comp(undef, undef, 'Query', '( ( Content LIKE \'firmware\' ) )', 'TotalFound', undef, 'AllowSorting', 1, 'OrderBy', ...) called at /opt/rt4/share/html/Search/Results.html line 63
        HTML::Mason::Commands::__ANON__('RowsPerPage', 50, 'OrderBy', 'id|||', 'SavedSearchId', 'new', 'Format', '\'   <b><a href="__WebPath__/Ticket/Display.html?id=__id__">_...', 'Order', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Component.pm line 138
        HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0x920ed20)', 'RowsPerPage', 50, 'OrderBy', 'id|||', 'SavedSearchId', 'new', 'Format', '\'   <b><a href="__WebPath__/Ticket/Display.html?id=__id__">_...', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1305
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1295
        HTML::Mason::Request::comp(undef, undef, undef, 'RowsPerPage', 50, 'OrderBy', 'id|||', 'SavedSearchId', 'new', ...) called at /opt/rt4/sbin/../lib/RT/Interface/Web.pm line 682
        RT::Interface::Web::ShowRequestedPage('HASH(0x8fa88a8)') called at /opt/rt4/sbin/../lib/RT/Interface/Web.pm line 370
        RT::Interface::Web::HandleRequest('HASH(0x8fa88a8)') called at /opt/rt4/share/html/autohandler line 53
        HTML::Mason::Commands::__ANON__('SavedChartSearchId', 'new', 'Query', '( ( Content LIKE \'firmware\' ) )', 'RowsPerPage', 50, 'OrderBy', 'id|||', 'SavedSearchId', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Component.pm line 138
        HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0x8f10fb0)', 'SavedChartSearchId', 'new', 'Query', '( ( Content LIKE \'firmware\' ) )', 'RowsPerPage', 50, 'OrderBy', 'id|||', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1300
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 1295
        HTML::Mason::Request::comp(undef, undef, undef, 'SavedChartSearchId', 'new', 'Query', '( ( Content LIKE \'firmware\' ) )', 'RowsPerPage', 50, ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 484
       eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 484
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/Request.pm line 436
        HTML::Mason::Request::exec('RT::Interface::Web::Request=HASH(0x8c5e388)') called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler.pm line 96
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler.pm line 96
        HTML::Mason::Request::PSGI::exec('RT::Interface::Web::Request=HASH(0x8c5e388)') called at /usr/local/share/perl/5.18.2/HTML/Mason/Interp.pm line 345
        HTML::Mason::Interp::exec(undef, undef, 'SavedChartSearchId', 'new', 'Query', '( ( Content LIKE \'firmware\' ) )', 'RowsPerPage', 50, 'OrderBy', ...) called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler.pm line 59
        eval {...} called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler.pm line 59
        HTML::Mason::PSGIHandler::invoke_mason('HTML::Mason::PSGIHandler::Streamy=HASH(0x8c5df98)', 'HASH(0x9408b08)', 'HASH(0x93f4e20)') called at /usr/local/share/perl/5.18.2/HTML/Mason/PSGIHandler/Streamy.pm line 52
        HTML::Mason::PSGIHandler::Streamy::__ANON__('CODE(0x93f6808)') called at /opt/rt4/sbin/../lib/RT/Interface/Web/Handler.pm line 313
        RT::Interface::Web::Handler::__ANON__('CODE(0x93f6808)') called at /usr/local/share/perl/5.18.2/Plack/Util.pm line 301
        Plack::Util::__ANON__('CODE(0x8db7ff8)') called at /usr/local/share/perl/5.18.2/Plack/Handler/FCGI.pm line 136
        Plack::Handler::FCGI::run('Plack::Handler::FCGI=HASH(0x8dcf220)', 'CODE(0x8e068e0)') called at /usr/local/share/perl/5.18.2/Plack/Loader.pm line 84
        Plack::Loader::run('Plack::Loader=HASH(0x8c5df20)', 'Plack::Handler::FCGI=HASH(0x8dcf220)') called at /usr/local/share/perl/5.18.2/Plack/Runner.pm line 277
        Plack::Runner::run('RT::PlackRunner=HASH(0x27ef928)') called at /opt/rt4/sbin/../lib/RT/PlackRunner.pm line 141
        eval {...} called at /opt/rt4/sbin/../lib/RT/PlackRunner.pm line 141
        RT::PlackRunner::run('RT::PlackRunner=HASH(0x27ef928)') called at /opt/rt4/sbin/rt-server.fcgi line 162 (/usr/share/perl/5.18/Carp.pm:103)

Does anyone knows, how to solve this problem?

Regards
Daniel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20140702/6c4e610e/attachment.htm>