[rt-users] Rt search does not work with sphinx main+delta index

[Guadagnino Cristiano]

I am having trouble with RT fulltext searches. I am using RT 4.0.17 with Mysql 
and Sphinx 4.0.8.
Sphinx is configured to use a main+delta index (configuration attached). Main 
index is created once every morning, while the delta index is created every 5 
minutes.
If I search using the "search" command line tool from Sphinx everything is 
correct.

Fulltext search with RT 4.0.10 instead seemed to return only the results from 
the main index, disregarding anything from the delta index.
So I updated to the latest RT release (4.0.17) just to be sure. Now fulltext 
search with RT does not return anything at all!!

Example from the CLI:

search CrevalPublic --rsort=date -q -l 500

This returns 73 hits on the main index and 39 ihits on the delta index.

Simple search with RT:

fulltext:CrevalPublic any

This returns 0 results.

Any suggestions?

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


Nota di riservatezza: Il presente messaggio non è di natura personale ma 
inviato per esigenze lavorative; l’eventuale messaggio di risposta potrà essere 
conosciuto anche da altri soggetti diversi dall’originatore di questo messaggio 
per dette esigenze o per controllo aziendale. Questo messaggio, corredato dei 
relativi allegati, contiene informazioni da considerarsi strettamente 
riservate, ed è destinato esclusivamente al destinatario sopra indicato, il 
quale è l'unico autorizzato ad usarlo, copiarlo e, sotto la propria 
responsabilità, diffonderlo. Chiunque ricevesse questo messaggio per errore o 
comunque lo leggesse senza esserne legittimato è avvertito che trattenerlo, 
copiarlo, divulgarlo, distribuirlo a persone diverse dal destinatario è 
severamente proibito, ed è pregato di rinviarlo immediatamente al mittente 
distruggendone l'originale.

source rt {
    type            = mysql

    sql_host        = localhost
    sql_db          = rt4
    sql_user        = <amended>
    sql_pass        = <amended>

    sql_query_pre   = SET NAMES utf8

    sql_query       = \
        select  A.id, \
                A.Content, \
                TK.Subject as Subject, \
                TK.id as Ticket, \
                TK.Creator as CreatedBy, \
                TK.Queue as Queue, \
                unix_timestamp(convert_tz(TK.Created,'GMT','Europe/Rome')) as 
Created \
           from Tickets TK, Transactions T, Attachments A \
          where ((T.ObjectId = TK.id) or (T.ObjectId is null)) \
            and A.TransactionId = T.id \
            and T.ObjectType = 'RT::Ticket' \
            and A.ContentType = 'text/plain' \
            and A.Content is not null \
            and TK.Created < concat(year(now()),'-',month(now()),'-01') \
            and TK.Status != 'deleted'

        sql_attr_timestamp      = Created
        sql_attr_uint           = Queue
        sql_attr_uint           = CreatedBy
        sql_attr_uint           = Ticket

    sql_query_info  = SELECT * FROM Attachments WHERE id=$id
}

source rtdelta : rt
{
    sql_query       = \
        select  A.id, \
                A.Content, \
                TK.Subject as Subject, \
                TK.id as Ticket, \
                TK.Creator as CreatedBy, \
                TK.Queue as Queue, \
                unix_timestamp(convert_tz(TK.Created,'GMT','Europe/Rome')) as 
Created \
           from Tickets TK, Transactions T, Attachments A \
          where ((T.ObjectId = TK.id) or (T.ObjectId is null)) \
            and A.TransactionId = T.id \
            and T.ObjectType = 'RT::Ticket' \
            and A.ContentType = 'text/plain' \
            and A.Content is not null \
            and TK.Created between concat(year(now()),'-',month(now()),'-01') 
and last_day(now()) \
            and TK.Status != 'deleted'
}


index rt {
    source                  = rt
    path                    = /var/sphinx/index
    docinfo                 = extern
    charset_type            = utf-8
    min_infix_len           = 3
    enable_star             = 1
}

index rtdelta: rt
{
    source                  = rtdelta
    path                    = /var/sphinx/indexdelta
    charset_type            = utf-8
    min_infix_len           = 3
    enable_star             = 1
}

indexer {
    mem_limit               = 256M
}

searchd {
    port                    = 3312
    log                     = /var/sphinx/searchd.log
    query_log               = /var/sphinx/query.log
    read_timeout            = 5
    max_children            = 30
    pid_file                = /var/sphinx/searchd.pid
    max_matches             = 10000
    seamless_rotate         = 1
    preopen_indexes         = 0
    unlink_old              = 1
    workers                 = threads
    binlog_path             = /var/sphinx/
    compat_sphinxql_magics  = 0
}