26

I am trying to connect to SphinxQL server through Linux command-line this way:

> mysql -P 9306

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

My Sphinx config file has 2 listen entries:

listen                  = 9312
listen                  = 9306:mysql41

searchd daemon is running:

> ps ax | grep searchd
10727 ?        S      0:00 /usr/local/sphinx/bin/searchd
10728 ?        Sl     0:00 /usr/local/sphinx/bin/searchd

Regular search queries work perfectly:

> /usr/local/sphinx/bin/search StackOverflow | more

Sphinx 2.0.4-release (r3135)
Copyright (c) 2001-2012, Andrew Aksyonoff
Copyright (c) 2008-2012, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/usr/local/sphinx/etc/sphinx.conf'...
index 'test1': query 'StackOverflow ': returned 2 matches of 2 total in 0.009 sec

displaying matches:
1. document=1788212, weight=1797
        id=1788212
...

So, what I am doing wrong? How can I get access to SphinxQL console?

Any hint will be appreciated. Thanks in advance!

snippetsofcode
  • 937
  • 2
  • 10
  • 10
  • 2
    Specifying host address I got MySQL console instead of SphinxQL console: This is weird ...: > mysql -h 127.0.0.1 -P 9306 Welcome to the MySQL monitor... – snippetsofcode Aug 26 '12 at 01:17

3 Answers3

72

the 'mysql' client, will totally ignore the -P param, if it detects mysql is running on a unix-socket. So really even though you ask for the sphinxQL port, you are connecting to mysql

Use

mysql -P9306 --protocol=tcp

to tell the client to ignore the socket.

Pro Tip:

mysql -P9306 --protocol=tcp --prompt='sphinxQL> '

which serves as a useful ongoing reminder you are connected to sphinx not mysql :)

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • If you've got an old version of Sphinx but a newer version of MySQL then you might get a message saying `ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)`. You need to add an extra option to the command: `mysql -P9306 --protocol=tcp --prompt='sphinxQL> ' --skip-secure-auth` – alnorth29 May 23 '17 at 08:10
12

Works for me:

mysql -P 9306 -h 0
kleopatra
  • 51,061
  • 28
  • 99
  • 211
mafin
  • 137
  • 1
  • 4
2

I ran into this recently. I was able to get in to Sphinx via the mysql shell by commenting out the listen configuration that didn't specify MySQL. This may not work for you, if you still need to get to searchd via the API.

Chris Henry
  • 11,914
  • 3
  • 30
  • 31
  • I did it, and restarted searchd daemon. But I throughts the same errors. Nevertheless it seems I could enter, using "mysql -P 9306 -p" command and root MySQL password, but it does identify as MySQL console. It is weird. When querying mysql> SELECT * FROM test1 LIMIT 0,10; it responds: ERROR 1064 (42000): index test1: fullscan requires extern docinfo – snippetsofcode Aug 26 '12 at 05:55
  • 1
    Hmm, that is odd, that error message (about requiting extern docinfo' is a sphinx error, not a mysql error. Which suggests you are connecting to searchd. What version of mysql is reported when first connect, it should show the server version when connecting. – barryhunter Aug 26 '12 at 23:28
  • Fixed. I was connecting to MySQL instead of SphinxQL. My command lacked an important modifier: --protocol=tcp. Thanks! – snippetsofcode Aug 27 '12 at 01:21