1

Mysql client have access, password is true. The mysql user have all privileges on all databases:

  • GRANT SELECT,
  • INSERT,
  • UPDATE,
  • DELETE,
  • CREATE,
  • DROP,
  • RELOAD,
  • SHUTDOWN,
  • PROCESS,
  • REFERENCES,
  • INDEX,
  • ALTER,
  • SHOW DATABASES,
  • SUPER,
  • CREATE TEMPORARY TABLES, LOCK TABLES,
  • EXECUTE,
  • REPLICATION SLAVE, REPLICATION CLIENT,
  • CREATE VIEW, SHOW VIEW,
  • CREATE ROUTINE, ALTER ROUTINE,
  • CREATE USER,
  • EVENT,
  • TRIGGER ON . TO 'ad1'@'%'

I have errors:

/usr/bin/indexer   --all
Sphinx 2.2.10-id64-release (2c212e0)
Copyright (c) 2001-2015, Andrew Aksyonoff
Copyright (c) 2008-2015, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'ad1_offers'...
ERROR: index 'ad1_offers': sql_connect: Access denied for user ad1'@'192.168.0.177' (using password: YES) (DSN=mysql://ad1:***@192.168.0.177:3306/ad1).

or

/etc/init.d/sphinxsearch start
Starting sphinxsearch: Sphinx 2.2.10-id64-release (2c212e0)
Copyright (c) 2001-2015, Andrew Aksyonoff
Copyright (c) 2008-2015, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinxsearch/sphinx.conf'...
listening on 192.168.0.177:9312
listening on 192.168.0.177:9306
precaching index 'ad1_offers'
WARNING: index 'ad1_offers': preload: failed to open /var/lib/sphinxsearch/data/    ad1_offers.sph: No such file or directory; NOT SERVING
FATAL: no valid indexes to serve ERROR.

spnix.conf:

    type                    = mysql

    sql_host                = 192.168.0.177
    sql_user                = ad1
    sql_pass                = ....
    sql_db                  = ad1
    sql_port                = 3306  # optional, default is 3306
Alexandre Kalendarev
  • 681
  • 2
  • 10
  • 24

2 Answers2

0

ERROR: index 'ad1_offers': sql_connect: Access denied for user ad1'@'192.168.0.177' (using password: YES) (DSN=mysql://ad1:***@192.168.0.177:3306/ad1).

That means indexer can't even CONNECT to mysql. Its more about the user existing and the password being right, than the actual permissions the user has.

Can you use the mysql command line client to connect to your database?

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • the mysql client have access to Db ad1 by user ad1 with pass .... from host (localhost), so mysql user have ALL GRANT PRIVILEGES – Alexandre Kalendarev Dec 21 '15 at 11:19
  • The sphinx.con is : sql_host = 192.168.0.177 sql_user = ad1 sql_pass = .... sql_db = ad1 sql_port = 3306 – Alexandre Kalendarev Dec 21 '15 at 11:21
  • # mysql -u ad1 -p -h 192.168.0.177 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 85899 Server version: 5.5.46-0+deb8u1 (Debian) – Alexandre Kalendarev Dec 21 '15 at 11:24
  • also , with root login/password access denied – Alexandre Kalendarev Dec 21 '15 at 12:32
  • Maybe try adding --protocol=tcp to the mysql command line, `mysql -u ad1 -p -h 192.168.0.177 --protocol=tcp` - thats an even better simulation. See http://stackoverflow.com/questions/12126948/connect-to-sphinxql-through-linux-command-line (sphinx wont use the socket, that mysql client may default to ) – barryhunter Dec 21 '15 at 14:45
0

in my case, when I had such a mistake it's just was a wrong password for MySQL. my users's password contained signs of type like '#' or '!'. after I removed these characters from the password everything started working!

exud
  • 570
  • 6
  • 15