Sphinx error

[lochness]

I have tried to run Sphinx as search engine because using mysql returns a lot of internal errors, and when running it, when yo try doing a search, I get

Code: Select all

Sphinx Error: connection to 127.0.0.1:9312 failed (errno=111, msg=Connection refused)

Any ideas why?

Everything is standard, except for two language packs and a second style.

[</Solidjeuh>]

Try 'localhost' instead of '127.0.0.1'.

Of try via SSH

Code: Select all

netstat -nlp | grep searchd
netstat -nlp | grep 9312

I also think this is not a phpBB problem

[AmigoJack]

mysql returns a lot of internal errors

Ignoring those won't cure the problem.

[lochness]

I'm giving some outupt from the command line after trying to follow the instructions from https://wiki.phpbb.com/Sphinx_Fulltext_Search
The indexers appear to have worked and the ACP shows the same number of messages in total indexed messages and total indexes in main index.

When I try to run /etc/sphinxsearch/sphinx.conf search term (and the reason for asking here first, since the conf file is the one generated by phpBB)

Code: Select all

/etc/sphinxsearch/sphinx.conf: line 1: source_phpbb_ID_main: No such file or directory
/etc/sphinxsearch/sphinx.conf: line 10: syntax error near unexpected token `('
/etc/sphinxsearch/sphinx.conf: line 10: `       sql_query_pre = UPDATE phpbb_sphinx SET max_doc_id = (SELECT MAX(post_id) FROM phpbb_posts) WHERE counter_id = 1 '
[2]-  Exit 1                  indexer --config /etc/sphinxsearch/sphinx.conf/ index_phpbb_ID_main >> /home/sphinx/log/indexer.log 2>&1

If I run the sql query from phpMyadmin it works perfectly.

The indexer log.

Code: Select all

FATAL: config file '/etc/sphinxsearch/sphinx.conf/' does not exist or is not readable
Sphinx 2.2.11-id64-release (95ae9a6)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'index_phpbb_ID_delta'...
ERROR: index 'index_phpbb_ID_delta': sql_connect: Access denied for user 'xxx'@'%' to database 'xxx' (DSN=mysql://XXX:***@127.0.0.1:3306/xxx).
total 0 docs, 0 bytes
total 0.068 sec, 0 bytes/sec, 0.00 docs/sec
total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 0 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
Sphinx 2.2.11-id64-release (95ae9a6)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'index_phpbb_xxx_delta'...
ERROR: index 'index_phpbb_xxx_delta': sql_connect: Access denied for user xxx'@'%' to database 'xxx' (DSN=mysql://xxx:***@127.0.0.1:3306/xxx).
total 0 docs, 0 bytes
total 0.007 sec, 0 bytes/sec, 0.00 docs/sec
total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 0 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
FATAL: config file '/etc/sphinxsearch/sphinx.conf/' does not exist or is not readable
FATAL: config file '/etc/sphinxsearch/sphinx.conf/' does not exist or is not readable

permissions for sphinx.conf are 744.

On searchd.log

Code: Select all

[Fri Feb 22 16:07:12.100 2019] [14602] watchdog: main process 14604 exited cleanly (exit code 1), shutting down
[Fri Feb 22 16:11:31.698 2019] [15077] watchdog: main process 15078 forked ok
[Fri Feb 22 16:11:31.710 2019] [15078] WARNING: multiple addresses found for 'localhost', using the first one (ip=127.0.0.1)
[Fri Feb 22 16:11:31.711 2019] [15078] listening on 127.0.0.1:9312
[Fri Feb 22 16:11:31.713 2019] [15078] WARNING: index 'index_phpbb_xxx_main': preload: failed to open /home/sphinx/index_phpbb_xxx_main.sph: No such file or directory; NOT SERVING
[Fri Feb 22 16:11:31.714 2019] [15078] WARNING: index 'index_phpbb_xxx_delta': preload: failed to open /home/sphinx/index_phpbb_xxx_delta.sph: No such file or directory; NOT SERVING
[Fri Feb 22 16:11:31.714 2019] [15078] FATAL: no valid indexes to serve
[Fri Feb 22 16:11:31.721 2019] [15077] watchdog: main process 15078 exited cleanly (exit code 1), shutting down

Try 'localhost' instead of '127.0.0.1'.

Of try via SSH

Code: Select all

netstat -nlp | grep searchd
netstat -nlp | grep 9312

I also think this is not a phpBB problem

Neither netstat returns anything and I tried first with localhost, restarting sphinxsearch everytime I modified the conf file.

[lochness]

mysql returns a lot of internal errors

Ignoring those won't cure the problem.

And not using the mysql fulltext search also helps not overloading the server. I have 4 million messages now and I'd rather use phpBB Native Fulltext than mysql fulltext.

I've had enough headaches this week transfering the board to a new server due a damaged boot disk and starting with just the database backup. You know, new OS, new php and new mysql server versions not allowing to just copy pasting old config to new server and having to config everything from default settings. So if I have to add an additional headache, I'd rather it be for Sphinx than mysql fulltext.

[lochness]

Moving the files from /home to /var solve the first problem, but when running
/etc/sphinxsearch/sphinx.conf 'search term' I'm still getting

Code: Select all

/etc/sphinxsearch/sphinx.conf: line 1: source_phpbb_xxx_main: No such file or directory
/etc/sphinxsearch/sphinx.conf: line 11: syntax error near unexpected token `('
/etc/sphinxsearch/sphinx.conf: line 11: `                                       UPDATE phpbb_sphinx SET max_doc_id = (SELECT MAX(post_id) FROM phpbb_posts) WHERE counter_id = 1 '

[lochness]

I've tested in the live board and even if the test returns that message, it is working properly, just in case somebody else comes into the same output from running the test and you prefer to update the wiki.

[thecoalman]

Code: Select all

/etc/sphinxsearch/sphinx.conf: line 1: source_phpbb_xxx_main: No such file or directory

Does this file exist? It should. The main index is created after startup of Sphinx when you run

Code: Select all

indexer --config {CONFIG_PATH}/sphinx.conf index_phpbb_{SPHINX_ID}_main >> {DATA_PATH}/log/indexer.log 2>&1 &

and is refreshed depending on when you set it cron using -rotate. This takes a lot of resources to create so it should only be done once a day. The delta index is used for new posts since creation of the main index and can be set to refresh every 5 minutes or whatever.

The best place to research this is on sphinx's site. It was PITA setting this up when I did it but well worth it. Searches are almost instant and very little load.

Code: Select all

Sphinx Error: connection to 127.0.0.1:9312 failed (errno=111, msg=Connection refused) 

One cause of this is not enough RAM allocated. Sphinx will exit and needs to be restarted, if you get that message again check to see if it's running.

[lochness]

The best place to research this is on sphinx's site. It was PITA setting this up when I did it but well worth it. Searches are almost instant and very little load.

I agree, and it just seconds the search index was ready.

Code: Select all

Sphinx Error: connection to 127.0.0.1:9312 failed (errno=111, msg=Connection refused) 

One cause of this is not enough RAM allocated. Sphinx will exit and needs to be restarted, if you get that message again check to see if it's running.

The cause apparently was that the port searchd listens to was unmapped.