Re: [rt-users] RES: RE: RES: Sphinx fulltext index v4.0.4

2013-12-20 Thread Alex Vandiver 
On Tue, 2013-12-17 at 17:28 -0500, Alex Vandiver wrote:
 This lies firmly in the domain of having to debug MySQL and the SphinxSE
 plugin, and not in debugging RT itself.  From recent testing locally,
 using 127.0.0.1 instead of localhost works acceptably.

I have confirmed that this is a bug in sphinxsearch.  mysql 5.5.15 and
above removed a codepath that SphinxSE was using to do name resolution,
and the re-implementation of it that SphinxSE uses could never have
worked.

The work-around is to use an IP address (rather than a hostname like
localhost) in the sphinx:// URL that rt-setup-fulltext-index prompts
for.  Alternately, the two attached patches can be applied to the
storage/sphinx/ directory.

The issue has been reported to sphinxsearch as bug 1815.  RT may work
around this issue in a future release by defaulting to suggesting
127.0.0.1 instead of localhost when running on MySQL 5.5.
 - Alex
From 21770e15fa8667177b79ae8f5cd8de67ebd44b28 Mon Sep 17 00:00:00 2001
From: Alex Vandiver a...@chmrr.net
Date: Thu, 19 Dec 2013 01:18:43 -0500
Subject: [PATCH 1/2] getaddrinfo returns 0 on success

---
 mysqlse/ha_sphinx.cc | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/mysqlse/ha_sphinx.cc b/mysqlse/ha_sphinx.cc
index 06c8610..8305519 100644
--- a/mysqlse/ha_sphinx.cc
+++ b/mysqlse/ha_sphinx.cc
@@ -2121,7 +2121,7 @@ int ha_sphinx::Connect ( const char * sHost, ushort uPort )
 #if MYSQL_VERSION_ID=50515
 			struct addrinfo *hp = NULL;
 			tmp_errno = getaddrinfo ( sHost, NULL, NULL, hp );
-			if ( !tmp_errno || !hp || !hp-ai_addr )
+			if ( tmp_errno || !hp || !hp-ai_addr )
 			{
 bError = true;
 if ( hp )
-- 
1.8.5

From 204e78173db262d2ba73555ed277f0908a3fc568 Mon Sep 17 00:00:00 2001
From: Alex Vandiver a...@chmrr.net
Date: Thu, 19 Dec 2013 01:19:20 -0500
Subject: [PATCH 2/2] Copy out the correct part of the addrinfo response

Merely copying starting at the ai_addr of the addrinfo is incorrect; for
the presumed sockaddr_in value stored in ai_addr, the first bytes are
generally the family and port, not the in_addr.

Dereference to the in_addr out explicitly, and copy that.
---
 mysqlse/ha_sphinx.cc | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/mysqlse/ha_sphinx.cc b/mysqlse/ha_sphinx.cc
index 8305519..d6ef94b 100644
--- a/mysqlse/ha_sphinx.cc
+++ b/mysqlse/ha_sphinx.cc
@@ -2148,7 +2148,7 @@ int ha_sphinx::Connect ( const char * sHost, ushort uPort )
 			}
 
 #if MYSQL_VERSION_ID=50515
-			memcpy ( sin.sin_addr, hp-ai_addr, Min ( sizeof(sin.sin_addr), (size_t)hp-ai_addrlen ) );
+			memcpy ( sin.sin_addr, ((struct sockaddr_in *)hp-ai_addr)-sin_addr, sizeof(sin.sin_addr) );
 			freeaddrinfo ( hp );
 #else
 			memcpy ( sin.sin_addr, hp-h_addr, Min ( sizeof(sin.sin_addr), (size_t)hp-h_length ) );
-- 
1.8.5

Re: [rt-users] RES: RE: RES: Sphinx fulltext index v4.0.4

2013-12-17 Thread ms 
Hi,

didn't get a response there. Is there a way to solve this? Possibly
through debugging RT4 to see how its actually trying to connect to
searchd instead of how the config looks like it should?

Regards

On 04/12/2013 13:45, m...@fv-berlin.de wrote:
 On Jan 5 09:55:28 EST 2012, Luciano Ernesto da Silva wrote:
 
 Hi,
 
 did you ever receive an answer to your problem because I am encountering
 the same thing (searchd running on 0.0.0.0:3312 but RT reporting it cant
 connect to it / resolve localhost).
 
 Additionally to what you already posted, I made sure /etc/hosts connects
 127.0.0.1 to localhost and commented out the ::1 line because I
 suspected RT4 might not be IPv6-aware, but that didnt help.
 
 KR
 
 Hello,

 This is my configuration on sphinx.conf, seems that even I change the
 name of sql_host, RT still says : failed to resolve searchd host
 (name=localhost). Seems that RT isn't looking to the right  connection.


 Luciano


 vi /etc/sphinxsearch/sphinx.conf


 source rt {
 type= mysql

 sql_host= localhost
 sql_db  = rt4
 sql_user= rt4
 sql_pass= secret

 sql_query_pre   = SET NAMES utf8
 sql_query   = \
 SELECT a.id, a.content FROM Attachments a \
 JOIN Transactions txn ON a.TransactionId = txn.id AND
 txn.ObjectType = 'RT::Ticket' \
 JOIN Tickets t ON txn.ObjectId = t.id \
 WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted'
 sql_query_info  = SELECT * FROM Attachments WHERE id=$id
 }

 index rt {
 source  = rt
 path= /opt/rt4/var/sphinx/index
 docinfo = extern
 charset_type= utf-8
 }

 indexer {
 mem_limit   = 32M
 }

 searchd {
 port= 3312
 log = /opt/rt4/var/sphinx/searchd.log
 query_log   = /opt/rt4/var/sphinx/query.log
 read_timeout= 5
 max_children= 30
 pid_file= /opt/rt4/var/sphinx/searchd.pid
 max_matches = 1
 seamless_rotate = 1
 preopen_indexes = 1
 unlink_old  = 1
 compat_sphinxql_magics  = 0
 }

 Sphinx is running OK:

 netstat -ntlp | grep searchd
 tcp0  0 0.0.0.0:33120.0.0.0:*
 LISTEN  10762/searchd

 ps -eaf |grep searchd 
 root 10762 1  0 09:17 pts/000:00:00 searchd

 The table AttachmentsIndex seems OK:

 mysql show create table AttachmentsIndex; 
 +--+
 
 
 ---+
 | Table| Create Table
 |
 +--+
 
 
 ---+
 | AttachmentsIndex | 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://localhost:3312/rt' |
 +--+
 
 
 ---+
 1 row in set (0.00 sec)

 -Mensagem original-
 De: Poulter, Dale [mailto:dale.poulter at Vanderbilt.Edu] 
 Enviada em: quinta-feira, 5 de janeiro de 2012 10:50
 Para: Luciano Ernesto da Silva; rt-users at lists.bestpractical.com
 Assunto: RE: [rt-users] RES: Sphinx fulltext index v4.0.4

 Sounds like it cannot connect to the sphinx server.  Can you confirm
 that sphinx is running (ps -eaf |grep searchd ) and that it is running
 on the port specified in the attachmentsindex create statement (mysql
 show create table AttachmentsIndex; )?   I believe the default port is
 9312 but the documents at
 http://blog.bestpractical.com/2011/06/full-text-searching.html indicate
 that the port is 3312.

 -Original Message-
 From: rt-users-bounces at lists.bestpractical.com
 [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Luciano
 Ernesto da Silva
 Sent: Thursday, January 05, 2012 5:24 AM
 To: rt-users at lists.bestpractical.com
 Subject: [rt-users] RES: Sphinx fulltext index v4.0.4

 Hello,

 I installed everything as described here by Dale/ documentation from
 docs/full_text_indexing.podc  ,  documentarion by sphinxsearch but i got
 this error:

 RT: DBD::mysql::st execute failed: Unable to connect to foreign data
 source: failed to resolve searchd host (name=localhost) at

Re: [rt-users] RES: RE: RES: Sphinx fulltext index v4.0.4

2013-12-17 Thread Alex Vandiver 
On Tue, 2013-12-17 at 23:11 +0100, m...@fv-berlin.de wrote:
 didn't get a response there. Is there a way to solve this? Possibly
 through debugging RT4 to see how its actually trying to connect to
 searchd instead of how the config looks like it should?

This lies firmly in the domain of having to debug MySQL and the SphinxSE
plugin, and not in debugging RT itself.  From recent testing locally,
using 127.0.0.1 instead of localhost works acceptably.  Future versions
of RT may default to 127.0.0.1 instead of localhost in the Sphinx
connection parameters --  though I would be much more comfortable making
that change if the underlying reason for the 'localhost' failure were
clear.  Unfortunately, debugging mysql is rather complex.

You can drop and re-create your SphinxSE table by running 'DROP TABLE
AttachmentsIndex' at a mysql prompt, re-running rt-setup-fulltext-index,
and providing sphinx://127.0.0.1:3312/rt as the URL of the Sphinx
server.

 - Alex

Re: [rt-users] RES: RE: RES: Sphinx fulltext index v4.0.4

2013-12-04 Thread ms 
On Jan 5 09:55:28 EST 2012, Luciano Ernesto da Silva wrote:

Hi,

did you ever receive an answer to your problem because I am encountering
the same thing (searchd running on 0.0.0.0:3312 but RT reporting it cant
connect to it / resolve localhost).

Additionally to what you already posted, I made sure /etc/hosts connects
127.0.0.1 to localhost and commented out the ::1 line because I
suspected RT4 might not be IPv6-aware, but that didnt help.

KR

 Hello,
 
 This is my configuration on sphinx.conf, seems that even I change the
 name of sql_host, RT still says : failed to resolve searchd host
 (name=localhost). Seems that RT isn't looking to the right  connection.
 
 
 Luciano
 
 
 vi /etc/sphinxsearch/sphinx.conf
 
 
 source rt {
 type= mysql
 
 sql_host= localhost
 sql_db  = rt4
 sql_user= rt4
 sql_pass= secret
 
 sql_query_pre   = SET NAMES utf8
 sql_query   = \
 SELECT a.id, a.content FROM Attachments a \
 JOIN Transactions txn ON a.TransactionId = txn.id AND
 txn.ObjectType = 'RT::Ticket' \
 JOIN Tickets t ON txn.ObjectId = t.id \
 WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted'
 sql_query_info  = SELECT * FROM Attachments WHERE id=$id
 }
 
 index rt {
 source  = rt
 path= /opt/rt4/var/sphinx/index
 docinfo = extern
 charset_type= utf-8
 }
 
 indexer {
 mem_limit   = 32M
 }
 
 searchd {
 port= 3312
 log = /opt/rt4/var/sphinx/searchd.log
 query_log   = /opt/rt4/var/sphinx/query.log
 read_timeout= 5
 max_children= 30
 pid_file= /opt/rt4/var/sphinx/searchd.pid
 max_matches = 1
 seamless_rotate = 1
 preopen_indexes = 1
 unlink_old  = 1
 compat_sphinxql_magics  = 0
 }
 
 Sphinx is running OK:
 
 netstat -ntlp | grep searchd
 tcp0  0 0.0.0.0:33120.0.0.0:*
 LISTEN  10762/searchd
 
 ps -eaf |grep searchd 
 root 10762 1  0 09:17 pts/000:00:00 searchd
 
 The table AttachmentsIndex seems OK:
 
 mysql show create table AttachmentsIndex; 
 +--+
 
 
 ---+
 | Table| Create Table
 |
 +--+
 
 
 ---+
 | AttachmentsIndex | 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://localhost:3312/rt' |
 +--+
 
 
 ---+
 1 row in set (0.00 sec)
 
 -Mensagem original-
 De: Poulter, Dale [mailto:dale.poulter at Vanderbilt.Edu] 
 Enviada em: quinta-feira, 5 de janeiro de 2012 10:50
 Para: Luciano Ernesto da Silva; rt-users at lists.bestpractical.com
 Assunto: RE: [rt-users] RES: Sphinx fulltext index v4.0.4
 
 Sounds like it cannot connect to the sphinx server.  Can you confirm
 that sphinx is running (ps -eaf |grep searchd ) and that it is running
 on the port specified in the attachmentsindex create statement (mysql
 show create table AttachmentsIndex; )?   I believe the default port is
 9312 but the documents at
 http://blog.bestpractical.com/2011/06/full-text-searching.html indicate
 that the port is 3312.
 
 -Original Message-
 From: rt-users-bounces at lists.bestpractical.com
 [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Luciano
 Ernesto da Silva
 Sent: Thursday, January 05, 2012 5:24 AM
 To: rt-users at lists.bestpractical.com
 Subject: [rt-users] RES: Sphinx fulltext index v4.0.4
 
 Hello,
 
 I installed everything as described here by Dale/ documentation from
 docs/full_text_indexing.podc  ,  documentarion by sphinxsearch but i got
 this error:
 
 RT: DBD::mysql::st execute failed: Unable to connect to foreign data
 source: failed to resolve searchd host (name=localhost) at
 /usr/local/share/perl/5.10.1/DBIx/SearchBuilder/Handle.pm line 587.
 (/usr/local/share/perl/5.10.1/DBIx/SearchBuilder/Handle.pm:587)
 Jan  5 08:45:16 rt4 RT: RT::Handle=HASH(0x7faacbf8ec58) couldn't execute
 the query 'SELECT COUNT(DISTINCT main.id) FROM Tickets

[rt-users] RES: Re: RES: Sphinx fulltext index v4.0.4

2012-01-12 Thread Luciano Ernesto da Silva 
Tarek,

I already tried migrate from Mysql 5.1 to postgreSQL 9.1 but I had some 
problems quith some charset. Some records in Attachments table (from users that 
send an email in 8859-1) don't get converted to utf8 and I get the strange 
character with letter I with acute, like í looking to  (there's a space 
after Â, hidden or not). I don't know fix that, do you know any way to do that? 
I even used sed command, but it don't remove the space or whatever this is.

Dale,

SPHINX: I'm  thing theirs is a problem with perl module DBIx::SearchBuilder, 
mine is 1.61, but can't connect to localhost port 3312. I tested searchd daemon 
and is working OK. 

Dale,  what version of DBIx::SearchBuilder are you using?  



Luciano

-Mensagem original-
De: rt-users-boun...@lists.bestpractical.com 
[mailto:rt-users-boun...@lists.bestpractical.com] Em nome de gekkoman
Enviada em: quinta-feira, 5 de janeiro de 2012 19:02
Para: Poulter, Dale; rt-users@lists.bestpractical.com
Assunto: [*SPAM*] Re: [rt-users] RES: Sphinx fulltext index v4.0.4

Greetings,

FYI I got sphinx running on 4.04 by following instructions before Christmas and 
everything looked ok.but then I started discovering non functioning aspects 
of RT post the change eg changing user group memberships stopped working for 
example. The database was intact with no corruptions.

This is obviously not ideal. I had debugged it to the point of finding some RT 
generated SQL no longer running properly on the database.

Since I actually prefer psql over mysql I ended up migrated to psql using 
exactly the same dataset, engaged fulltext indexing and the problems all went 
away.

Tarek




 Sounds like it cannot connect to the sphinx server.  Can you confirm 
 that sphinx is running (ps -eaf |grep searchd ) and that it is running 
 on the port specified in the attachmentsindex create statement (mysql show
 create table AttachmentsIndex; )?   I believe the default port is 9312 but
 the documents at
 http://blog.bestpractical.com/2011/06/full-text-searching.html 
 indicate that the port is 3312.

 -Original Message-
 From: rt-users-boun...@lists.bestpractical.com
 [mailto:rt-users-boun...@lists.bestpractical.com] On Behalf Of Luciano 
 Ernesto da Silva
 Sent: Thursday, January 05, 2012 5:24 AM
 To: rt-users@lists.bestpractical.com
 Subject: [rt-users] RES: Sphinx fulltext index v4.0.4

 Hello,

 I installed everything as described here by Dale/ documentation from 
 docs/full_text_indexing.podc  ,  documentarion by sphinxsearch but i 
 got this error:

 RT: DBD::mysql::st execute failed: Unable to connect to foreign data
 source: failed to resolve searchd host (name=localhost) at 
 /usr/local/share/perl/5.10.1/DBIx/SearchBuilder/Handle.pm line 587.
 (/usr/local/share/perl/5.10.1/DBIx/SearchBuilder/Handle.pm:587)
 Jan  5 08:45:16 rt4 RT: RT::Handle=HASH(0x7faacbf8ec58) couldn't 
 execute the query 'SELECT COUNT(DISTINCT main.id) FROM Tickets main 
 JOIN Transactions Transactions_1  ON ( Transactions_1.ObjectType = 
 'RT::Ticket'
 ) AND ( Transactions_1.ObjectId = main.id ) JOIN Attachments 
 Attachments_2  ON ( Attachments_2.TransactionId = Transactions_1.id ) 
 JOIN AttachmentsIndex AttachmentsIndex_3  ON ( AttachmentsIndex_3.id = 
 Attachments_2.id )  WHERE (main.Status !=
 'deleted') AND (main.Queue = '23' AND  ( AttachmentsIndex_3.query = 
 'ESEF;limit=1;maxmatches=1' ) ) AND (main.Type = 'ticket') AND 
 (main.EffectiveId = main.id) ' at 
 /usr/local/share/perl/5.10.1/DBIx/SearchBuilder/Handle.pm line 600

 The Mysql server is running on localhost and the firewall is off(Devel
 server) and SELINUX is disabled.

 DBIx::SearchBuilder is up to date.


 Any ideas?


 Luciano



 -Mensagem original-
 De: rt-users-boun...@lists.bestpractical.com
 [mailto:rt-users-boun...@lists.bestpractical.com] Em nome de Luciano 
 Ernesto da Silva Enviada em: sexta-feira, 30 de dezembro de 2011 10:49
 Para: Poulter, Dale
 Cc: rt-users@lists.bestpractical.com
 Assunto: Re: [rt-users] Sphinx fulltext index v4.0.4

 Thanks for you help!

 Luciano

 -Original Message-
 From: Poulter,   Dale [mailto:dale.poul...@vanderbilt.edu]
 Sent: Thu 12/29/2011 4:42 PM
 To: Luciano Ernesto da Silva
 Cc: rt-users@lists.bestpractical.com
 Subject: RE: [rt-users] Sphinx fulltext index v4.0.4

 Here is the process I used.

 From Mysql-5.5.17 source

 Move the sphinx source directory msqlse (I used sphinx-2.0.2-beta)  to 
 storage/sphinx in the source tree

 cmake \
 -DWITH_UNIXODBC=1 \
 -DWITH_INNOBASE_STORAGE_ENGINE=1 \
 -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
 -DWITH_SPHINX_STORAGE_ENGINE=1 \
 -DDEFAULT_CHARSET=utf8 \
 -DDEFAULT_COLLATION=utf8_general_ci \
 -DWITH_SSL=yes


 Once mysql is compiled and installed you just need to install the 
 plugin

 mysql INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

 mysqls elect * from mysql.plugin;

 This should indicated that the sphinx plugin is installed.




 -Original Message-
 From: Luciano Ernesto da Silva

Re: [rt-users] RES: Re: RES: Sphinx fulltext index v4.0.4

2012-01-12 Thread Jeff Blaine 

On 1/12/2012 9:46 AM, Luciano Ernesto da Silva wrote:

Tarek,

I already tried migrate from Mysql 5.1 to postgreSQL 9.1 but I had
some problems quith some charset. Some records in Attachments table
(from users that send an email in 8859-1) don't get converted to utf8
and I get the strange character with letter I with acute, like í
looking to  (there's a space after Â, hidden or not). I don't
know fix that, do you know any way to do that? I even used sed
command, but it don't remove the space or whatever this is.


We had the same problem.

This worked perfectly for us in the end, though we went to
PostgreSQL 8.4.9 as provided by RHEL 6.x

http://wiki-archive.bestpractical.com/view/rt-mysql2pg

RT Training Sessions (http://bestpractical.com/services/training.html)
* Boston  March 5  6, 2012