User:Hef/rt search

Log into mariadb as root and run INSTALL SONAME 'ha_sphinx'; then show engines; to confirm it worked

Create Table

CREATE TABLE AttachmentsIndex
(
    id          BIGINT NOT NULL,
    weight      INTEGER NOT NULL,
    query       VARCHAR(3072) NOT NULL,
    group_id    INTEGER,
    INDEX(query)
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/rt" CHARACTER SET utf8;

More output

I added The following to RT_SiteConfig.pm

Set( %FullTextSearch,

   Enable     => 1,
   Indexed    => 1,
   MaxMatches => '10000',
   Table      => 'AttachmentsIndex',

);

Below is a simple Sphinx configuration which can be used to index all text/plain attachments in your database. This configuration is not ideal; you should read the Sphinx documentation to understand how to configure it to better suit your needs.

source rt {

   type            = mysql

   sql_host        = localhost
   sql_db          = rt4
   sql_user        = rt_user
   sql_pass        = rt_pass

   sql_query_pre   = SET NAMES utf8
   sql_query       = \
       SELECT a.id, a.content FROM Attachments a \
       JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \
       JOIN Tickets t ON txn.ObjectId = t.id \
       WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted'

   sql_query_info  = SELECT * FROM Attachments WHERE id=$id

}

index rt {
    source                  = rt
    path                    = /opt/rt4/var/sphinx/index
    docinfo                 = extern
    charset_type            = utf-8
}

indexer {
    mem_limit               = 32M
}

searchd {
    port                    = 9312
    log                     = /opt/rt4/var/sphinx/searchd.log
    query_log               = /opt/rt4/var/sphinx/query.log
    read_timeout            = 5
    max_children            = 30
    pid_file                = /opt/rt4/var/sphinx/searchd.pid
    max_matches             = 10000
    seamless_rotate         = 1
    preopen_indexes         = 0
    unlink_old              = 1
}

Prime things

   sudo groupadd sphinx
   sudo useradd -g sphinx -d /usr/share/sphinx/ -s /bin/false sphinx
   sudo chown -R sphinx:sphinx /var/lib/sphinx/
   sudo -u sphinx indexer --all
   sudo systemctl start sphinx