Improved Fulltext Search (MySQL Only)

Dont create your support topics here! No new topics with questions allowed!

Moderator: crythias

Forum rules
Dont create your support topics here! No new topics with questions allowed!
Post Reply
CSL
Znuny expert
Posts: 159
Joined: 11 Nov 2011, 19:27
Znuny Version: 3.0.11

Improved Fulltext Search (MySQL Only)

Post by CSL »

Disclaimer: You MUST use MySQL for your OTRS database for this to work!

Where I work, we have built up a large catalogue of tickets that we want to be able to search quickly through for similar issues, and OTRS's fulltext search seemed at first like the ideal solution. Testing showed it to be very unreliable and unpredictable however, as it seemed to return either thousands of results or nothing at all.

I investigated the queries being run on the database and found that OTRS uses a LIKE statement for its fulltext search. This is very limiting, doesn't give any advanced operators (such as +word, -word, etc), and is slow because it does not use an index.

I came up with a solution that changes this to MySQL's native fulltext-index search, which is described here: http://dev.mysql.com/doc/refman/5.1/en/ ... earch.html

Benefits of this are:
Faster - uses a proper index, so search times are reduced
More Powerful - allows complex expressions and operators
Real-time - no need to run a script to update the index (as with StaticDB), as MySQL does this automatically


1. Add the following lines to your my.cnf file and restart MySQL:

Code: Select all

# Fulltext index perameters
ft_stopword_file = ""
ft_min_word_len=3
2. Run this command from the mysql command line as root to add the fulltext index:

Code: Select all

ALTER TABLE article ADD FULLTEXT(a_from, a_to, a_cc, a_subject, a_body);
3. Copy the perl module that needs to be changed to the /Custom directory

Code: Select all

mkdir -p /opt/otrs/Custom/Kernel/System/Ticket/ArticleSearchIndex/ 

cp /opt/otrs/Kernel/System/Ticket/ArticleSearchIndex/RuntimeDB.pm /opt/otrs/Custom/Kernel/System/Ticket/ArticleSearchIndex/
4. Modify RuntimeDB.pm to change the SQL used for the fulltext search
*EDIT (cleaned up code and fixed issue with subject/from/to etc searches)

Open /opt/otrs/Custom/Kernel/System/Ticket/ArticleSearchIndex/RuntimeDB.pm in your favourite text editor. For simplicity, you can just delete the contents of the whole file, and paste in the code below:

Code: Select all

# --
# Kernel/System/Ticket/ArticleSearchIndex/RuntimeDB.pm - article search index backend runtime
# Copyright (C) 2001-2010 xxx, http://otrs.org/
# --
# $Id: RuntimeDB.pm,v 1.12 2010/10/16 09:25:35 bes Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

package Kernel::System::Ticket::ArticleSearchIndex::RuntimeDB;

use strict;
use warnings;

use vars qw($VERSION);
$VERSION = qw($Revision: 1.12 $) [1];

sub ArticleIndexBuild {
    my ( $Self, %Param ) = @_;

    # check needed stuff
    for (qw(ArticleID UserID)) {
        if ( !$Param{$_} ) {
            $Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
            return;
        }
    }

    return 1;
}

sub ArticleIndexDelete {
    my ( $Self, %Param ) = @_;

    # check needed stuff
    for (qw(ArticleID UserID)) {
        if ( !$Param{$_} ) {
            $Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
            return;
        }
    }

    return 1;
}

sub ArticleIndexDeleteTicket {
    my ( $Self, %Param ) = @_;

    # check needed stuff
    for (qw(TicketID UserID)) {
        if ( !$Param{$_} ) {
            $Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
            return;
        }
    }

    return 1;
}

sub _ArticleIndexQuerySQL {
    my ( $Self, %Param ) = @_;

    # check needed stuff
    for (qw(Data)) {
        if ( !$Param{$_} ) {
            $Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
            return;
        }
    }

    # use also article table if required
    my $SQL    = '';
    my $SQLExt = '';
    for (
        qw(
        From To Cc Subject Body
        ArticleCreateTimeOlderMinutes ArticleCreateTimeNewerMinutes
        ArticleCreateTimeOlderDate ArticleCreateTimeNewerDate
        )
        )
    {

        if ( $Param{Data}->{$_} ) {
            $SQL    = ', article art ';
            $SQLExt = ' AND st.id = art.ticket_id';
            last;
        }
    }

    return $SQL, $SQLExt;
}

sub _ArticleIndexQuerySQLExt {
    my ( $Self, %Param ) = @_;

    # check needed stuff
    for (qw(Data)) {
        if ( !$Param{$_} ) {
            $Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
            return;
        }
    }

    my %FieldSQLMapFullText = (
        From    => 'art.a_from',
        To      => 'art.a_to',
        Cc      => 'art.a_cc',
        Subject => 'art.a_subject',
        Body    => 'art.a_body',
    );
    my $SQLExt      = '';
    my $FullTextSQL = '';


###############################################################################

    # Zero the counter
    my $checkcount = 0;

    # Loop through the hash values to see how many fields we are searching over
    for my $Key ( keys %FieldSQLMapFullText ) {
        next if !$Param{Data}->{$Key};
	$checkcount += 1;
	$FullTextSQL = lc $Self->{DBObject}->Quote( $Param{Data}->{$Key} );
    }

    # If we are searching for 5 fields or over, then we must be running
    # a fulltext search. 
    if ($checkcount >= 5) {

	$FullTextSQL = '\'' . $FullTextSQL . '\'';

	$SQLExt = ' AND MATCH(a_from, a_to, a_cc, a_subject, a_body) AGAINST( ' . $FullTextSQL . ' IN BOOLEAN MODE) ';

	# Return here. This cuts off any other search terms entered, but this 
	# is in line with stock behavour!
	return $SQLExt;
    }

    # If we aren't running a fulltext search, remove values and contuine on.
    $FullTextSQL = '';


###############################################################################


    for my $Key ( keys %FieldSQLMapFullText ) {
        next if !$Param{Data}->{$Key};

        # replace * by % for SQL like
        $Param{Data}->{$Key} =~ s/\*/%/gi;

        # check search attribute, we do not need to search for *
        next if $Param{Data}->{$Key} =~ /^\%{1,3}$/;

        if ($FullTextSQL) {
            $FullTextSQL .= ' ' . $Param{Data}->{ContentSearch} . ' ';
        }

        # check if search condition extension is used
        if ( $Param{Data}->{ConditionInline} ) {
            $FullTextSQL .= $Self->{DBObject}->QueryCondition(
                Key          => $FieldSQLMapFullText{$Key},
                Value        => $Param{Data}->{$Key},
                SearchPrefix => $Param{Data}->{ContentSearchPrefix},
                SearchSuffix => $Param{Data}->{ContentSearchSuffix},
                Extended     => 1,
            );
        }
        else {

            my $Field = $FieldSQLMapFullText{$Key};
            my $Value = $Param{Data}->{$Key};

            if ( $Param{Data}->{ContentSearchPrefix} ) {
                $Value = $Param{Data}->{ContentSearchPrefix} . $Value;
            }
            if ( $Param{Data}->{ContentSearchSuffix} ) {
                $Value .= $Param{Data}->{ContentSearchSuffix};
            }

            # replace %% by % for SQL
            $Param{Data}->{$Key} =~ s/%%/%/gi;

            # db quote
            $Value = $Self->{DBObject}->Quote( $Value, 'Like' );

            # check if database supports LIKE in large text types (in this case for body)
            if ( $Self->{DBObject}->GetDatabaseFunction('CaseInsensitive') ) {
                $FullTextSQL .= " $Field LIKE '$Value'";
            }
            elsif ( $Self->{DBObject}->GetDatabaseFunction('LcaseLikeInLargeText') ) {
                $FullTextSQL .= " LCASE($Field) LIKE LCASE('$Value')";
            }
            else {
                $FullTextSQL .= " LOWER($Field) LIKE LOWER('$Value')";
            }
        }
    }
    if ($FullTextSQL) {
        $SQLExt = ' AND (' . $FullTextSQL . ')';
    }
    return $SQLExt;
}

1;
The section between the comment blocks (####) is the only part that is different from the original, so you could copy and paste it into the file instead if you wish.

5. Re-run permissions script

Run the /opt/otrs/bin/otrs.SetPermissions.pl script with settings appropriate for your install. Examples can be found in section 7 of the INSTALL file in the OTRS directory.

6. Make sure OTRS is set to use RuntimeDB for fulltext search, and NOT StaticDB

Go to Sysconfig and navigate to:
Ticket -> Core::FulltextSearch - Ticket::SearchIndexModule

Make sure it is set to 'RuntimeDB'. If you have set it to StaticDB deliberately (to try to improve fulltext search speed) then it is safe to switch it back to RuntimeDB.

7. Try it out!

I've set the SQL command to use 'IN BOOLEAN MODE', which gives you these operators to use: http://dev.mysql.com/doc/refman/5.1/en/ ... olean.html

Searches with double-quotes will return EXACTLY what you put between them. So if you search for "any" it will only find every instance of the word 'any' on it's own. It will not find 'anyone' 'anywhere' 'toany' etc. If you want these, include them in quotes as well - "any" "anyone" "anywhere"

Same for plurals - "computer" will not find 'computers'. If you want both, put them both in - "computer" "computers", or just search for computer*

I've found this to be far more accurate and useful than the default LIKE search. It would be great to hear from anyone who tries this, and what their own thoughts on it are.
Backend: OTRS 3.0.11 RedHat Enterprise Linux 6.2, Apache, MySQL with replication
Frontend: OTRS 3.0.11 RedHat Enterprise Linux 6.2 with SELinux, Apache SSL
evris
Znuny newbie
Posts: 2
Joined: 08 Feb 2013, 17:18
Znuny Version: 3.1.12
Real Name: Evripidis
Company: Evripidis

Re: Improved Fulltext Search (MySQL Only)

Post by evris »

Hello,
I (think) did the patch you are describing, but then using Agent Search searching Fulltext for "someword1 anotherword2" i get nothing. And in logs i get:

Unknown column 'a_from' in 'where clause', SQL: 'SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id AND st.id = art.ticket_id WHERE 1=1 AND sq.group_id IN (1, 2, 3, 4, 4) AND MATCH(a_from, a_to, a_cc, a_subject, a_body) AGAINST( 'someword1 anotherword2' IN BOOLEAN MODE) ORDER BY st.create_time_unix DESC LIMIT 2000'


So, i know deep inside it should my fault.... but have got any idea what it may have gone wrong ?


mysql> desc article;
+------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| ticket_id | bigint(20) | NO | MUL | NULL | |
| article_type_id | smallint(6) | NO | MUL | NULL | |
| article_sender_type_id | smallint(6) | NO | MUL | NULL | |
| a_from | text | YES | MUL | NULL | |
| a_reply_to | text | YES | | NULL | |
| a_to | text | YES | | NULL | |
| a_cc | text | YES | | NULL | |
| a_subject | text | YES | | NULL | |
| a_message_id | text | YES | MUL | NULL | |
| a_in_reply_to | text | YES | | NULL | |
| a_references | text | YES | | NULL | |
| a_content_type | varchar(250) | YES | | NULL | |
| a_body | mediumtext | NO | | NULL | |
| incoming_time | int(11) | NO | | NULL | |
| content_path | varchar(250) | YES | | NULL | |
| valid_id | smallint(6) | NO | MUL | NULL | |
| create_time | datetime | NO | | NULL | |
| create_by | int(11) | NO | MUL | NULL | |
| change_time | datetime | NO | | NULL | |
| change_by | int(11) | NO | MUL | NULL | |
+------------------------+--------------+------+-----+---------+----------------+

Thanks for your time!
otrs 3.1.2
CSL
Znuny expert
Posts: 159
Joined: 11 Nov 2011, 19:27
Znuny Version: 3.0.11

Re: Improved Fulltext Search (MySQL Only)

Post by CSL »

Hi, glad to see someone giving it a shot,

The query you should see if the modifications are successful is something like this:

SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st, queue sq , article art WHERE sq.id = st.queue_id AND st.id = art.ticket_id AND sq.group_id IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 11, 12, 15, 16, 17, 18, 19, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63) AND MATCH(a_from, a_to, a_cc, a_subject, a_body) AGAINST( 'someword1 anotherword2' IN BOOLEAN MODE) ORDER BY st.create_time_unix DESC LIMIT 4000

To better troubleshoot this, I would turn on the MySQL general log by adding something like this to your my.cnf file:
# Turn on general log
general-log=1
general_log_file=/var/log/mysql/general.log

Then you will be able to see all of the commands OTRS is running. An easy way to find the one you are interested in is to grep the logfile for the search terms you entered (so grep "someword1" ./general.log ).

The query you have looks pretty strange, to be honest. I've not seen that form of SQL in OTRS, especially the part with "WHERE 1=1" (which is a redundant clause that always equals true). I'm not sure how your install generated that query, unless they've changed something radically in the latest version.

Without knowing more, like exactly what files you modified, I think it may be best to undo your changes and have another try from scratch. In my instructions I wasn't as exact about how to modify the /opt/otrs/Custom/Kernel/System/Ticket/ArticleSearchIndex/RuntimeDB.pm file as I could have been. All you need to do is first of all find this part of the file:

Code: Select all

    my %FieldSQLMapFullText = (
        From    => 'art.a_from',
        To      => 'art.a_to',
        Cc      => 'art.a_cc',
        Subject => 'art.a_subject',
        Body    => 'art.a_body',
    );
    my $SQLExt      = '';
    my $FullTextSQL = '';
And then copy / paste this new section directly below it:

Code: Select all

###############################################################################

    # Zero the counter
    my $checkcount = 0;

    # Loop through the hash values to see how many fields we are searching over
    for my $Key ( keys %FieldSQLMapFullText ) {
        next if !$Param{Data}->{$Key};
   $checkcount += 1;
   $FullTextSQL = lc $Self->{DBObject}->Quote( $Param{Data}->{$Key} );
    }

    # If we are searching for 5 fields or over, then we must be running
    # a fulltext search.
    if ($checkcount >= 5) {

   $FullTextSQL = '\'' . $FullTextSQL . '\'';

   $SQLExt = ' AND MATCH(a_from, a_to, a_cc, a_subject, a_body) AGAINST( ' . $FullTextSQL . ' IN BOOLEAN MODE) ';

   # Return here. This cuts off any other search terms entered, but this
   # is in line with stock behaviour!
   return $SQLExt;
    }

    # If we aren't running a fulltext search, remove values and continue on.
    $FullTextSQL = '';


###############################################################################
That should work for you if the other steps are also completed. Make sure to check that 'RuntimeDB' is selected as your search index in sysconfig so that the file is used. Hope this helps.
Backend: OTRS 3.0.11 RedHat Enterprise Linux 6.2, Apache, MySQL with replication
Frontend: OTRS 3.0.11 RedHat Enterprise Linux 6.2 with SELinux, Apache SSL
evris
Znuny newbie
Posts: 2
Joined: 08 Feb 2013, 17:18
Znuny Version: 3.1.12
Real Name: Evripidis
Company: Evripidis

Re: Improved Fulltext Search (MySQL Only)

Post by evris »

Thanks man, did exactly that. Deleted the old file RuntimeDB.pm and followed your instructions for patching it. Worked smoothly like a baby's bottom.
I don't know if I am happy... I had already decided that noone was going to answer so I wrote a small PHP external file, something like a search page that did the fullsearch by accessing the DB, returning the result set and by clicking on the ID it would redirect me back to OTRS View Ticket. Then I thought, man I am so smart.... and now I threw it all together, because it is useless.
So, thanks again!
otrs 3.1.2
sallami
Znuny newbie
Posts: 12
Joined: 17 Feb 2015, 14:31
Znuny Version: 4.0.0

Re: Improved Fulltext Search (MySQL Only)

Post by sallami »

this doen't work with OTRS4 because the table are in InnoDB mode. so I can't add fulltext indexing.
Lagjaogale
Znuny newbie
Posts: 1
Joined: 23 Apr 2015, 07:00
Znuny Version: aaaaaaaaa
Real Name: aaaaaaaaa
Company: aaaaaaaaa

Re: Improved Fulltext Search (MySQL Only)

Post by Lagjaogale »

Hello,
ok thks for the howtos.

So simply in fact you test the subject then reinject the status, right ?

where in sysconfig to enable ????
Post Reply