The AttachmentsIndex table

[ted]

Hello,

It just took 19 hours to restore a RT 4.4.4 db dump to a MariaDB 10.1.40 master-slave replication environment. Ten hours in, an “ALTER TABLE AttachmentsIndex ENABLE KEYS” query on the master changed state from “Repair by sorting” to “Repair with keycache” and the log had this warning:
[Warning] Warning: Enabling keys got errno 28 on rt4.AttachmentsIndex, retrying

QUESTION #1: Is it safe to exclude the AttachmentsIndex table from RT4 backups? Indexing adds over 70GB to the server and we don’t necessarily need the index backed-up if it can be re-created.

This post suggests removing “AttachmentsIndex” and re-running “rt-setup-fulltext-index” could be problematic, at least on Postgres:

Fulltext:word failling on upgrade to 4.4.4

Dear all. i’ve upgrade my RT server from 4.4.0 to 4.4.4. All are running well, although i’ve can’t use the simple search with “fulltext:word” as before. I’m using Postgresql as DB. I try to remove the AttachmentsIndex table in database, and run a new “rt-setup-fulltext-index” command. It complains about some string too long for the tsvector. But at end, i’ve got a index for searching. The search is correct when i’m using a “New Search”, i can define the "Content matches " creteria, and get ba…

QUESTION #2: Is there a bug with creating an InnoDB-type AttachmentsIndex table on MariaDB 10.1.x? “mysql> show engines” shows InnoDB is supported, and the docs say the table gets created as InnoDB for versions of MariaDB which support it, but it gets created as MyISAM and rt-setup-fulltext-index outputs this:

“Complete support for full-text search requires MySQL 5.6 or higher. For prior
versions such as yours, full-text indexing can either be provided using MyISAM
tables, or the external Sphinx indexer…”

Thanks.

[knation]

@ted

QUESTION #2 : Is there a bug with creating an InnoDB-type AttachmentsIndex table on MariaDB 10.1.x? “mysql> show engines” shows InnoDB is supported, and the docs say the table gets created as InnoDB for versions of MariaDB which support it , but it gets created as MyISAM and rt-setup-fulltext-index outputs this:

I see this in the code $RT::Handle->dbh->{mysql_serverversion} < 50600 which results in the message you receive:

“Complete support for full-text search requires MySQL 5.6 or higher. For prior
versions such as yours, full-text indexing can either be provided using MyISAM
ables, or the external Sphinx indexer…”

Can you confirm RT is using MariaDB 10.1.4?

[ted]

Hi knation,

Thanks for replying. Yes, we installed MariaDB 10.1.40 with the stock Ubuntu 18.04 repo package:

$ sudo apt-get install mariadb-server

MariaDB version shows:

10.1.40-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

[knation]

When you run mysql -u root which version of MySQL server does it say it is?

[ted]

$ mysql -u root -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 250
Server version: 10.1.40-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

-V shows it with ‘mysql Ver 15.1’ also:

$ sudo mysql -u root -V
mysql Ver 15.1 Distrib 10.1.40-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

MariaDB 10.1.40 is definitely the latest stable:

MariaDB Server - All releases - MariaDB.org

[knation]

I would expect

@knation

$RT::Handle->dbh->{mysql_serverversion}

to return 10140 as the version number should mean you shouldn’t enter the loop that is displaying the message you’re getting. You could try printing the version number to see what RT thinks the server version is. I tried this and it works, I just added a error log statement to the --help command to print the version number.

sub show_help {
    my $error = shift;
    RT::Logger->error($RT::Handle->dbh->{mysql_serverversion});
    RT::Interface::CLI->ShowHelp(
        ExitValue => $error,
        Sections => 'NAME|DESCRIPTION',
    );
}

[ted]

Thank you knation, that explains it! I’m getting this output:

[12877] [Tue Jul 16 16:48:40 2019] [error]: 50505 (/opt/rt4/sbin/rt-setup-fulltext-index:720)

These links explain that MariaDB has an issue with reporting the version number:

upgrade.php index warning

"MariaDB is lying about its MySQL version compatibility: they backported 5.6 MySQL’s innodb fulltext index in MariaDB 10.0.5 but they didn’t change the server_version compatibility string (still 5.5.5).

If your MariaDB version is greater that 10.0.5 it’s safe to add the fulltext index…"

There’s some discussion here about how to resolve it:
https://jira.mariadb.org/browse/MDEV-13341

For now I can update the code to explicitly allow the InnoDB creation if the version is >= 50505 too.
But I wonder if it’s smarter to leave the index as MyISAM since it adds over 70GB to the size? - I tried running rt-externalize-attachments but we only saved 2.5GB

Thank you very much!

I’m still curious if we can drop the AttachmentsIndex table from backups to save space; somebody tried and subsequent runs of rt-setup-fulltext-index didn’t update the index for old tickets; is the magic sauce simply to remove Set( %FullTextSearch… from RT_SiteConfig.pm BEFORE subsequently re-running rt-setup-fulltext-index to recreate the index from scratch?

[knation]

I think you could manually drop the AttachmentsIndex table and then re-run rt-setup-fulltext-index script, but yes comment out the table in RT_SiteConfig.pm until it exists

[ted]

Thank you very much! RT rocks!