Possible bug in fulltext search with mysql and sphinx

RT Users
1

Hi all,
I am running RT 4.0.10 oh a CentOS server (64bit).
I have set up full text search with mysql and sphinx 2.0.7.

I was able to search using the “search” tool from sphinx, but whenever I tried a search in RT I got 0 results.

Looking in the RT log I found this error:
“ERROR 1430: 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”

After a lot of research, I dropped and recreated the AttachmentsIndex table, changing the Id field from int(10) to bigint.
Now search in RT works correctly.

Hope this helps.
Bye
Cristiano
Cristiano Guadagnino
Servizio Data Administration
Bankadati S.I.
Gruppo Credito Valtellinese
Tel. +39-0342-522172

2

Hi,

It’s good that you’ve figured out problem yourself. Your description is
complete enough to file a bug report, so it doesn’t disappear in the
mailing list. Will you file a bug report?On Mon, Apr 15, 2013 at 2:02 PM, Guadagnino Cristiano < guadagnino.cristiano@creval.it> wrote:

Hi all,
I am running RT 4.0.10 oh a CentOS server (64bit).
I have set up full text search with mysql and sphinx 2.0.7.

I was able to search using the “search” tool from sphinx, but whenever I
tried a search in RT I got 0 results.

Looking in the RT log I found this error:

“ERROR 1430: 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”

After a lot of research, I dropped and recreated the AttachmentsIndex
table, changing the Id field from int(10) to bigint.
Now search in RT works correctly.

Hope this helps.
Bye
Cristiano

Cristiano Guadagnino
Servizio Data Administration
Bankadati S.I.
Gruppo Credito Valtellinese
Tel. +39-0342-522172

Best regards, Ruslan.

3

Hello,

Looking in the RT log I found this error:
“ERROR 1430: 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”

The problem I think is not so much in RT as it is in Sphinx. When building
Sphinx binary, you can choose how long the document identifiers should be -
they can be either 32-bit or 64-bit if --enable-id64 was specified.
Depending on this setting Sphinx simply wants either 4 or 8 byte integers on
input to match the internal type. It simply lacks some flexibility to accept
shorter types. You can maintain compatibility with the original RT database
by re-building Sphinx without --enable-id64.

Maciek

4

Hi Rus,

I think that you should warn this issue and show the alternative when you run rt-setup-fulltext-index.

I just installed rt 4.2.0 in a CentOS 6.4 (64bits) with MariaDB (which it’s already supporting Sphinx ) and I had the same issue.

Cheers,
CarlosEl 15/04/2013, a las 20:20, Maciej Dobrzanski escribió:

Hello,

Looking in the RT log I found this error:
“ERROR 1430: 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”

The problem I think is not so much in RT as it is in Sphinx. When building
Sphinx binary, you can choose how long the document identifiers should be -
they can be either 32-bit or 64-bit if --enable-id64 was specified.
Depending on this setting Sphinx simply wants either 4 or 8 byte integers on
input to match the internal type. It simply lacks some flexibility to accept
shorter types. You can maintain compatibility with the original RT database
by re-building Sphinx without --enable-id64.

Maciek

Carlos Fuentes Bermejo
Security Engineer. IRIS-CERT
RedIRIS / Red.es

Tel.: +34 91 212 76 20 (Ext. 5583)
Mobile: + 34 675 54 41 69
E-Mail: carlos.fuentes@rediris.es
PGP Key ID: 0xF20D4295 2004-06-24
Fingerprint: ED02 A355 71B6 702A 5907 2571 8C2C E601 F20D 4295

http:://www.rediris.es/cert

signature.asc (203 Bytes)