We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL 5.5.35. Management is now asking me to enable full text searching on the sites so we can search inside the body of the tickets. From what I’ve read, our current configuration won’t support it due to MySQL 5.5.x not supporting it natively. It looks like I have two options:
Migrate our databases from MySQL 5.5.x to Postgres SQL to get the full text index functionality
Create a new MySQL Server that has the Sphinx add-on running & restore my databases into that.
Has anyone had to do this before? Suggestions? Warnings?
Stephen J. Cena
Systems Administrator - MIS/IT Dept
Quality Vision International
850 Hudson Ave
Rochester,NY 14620
Phone: 585-544-0450 x300
We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL
5.5.35. Management is now asking me to enable full text searching on the
sites so we can search inside the body of the tickets. From what I’ve
read, our current configuration won’t support it due to MySQL 5.5.x not
supporting it natively.
MySQL has supported fulltext indexes since version 5.0. What makes you
think otherwise?
We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL
5.5.35. Management is now asking me to enable full text searching on the
sites so we can search inside the body of the tickets. From what I’ve
read, our current configuration won’t support it due to MySQL 5.5.x not
supporting it natively.
MySQL has supported fulltext indexes since version 5.0. What makes you
think otherwise?
It supported InnoDB Full Text indexing in 5.0?
As was posted quite recently, here’s a decent recap of the technical
hurdles in using native 5.6 InnoDB indexing as it finally reaches
maturity.
We’ve come up with some possible workarounds for this, but I’m not
sure when we’ll have engineering resources to pursue them.
Stephen - if you’d like to remain on a packaged “mysql” platform,
several people have reported success using mariadb which ships with a
sphinx plugin (there’s some small shenanigans because packaged sphinx ships
with bigids requiring bigint ids in RT, but that’s documented in the
list archives).
I also know that users have built 12.04 mysql/sphinx debs (we’re still
running on an older LTS version of Ubuntu).
On 13/03/2014 14:00, Cena, Stephen (ext. 300) wrote:
We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL
5.5.35. Management is now asking me to enable full text searching on
the sites so we can search inside the body of the tickets. From what
I’ve read, our current configuration won’t support it due to MySQL
5.5.x not supporting it natively.
MySQL has supported fulltext indexes since version 5.0. What makes you think otherwise?
We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL
5.5.35. Management is now asking me to enable full text searching on the
sites so we can search inside the body of the tickets. From what I’ve
read, our current configuration won’t support it due to MySQL 5.5.x not
supporting it natively.
MySQL has supported fulltext indexes since version 5.0. What makes you
think otherwise?
It supported InnoDB Full Text indexing in 5.0?
No; you need to use MyISAM until 5.6.
But that should not be an issue here; I know that RT uses InnoDB by
default but there should be no reason why the table you want to search
can’t be converted to MyISAM.
We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL
5.5.35. Management is now asking me to enable full text searching on the
sites so we can search inside the body of the tickets. From what I’ve
read, our current configuration won’t support it due to MySQL 5.5.x not
supporting it natively.
MySQL has supported fulltext indexes since version 5.0. What makes you
think otherwise?
It supported InnoDB Full Text indexing in 5.0?
No; you need to use MyISAM until 5.6.
But that should not be an issue here; I know that RT uses InnoDB by
default but there should be no reason why the table you want to
search can’t be converted to MyISAM.
Mark
Hi Mark,
I do not use MySQL here, but the table that is to be indexed can be
very large and I certainly would not want to wait for a myisamchk to
complete after a crash. The sphinx option is the only viable one for
use with mysql. Moving to PostgreSQL would work but as suggested by
others, if your expertise is with MySQL, stick with MySQL.
I know that RT uses InnoDB by default but there should be no reason why
the table you want to search can’t be converted to MyISAM.
There’s a very straightforward reason why RT uses InnoDB: database-level
transactions. Converting the Attachments table to MyISAM would break a
notable number of internals.
Cena, Stephen (ext. 300) ha scritto:
We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL 5.5.35. Management is now asking me to enable full text searching on the sites so we can search inside the body of the tickets. From what I’ve read, our current configuration won’t support it due to MySQL 5.5.x not supporting it natively. It looks like I have two options:
Migrate our databases from MySQL 5.5.x to Postgres SQL to get the full text index functionality
Create a new MySQL Server that has the Sphinx add-on running & restore my databases into that.
Has anyone had to do this before? Suggestions? Warnings?
Stephen,
we initially (RT 3.8.x) wrote an extension to use Sphinx without the need to integrate it into mysql. You can find it on Wikia, but I would not encourage you to use it (and it will not work in newer versions of RT).
When migrating to RT 4.0.x we tried the migration to Postgres. The migration was successful, but we were disappointed with the performance of postgres when doing full-text searches. I guess we were too used to the stellar performance of Sphinx.
Finally we settled with mysql+sphinxSE, and we are very happy with it.
Bye
Cris
Cristiano Guadagnino
Servizio Data Administration
Bankadati Servizi Informatici Soc.Cons.P.A.
Gruppo bancario Credito Valtellinese
Via Trento, 22 - 23100 SONDRIO
tel +39 0342522172 - fax +39 0342522992
guadagnino.cristiano@creval.it
www.creval.ithttp://www.creval.it
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.
Referring to: rt-users Digest, Vol 120, Issue 13, articles 8,9,10,11,13,14,15:
Thanks to those who responded. First, no; I did not know that full text indexing was supported after 5.0. I thought that was a 5.6 up & coming feature. I must not have a full grasp of how it works, because if MySQL has it built in why would I need to add on Sphinx? I do have much more familiarity with MySQL over Postgres which is one reason to stay with it. I’ve never used Postgres before. I think Kevin’s answer might be the easiest. I tried swapping out for MariaDB before, but got tripped up over table names being case sensitive. I’m trying to keep everything as simple as possible & I’d rather not dive into building my own MySQL.
I’ll look into this some more and see what I can come up with. Thanks again to those who answered!
Stephen J. Cena
Systems Administrator - MIS/IT Dept
Quality Vision International
850 Hudson Ave
Rochester,NY 14620
Phone: 585-544-0450 x300