Sphinx and RT

RT Users
1

Hi,

I’m almost desperate - after spending 2 days trying to implement the
fulltext search in RT 4.2.2 i’m still at the point, where i can’t find
proper information regarding my issue. Here is my setup:

CentOS 6
MySQL 5.1.73 (grabbed from repos, beside that i downloaded the source
version of this MySQL in order to compile the sphinx lib)
Sphinx 2.1.5 (from their official website)
RT 4.2.2

So, from the source directory of Sphinx, i copied the “mysqlse” content
to the source tree of the mysql. Only compiled MySQL (without install)

  • installed the Sphinx lib, and everything went smooth. Currently this
    is the result from MySQL engines:

mysql> select * from mysql.plugin;
| name | dl |
| sphinx | ha_sphinx.so |
1 row in set (0.00 sec)

mysql>

It’s loaded, Sphinx is working:

[root@rtmig ymarinov]# ps xa|grep search
7162 ? S 0:00 /usr/bin/searchd --config
/etc/sphinx/sphinx.conf
7163 ? Sl 0:05 /usr/bin/searchd --config
/etc/sphinx/sphinx.conf
8103 pts/0 S+ 0:00 grep search
[root@rtmig ymarinov]#

At this point, everything looks find, but once i try to run
rt-setup-fulltext-index, i got the following error:

[7409] [Mon Feb 17 13:09:33 2014] [critical]: DBI
connect(‘dbname=rt4;host=localhost’,‘root’,…) failed: Can’t connect to
local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (111) at
./rt-setup-fulltext-index line 581. (/opt/rt4/sbin/…/lib/RT.pm:393)
DBI connect(‘dbname=rt4;host=localhost’,‘root’,…) failed: Can’t
connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
(111) at ./rt-setup-fulltext-index line 581.

No matter, what user or password i’m using - this is the error i’m getting.
Point me something to look for, please. Can I just use the “CREATE
TABLE” which is in this script, or there is something else which have to
be done?

BR.

2

Check the setup on this table I found that the only way it works for me is if I use the IP address instead of “localhost”.

CREATE TABLE AttachmentsIndex (
id int(10) unsigned NOT NULL,
weight int(11) NOT NULL,
query varchar(3072) NOT NULL,
KEY query (query(1024))
) ENGINE=SPHINX DEFAULT CHARSET=utf8 CONNECTION=‘sphinx://”ipaddress of host”:3312/rt’$$

Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.commailto:cstephan@copesan.com
www.copesan.comhttp://www.copesan.com/
"Servicing North America with Local Care"From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Yavor Marinov
Sent: Monday, February 17, 2014 8:20 AM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] Sphinx and RT

Hi,

I’m almost desperate - after spending 2 days trying to implement the fulltext search in RT 4.2.2 i’m still at the point, where i can’t find proper information regarding my issue. Here is my setup:

CentOS 6
MySQL 5.1.73 (grabbed from repos, beside that i downloaded the source version of this MySQL in order to compile the sphinx lib)
Sphinx 2.1.5 (from their official website)
RT 4.2.2

So, from the source directory of Sphinx, i copied the “mysqlse” content to the source tree of the mysql. Only compiled MySQL (without install) - installed the Sphinx lib, and everything went smooth. Currently this is the result from MySQL engines:

mysql> select * from mysql.plugin;
| name | dl |
| sphinx | ha_sphinx.so |
1 row in set (0.00 sec)

mysql>

It’s loaded, Sphinx is working:

[root@rtmig ymarinov]# ps xa|grep search
7162 ? S 0:00 /usr/bin/searchd --config /etc/sphinx/sphinx.conf
7163 ? Sl 0:05 /usr/bin/searchd --config /etc/sphinx/sphinx.conf
8103 pts/0 S+ 0:00 grep search
[root@rtmig ymarinov]#

At this point, everything looks find, but once i try to run rt-setup-fulltext-index, i got the following error:

[7409] [Mon Feb 17 13:09:33 2014] [critical]: DBI connect(‘dbname=rt4;host=localhost’,‘root’,…) failed: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (111) at ./rt-setup-fulltext-index line 581. (/opt/rt4/sbin/…/lib/RT.pm:393)
DBI connect(‘dbname=rt4;host=localhost’,‘root’,…) failed: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (111) at ./rt-setup-fulltext-index line 581.

No matter, what user or password i’m using - this is the error i’m getting.
Point me something to look for, please. Can I just use the “CREATE TABLE” which is in this script, or there is something else which have to be done?

BR.

3

Actually, the script rt-setup-fulltext-index is supposed to do that.
And I’ve tried everything, localhost,127.0.0.1, the real hostname, the
real IP - nothing made this script working.

Are you advising me to create this table by hand? Because if it’s this
… i can just cut my wrists ;)On 02/17/2014 04:30 PM, Bryon Baker wrote:

Check the setup on this table I found that the only way it works for
me is if I use the IP address instead of “localhost”.

CREATE TABLE AttachmentsIndex (

id int(10) unsigned NOT NULL,

weight int(11) NOT NULL,

query varchar(3072) NOT NULL,

KEY query (query(1024))

) ENGINE=SPHINX DEFAULT CHARSET=utf8 CONNECTION=‘sphinx://”ipaddress
of host”:3312/rt’$$

Bryon Baker

Network Operations Manager

/Copesan/**/- Specialists in Pest Solutions/

800-267-3726 • 262-783-6261 ext. 2296

bbaker@copesan.com mailto:cstephan@copesan.com

www.copesan.com http://www.copesan.com/

/“Servicing North America with Local Care”/

*From:*rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] *On Behalf Of *Yavor
Marinov
Sent: Monday, February 17, 2014 8:20 AM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] Sphinx and RT

Hi,

I’m almost desperate - after spending 2 days trying to implement the
fulltext search in RT 4.2.2 i’m still at the point, where i can’t find
proper information regarding my issue. Here is my setup:

CentOS 6
MySQL 5.1.73 (grabbed from repos, beside that i downloaded the source
version of this MySQL in order to compile the sphinx lib)
Sphinx 2.1.5 (from their official website)
RT 4.2.2

So, from the source directory of Sphinx, i copied the “mysqlse”
content to the source tree of the mysql. Only compiled MySQL
(without install) - installed the Sphinx lib, and everything went
smooth. Currently this is the result from MySQL engines:

mysql> select * from mysql.plugin;
±-------±-------------+
| name | dl |
±-------±-------------+
| sphinx | ha_sphinx.so |
±-------±-------------+
1 row in set (0.00 sec)

mysql>

It’s loaded, Sphinx is working:

[root@rtmig ymarinov]# ps xa|grep search
7162 ? S 0:00 /usr/bin/searchd --config
/etc/sphinx/sphinx.conf
7163 ? Sl 0:05 /usr/bin/searchd --config
/etc/sphinx/sphinx.conf
8103 pts/0 S+ 0:00 grep search
[root@rtmig ymarinov]#

At this point, everything looks find, but once i try to run
rt-setup-fulltext-index, i got the following error:

[7409] [Mon Feb 17 13:09:33 2014] [critical]: DBI
connect(‘dbname=rt4;host=localhost’,‘root’,…) failed: Can’t connect
to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (111)
at ./rt-setup-fulltext-index line 581. (/opt/rt4/sbin/…/lib/RT.pm:393)
DBI connect(‘dbname=rt4;host=localhost’,‘root’,…) failed: Can’t
connect to local MySQL server through socket
‘/var/lib/mysql/mysql.sock’ (111) at ./rt-setup-fulltext-index line 581.

No matter, what user or password i’m using - this is the error i’m
getting.
Point me something to look for, please. Can I just use the “CREATE
TABLE” which is in this script, or there is something else which have
to be done?

BR.

4

The table did get created for me but would not work until I recreated using the IP address. There was some testing procedures I used from the Sphinx site to figures this out I will send them in another email if I can find the again.

Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.commailto:cstephan@copesan.com
www.copesan.comhttp://www.copesan.com/
"Servicing North America with Local Care"From: Yavor Marinov [mailto:ymarinov@neterra.net]
Sent: Monday, February 17, 2014 8:42 AM
To: Bryon Baker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Sphinx and RT

Actually, the script rt-setup-fulltext-index is supposed to do that. And I’ve tried everything, localhost,127.0.0.1, the real hostname, the real IP - nothing made this script working.

Are you advising me to create this table by hand? Because if it’s this … i can just cut my wrists :wink:

5

It seems that the table AttachmentIndex is created, but currently it’s
empty. Once i try to insert the values i’m getting this:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Even if I try to drop that table, I’m getting the same problem. Also
creating executing the following

CREATE TABLE Sphinx (
id INTEGER UNSIGNED NOT NULL,
weight INTEGER NOT NULL,
query VARCHAR(3072) NOT NULL,
INDEX(query)
) ENGINE=SPHINX CONNECTION=“sphinx://127.0.0.1:9312/fulltext1”
CHARACTER SET utf8 ;

I’m getting

ERROR 1064 (42000): You have an error in your SQL syntax;On 02/17/2014 04:46 PM, Bryon Baker wrote:

The table did get created for me but would not work until I recreated
using the IP address. There was some testing procedures I used from
the Sphinx site to figures this out I will send them in another email
if I can find the again.

Bryon Baker

Network Operations Manager

/Copesan/**/- Specialists in Pest Solutions/

800-267-3726 • 262-783-6261 ext. 2296

bbaker@copesan.com mailto:cstephan@copesan.com

www.copesan.com http://www.copesan.com/

/“Servicing North America with Local Care”/

*From:*Yavor Marinov [mailto:ymarinov@neterra.net]
Sent: Monday, February 17, 2014 8:42 AM
To: Bryon Baker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Sphinx and RT

Actually, the script rt-setup-fulltext-index is supposed to do that.
And I’ve tried everything, localhost,127.0.0.1, the real hostname, the
real IP - nothing made this script working.

Are you advising me to create this table by hand? Because if it’s this
… i can just cut my wrists :wink:

On 02/17/2014 04:30 PM, Bryon Baker wrote:

Check the setup on this table I found that the only way it works
for me is if I use the IP address instead of “localhost”.

CREATE TABLE `AttachmentsIndex` (

`id` int(10) unsigned NOT NULL,

`weight` int(11) NOT NULL,

`query` varchar(3072) NOT NULL,

KEY `query` (`query`(1024))

) ENGINE=SPHINX DEFAULT CHARSET=utf8
CONNECTION='sphinx://”ipaddress of host”:3312/rt'$$

Bryon Baker

Network Operations Manager

*/Copesan/**/- Specialists in Pest Solutions/*

800-267-3726 •  262-783-6261 ext. 2296

bbaker@copesan.com <mailto:cstephan@copesan.com>

www.copesan.com <http://www.copesan.com/>

*/"Servicing North America with Local Care"/*

*From:*rt-users-bounces@lists.bestpractical.com
<mailto:rt-users-bounces@lists.bestpractical.com>
[mailto:rt-users-bounces@lists.bestpractical.com] *On Behalf Of
*Yavor Marinov
*Sent:* Monday, February 17, 2014 8:20 AM
*To:* rt-users@lists.bestpractical.com
<mailto:rt-users@lists.bestpractical.com>
*Subject:* [rt-users] Sphinx and RT

Hi,

I'm almost desperate - after spending 2 days trying to implement
the fulltext search in RT 4.2.2 i'm still at the point, where i
can't find proper information regarding my issue. Here is my setup:

CentOS 6
MySQL 5.1.73 (grabbed from repos, beside that i downloaded the
source version of this MySQL in order to compile the sphinx lib)
Sphinx 2.1.5 (from their official website)
RT 4.2.2

So, from the source directory of Sphinx, i copied the "mysqlse"
content to the source tree of the mysql. *Only* compiled MySQL
(without install) - installed the Sphinx lib, and everything went
smooth. Currently this is the result from MySQL engines:

mysql> select * from mysql.plugin;
+--------+--------------+
| name   | dl           |
+--------+--------------+
| sphinx | ha_sphinx.so |
+--------+--------------+
1 row in set (0.00 sec)

mysql>

It's loaded, Sphinx is working:

[root@rtmig ymarinov]# ps xa|grep search
 7162 ?        S      0:00 /usr/bin/searchd --config
/etc/sphinx/sphinx.conf
 7163 ?        Sl     0:05 /usr/bin/searchd --config
/etc/sphinx/sphinx.conf
 8103 pts/0    S+     0:00 grep search
[root@rtmig ymarinov]#

At this point, everything looks find, but once i try to run
*rt-setup-fulltext-index*, i got the following error:

[7409] [Mon Feb 17 13:09:33 2014] [critical]: DBI
connect('dbname=rt4;host=localhost','root',...) failed: Can't
connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (111) at ./rt-setup-fulltext-index
line 581. (/opt/rt4/sbin/../lib/RT.pm:393)
DBI connect('dbname=rt4;host=localhost','root',...) failed: Can't
connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (111) at ./rt-setup-fulltext-index
line 581.

No matter, what user or password i'm using - this is the error i'm
getting.
Point me something to look for, please. Can I just use the "CREATE
TABLE" which is in this script, or there is something else which
have to be done?

BR.
6

The table will always be empty. This was unexpected for me as well somewhere I did find some SQL that would test Sphinx and MySQL integration. Just can’t find it again.

Still looking.

Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.commailto:cstephan@copesan.com
www.copesan.comhttp://www.copesan.com/
"Servicing North America with Local Care"From: Yavor Marinov [mailto:ymarinov@neterra.net]
Sent: Monday, February 17, 2014 9:34 AM
To: Bryon Baker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Sphinx and RT

It seems that the table AttachmentIndex is created, but currently it’s empty. Once i try to insert the values i’m getting this:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Even if I try to drop that table, I’m getting the same problem. Also creating executing the following

CREATE TABLE Sphinx (
id INTEGER UNSIGNED NOT NULL,
weight INTEGER NOT NULL,
query VARCHAR(3072) NOT NULL,
INDEX(query)
) ENGINE=SPHINX CONNECTION=“sphinx://127.0.0.1:9312/fulltext1” CHARACTER SET utf8 ;

I’m getting

ERROR 1064 (42000): You have an error in your SQL syntax;

7

Since i’m struggle with this all day now, i tried everything even the
following create table:

CREATE TABLE t1
(
id INTEGER UNSIGNED 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/fulltext1” CHARACTER
SET utf8;

Still the error is:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Directly searching from the command line using “search” i’m getting
results, but still i’m missing something between RT and Sphinx/MySQLOn 02/17/2014 05:46 PM, Bryon Baker wrote:

The table will always be empty. This was unexpected for me as well
somewhere I did find some SQL that would test Sphinx and MySQL
integration. Just can’t find it again.

Still looking.

Bryon Baker

Network Operations Manager

/Copesan/**/- Specialists in Pest Solutions/

800-267-3726 • 262-783-6261 ext. 2296

bbaker@copesan.com mailto:cstephan@copesan.com

www.copesan.com http://www.copesan.com/

/“Servicing North America with Local Care”/

*From:*Yavor Marinov [mailto:ymarinov@neterra.net]
Sent: Monday, February 17, 2014 9:34 AM
To: Bryon Baker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Sphinx and RT

It seems that the table AttachmentIndex is created, but currently it’s
empty. Once i try to insert the values i’m getting this:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Even if I try to drop that table, I’m getting the same problem. Also
creating executing the following

CREATE TABLE Sphinx (
id INTEGER UNSIGNED NOT NULL,
weight INTEGER NOT NULL,
query VARCHAR(3072) NOT NULL,
INDEX(query)
) ENGINE=SPHINX CONNECTION=“sphinx://127.0.0.1:9312/fulltext1”
CHARACTER SET utf8 ;

I’m getting

ERROR 1064 (42000): You have an error in your SQL syntax;

On 02/17/2014 04:46 PM, Bryon Baker wrote:

The table did get created for me but would not work until I
recreated using the IP address.  There was some testing procedures
I used from the Sphinx site to figures this out I will send them
in another email if I can find the again.

Bryon Baker

Network Operations Manager

*/Copesan/**/- Specialists in Pest Solutions/*

800-267-3726 •  262-783-6261 ext. 2296

bbaker@copesan.com <mailto:cstephan@copesan.com>

www.copesan.com <http://www.copesan.com/>

*/"Servicing North America with Local Care"/*

*From:*Yavor Marinov [mailto:ymarinov@neterra.net]
*Sent:* Monday, February 17, 2014 8:42 AM
*To:* Bryon Baker
*Cc:* rt-users@lists.bestpractical.com
<mailto:rt-users@lists.bestpractical.com>
*Subject:* Re: [rt-users] Sphinx and RT

Actually, the script *rt-setup-fulltext-index* is supposed to do
that. And I've tried everything, localhost,127.0.0.1, the real
hostname, the real IP - nothing made this script working.

Are you advising me to create this table by hand? Because if it's
this ... i can just cut my wrists ;)

On 02/17/2014 04:30 PM, Bryon Baker wrote:

    Check the setup on this table I found that the only way it
    works for me is if I use the IP address instead of “localhost”.

    CREATE TABLE `AttachmentsIndex` (

    `id` int(10) unsigned NOT NULL,

    `weight` int(11) NOT NULL,

    `query` varchar(3072) NOT NULL,

    KEY `query` (`query`(1024))

    ) ENGINE=SPHINX DEFAULT CHARSET=utf8
    CONNECTION='sphinx://”ipaddress of host”:3312/rt'$$

    Bryon Baker

    Network Operations Manager

    */Copesan/**/- Specialists in Pest Solutions/*

    800-267-3726 •  262-783-6261 ext. 2296

    bbaker@copesan.com <mailto:cstephan@copesan.com>

    www.copesan.com <http://www.copesan.com/>

    */"Servicing North America with Local Care"/*

    *From:*rt-users-bounces@lists.bestpractical.com
    <mailto:rt-users-bounces@lists.bestpractical.com>
    [mailto:rt-users-bounces@lists.bestpractical.com] *On Behalf
    Of *Yavor Marinov
    *Sent:* Monday, February 17, 2014 8:20 AM
    *To:* rt-users@lists.bestpractical.com
    <mailto:rt-users@lists.bestpractical.com>
    *Subject:* [rt-users] Sphinx and RT

    Hi,

    I'm almost desperate - after spending 2 days trying to
    implement the fulltext search in RT 4.2.2 i'm still at the
    point, where i can't find proper information regarding my
    issue. Here is my setup:

    CentOS 6
    MySQL 5.1.73 (grabbed from repos, beside that i downloaded the
    source version of this MySQL in order to compile the sphinx lib)
    Sphinx 2.1.5 (from their official website)
    RT 4.2.2

    So, from the source directory of Sphinx, i copied the
    "mysqlse" content to the source tree of the mysql. *Only*
    compiled MySQL (without install) - installed the Sphinx lib,
    and everything went smooth. Currently this is the result from
    MySQL engines:

    mysql> select * from mysql.plugin;
    +--------+--------------+
    | name   | dl           |
    +--------+--------------+
    | sphinx | ha_sphinx.so |
    +--------+--------------+
    1 row in set (0.00 sec)

    mysql>

    It's loaded, Sphinx is working:

    [root@rtmig ymarinov]# ps xa|grep search
     7162 ?        S      0:00 /usr/bin/searchd --config
    /etc/sphinx/sphinx.conf
     7163 ?        Sl     0:05 /usr/bin/searchd --config
    /etc/sphinx/sphinx.conf
     8103 pts/0    S+     0:00 grep search
    [root@rtmig ymarinov]#

    At this point, everything looks find, but once i try to run
    *rt-setup-fulltext-index*, i got the following error:

    [7409] [Mon Feb 17 13:09:33 2014] [critical]: DBI
    connect('dbname=rt4;host=localhost','root',...) failed: Can't
    connect to local MySQL server through socket
    '/var/lib/mysql/mysql.sock' (111) at ./rt-setup-fulltext-index
    line 581. (/opt/rt4/sbin/../lib/RT.pm:393)
    DBI connect('dbname=rt4;host=localhost','root',...) failed:
    Can't connect to local MySQL server through socket
    '/var/lib/mysql/mysql.sock' (111) at ./rt-setup-fulltext-index
    line 581.

    No matter, what user or password i'm using - this is the error
    i'm getting.
    Point me something to look for, please. Can I just use the
    "CREATE TABLE" which is in this script, or there is something
    else which have to be done?

    BR.
8

Are you sure the MySQL tcp port is 9312? That is not default 3312 is did you change it?

Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.commailto:cstephan@copesan.com
www.copesan.comhttp://www.copesan.com/
"Servicing North America with Local Care"From: Yavor Marinov [mailto:ymarinov@neterra.net]
Sent: Monday, February 17, 2014 9:52 AM
To: Bryon Baker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Sphinx and RT

Since i’m struggle with this all day now, i tried everything even the following create table:

CREATE TABLE t1
(
id INTEGER UNSIGNED 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/fulltext1” CHARACTER SET utf8;

Still the error is:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Directly searching from the command line using “search” i’m getting results, but still i’m missing something between RT and Sphinx/MySQL

9

Yes, that’s correct i got this value in /etc/sphinx/sphinx.conf

listen = 9312
listen = 9306:mysql41

9306 is for SphinxQLOn 02/17/2014 05:55 PM, Bryon Baker wrote:

Are you sure the MySQL tcp port is 9312? That is not default 3312 is
did you change it?

Bryon Baker

Network Operations Manager

/Copesan/**/- Specialists in Pest Solutions/

800-267-3726 • 262-783-6261 ext. 2296

bbaker@copesan.com mailto:cstephan@copesan.com

www.copesan.com http://www.copesan.com/

/“Servicing North America with Local Care”/

*From:*Yavor Marinov [mailto:ymarinov@neterra.net]
Sent: Monday, February 17, 2014 9:52 AM
To: Bryon Baker
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Sphinx and RT

Since i’m struggle with this all day now, i tried everything even the
following create table:

CREATE TABLE t1
(
id INTEGER UNSIGNED 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/fulltext1”
CHARACTER SET utf8;

Still the error is:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Directly searching from the command line using “search” i’m getting
results, but still i’m missing something between RT and Sphinx/MySQL

On 02/17/2014 05:46 PM, Bryon Baker wrote:

The table will always be empty.  This was unexpected for me as
well somewhere I did find some SQL that would test Sphinx and
MySQL integration.  Just can’t find it again.

Still looking.

Bryon Baker

Network Operations Manager

*/Copesan/**/- Specialists in Pest Solutions/*

800-267-3726 •  262-783-6261 ext. 2296

bbaker@copesan.com <mailto:cstephan@copesan.com>

www.copesan.com <http://www.copesan.com/>

*/"Servicing North America with Local Care"/*

*From:*Yavor Marinov [mailto:ymarinov@neterra.net]
*Sent:* Monday, February 17, 2014 9:34 AM
*To:* Bryon Baker
*Cc:* rt-users@lists.bestpractical.com
<mailto:rt-users@lists.bestpractical.com>
*Subject:* Re: [rt-users] Sphinx and RT

It seems that the table AttachmentIndex is created, but currently
it's empty. Once i try to insert the values i'm getting this:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Even if I try to drop that table, I'm getting the same problem.
Also creating executing the following

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

I'm getting

ERROR 1064 (42000): You have an error in your SQL syntax;

On 02/17/2014 04:46 PM, Bryon Baker wrote:

    The table did get created for me but would not work until I
    recreated using the IP address.  There was some testing
    procedures I used from the Sphinx site to figures this out I
    will send them in another email if I can find the again.

    Bryon Baker

    Network Operations Manager

    */Copesan/**/- Specialists in Pest Solutions/*

    800-267-3726 •  262-783-6261 ext. 2296

    bbaker@copesan.com <mailto:cstephan@copesan.com>

    www.copesan.com <http://www.copesan.com/>

    */"Servicing North America with Local Care"/*

    *From:*Yavor Marinov [mailto:ymarinov@neterra.net]
    *Sent:* Monday, February 17, 2014 8:42 AM
    *To:* Bryon Baker
    *Cc:* rt-users@lists.bestpractical.com
    <mailto:rt-users@lists.bestpractical.com>
    *Subject:* Re: [rt-users] Sphinx and RT

    Actually, the script *rt-setup-fulltext-index* is supposed to
    do that. And I've tried everything, localhost,127.0.0.1, the
    real hostname, the real IP - nothing made this script working.

    Are you advising me to create this table by hand? Because if
    it's this ... i can just cut my wrists ;)

    On 02/17/2014 04:30 PM, Bryon Baker wrote:

        Check the setup on this table I found that the only way it
        works for me is if I use the IP address instead of
        “localhost”.

        CREATE TABLE `AttachmentsIndex` (

        `id` int(10) unsigned NOT NULL,

        `weight` int(11) NOT NULL,

        `query` varchar(3072) NOT NULL,

        KEY `query` (`query`(1024))

        ) ENGINE=SPHINX DEFAULT CHARSET=utf8
        CONNECTION='sphinx://”ipaddress of host”:3312/rt'$$

        Bryon Baker

        Network Operations Manager

        */Copesan/**/- Specialists in Pest Solutions/*

        800-267-3726 •  262-783-6261 ext. 2296

        bbaker@copesan.com <mailto:cstephan@copesan.com>

        www.copesan.com <http://www.copesan.com/>

        */"Servicing North America with Local Care"/*

        *From:*rt-users-bounces@lists.bestpractical.com
        <mailto:rt-users-bounces@lists.bestpractical.com>
        [mailto:rt-users-bounces@lists.bestpractical.com] *On
        Behalf Of *Yavor Marinov
        *Sent:* Monday, February 17, 2014 8:20 AM
        *To:* rt-users@lists.bestpractical.com
        <mailto:rt-users@lists.bestpractical.com>
        *Subject:* [rt-users] Sphinx and RT

        Hi,

        I'm almost desperate - after spending 2 days trying to
        implement the fulltext search in RT 4.2.2 i'm still at the
        point, where i can't find proper information regarding my
        issue. Here is my setup:

        CentOS 6
        MySQL 5.1.73 (grabbed from repos, beside that i downloaded
        the source version of this MySQL in order to compile the
        sphinx lib)
        Sphinx 2.1.5 (from their official website)
        RT 4.2.2

        So, from the source directory of Sphinx, i copied the
        "mysqlse" content to the source tree of the mysql. *Only*
        compiled MySQL (without install) - installed the Sphinx
        lib, and everything went smooth. Currently this is the
        result from MySQL engines:

        mysql> select * from mysql.plugin;
        +--------+--------------+
        | name   | dl           |
        +--------+--------------+
        | sphinx | ha_sphinx.so |
        +--------+--------------+
        1 row in set (0.00 sec)

        mysql>

        It's loaded, Sphinx is working:

        [root@rtmig ymarinov]# ps xa|grep search
         7162 ?        S      0:00 /usr/bin/searchd --config
        /etc/sphinx/sphinx.conf
         7163 ?        Sl     0:05 /usr/bin/searchd --config
        /etc/sphinx/sphinx.conf
         8103 pts/0    S+     0:00 grep search
        [root@rtmig ymarinov]#

        At this point, everything looks find, but once i try to
        run *rt-setup-fulltext-index*, i got the following error:

        [7409] [Mon Feb 17 13:09:33 2014] [critical]: DBI
        connect('dbname=rt4;host=localhost','root',...) failed:
        Can't connect to local MySQL server through socket
        '/var/lib/mysql/mysql.sock' (111) at
        ./rt-setup-fulltext-index line 581.
        (/opt/rt4/sbin/../lib/RT.pm:393)
        DBI connect('dbname=rt4;host=localhost','root',...)
        failed: Can't connect to local MySQL server through socket
        '/var/lib/mysql/mysql.sock' (111) at
        ./rt-setup-fulltext-index line 581.

        No matter, what user or password i'm using - this is the
        error i'm getting.
        Point me something to look for, please. Can I just use the
        "CREATE TABLE" which is in this script, or there is
        something else which have to be done?

        BR.
10

Check the setup on this table I found that the only way it works for me is if I use the IP
address instead of “localhost”.
) ENGINE=SPHINX DEFAULT CHARSET=utf8 CONNECTION=‘sphinx://“ipaddress of host”:3312/rt’$$

For the archives, this IP address restriction should be a MySQL
5.5+Sphinx problem, so not relevant to the original reporter who was
running MySQL 5.1

It’s resolved in this branch
https://github.com/bestpractical/rt/compare/4.0;sphinx-on-mysql-5.5
and a bug was filed upstream

http://sphinxsearch.com/bugs/view.php?id=1815

-kevin