Only some searches in RT fail (Sphinx)

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’, ''
…', ‘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’, ''
…’, …) 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’, ''
…', ‘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’, ''
…’, …) 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

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)

Does anyone knows, how to solve this problem?

Replace the id column of the AttachmentsIndex table with a BIGINT
column. Fix included in the upcoming 4.2.6rc1

-kevin

Thanks a lot for pointing me to this commit:

This solved my problem. Now the searches run without errors. But I have another problem related to the sphinx search.
If I search for tickets within the RT with these conditions:

Subject LIKE ‘something’
OR Content LIKE ‘something’

I get 11 search results. Then I narrow the search condition to

Content LIKE ‘something’

and I get 242 results.
I think this is not possible, is it? For the search term I’ve used, 242 matching tickets seems reasonable.

Regards
Daniel-----Ursprüngliche Nachricht-----
Von: rt-users [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von Kevin Falcone
Gesendet: Mittwoch, 2. Juli 2014 20:46
An: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] Only some searches in RT fail (Sphinx)

On Wed, Jul 02, 2014 at 02:19:40PM +0000, Daniel Eiteneuer wrote:

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)

Does anyone knows, how to solve this problem?

Replace the id column of the AttachmentsIndex table with a BIGINT column. Fix included in the upcoming 4.2.6rc1

-kevin