FTIndexes are Broken - Throwing "Query Error! [Trigger already exists]"

Overview

FTIndexes are throwing a trigger error in the log when creating new indexes:

db_mysqlxdataquery: Query Error! [Trigger already exists], err 1359

The error message (in Xinet) is "Cannot populate ftindex tables for ftindex1" (where ftindex1 is the index attempting to be created).

This error is thrown when the file_delete.TRN (or one of the other trigger files) is in the DB path in the file system. This article provides the solution for this error when attempting to create an FTIndex.

Solution

First, confirm that the file exists in the DB path. Connect to the SSH session, log in as root, and execute the following:

Navigate to the Database:

cd /XDB

Check to see that the file is in the path:

find . -name 'file_delete.TRN'

The returned result should have the following format:

./[my_directory]/file_delete.TRN

Now that it has been confirmed, remove the file:

rm -Rf file_delete.TRN

Confirm that the file was actually removed by running:

find . -name 'file_delete.TRN'

The command should not return any results to the terminal.

Testing

In order to test if this worked, attempt to create the indexes that triggered "Query Error! [Trigger already exists]". The error should not be thrown.