Sql_connect: Access denied for user

General Discussions - English
1

I recently upgraded a server to MariaDB 10.2 and Manticore 3.4.2 but my indexer cannot connect any more, this is what I tried:

mysql -P 9306 -u my_user -p
I type the password and connect happily to the database

mysql -P 9306 -h 0
connects happily to manticore

indexer myHB_index2 --rotate
Manticore 3.4.2 6903305@200410 release
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
Copyright (c) 2017-2020, Manticore Software LTD (http://manticoresearch.com)

using config file ‘/etc/manticoresearch/manticore.conf’…
indexing index ‘myHB_index2’…
ERROR: index ‘myHB_index2’: sql_connect: Access denied for user ''@‘localhost’ (using password: YES) (DSN=mysql://:@localhost:3306/*******)

indexer is using -DMYSQL_LIB=libmysqlclient.so.18

And I found the following in my lib64:
./usr/lib64/libmysqlclient.so.18.0.0
./usr/lib64/libmysqlclient.so.16.0.0
./usr/lib64/libmysqlclient.so
./usr/lib64/libmysqlclient.so.15
./usr/lib64/libmysqlclient.so.15.0.0
./usr/lib64/libmysqlclient.so.18
./usr/lib64/libmysqlclient.so.16
./usr/local/cpanel/3rdparty/lib64/mysql/libmysqlclient.so.18.1.0
./usr/local/cpanel/3rdparty/lib64/mysql/libmysqlclient.so.18
./usr/local/cpanel/3rdparty/lib/mariadb/libmysqlclient.so

So I guess the mysql library is ok.

I tried creating a new user, changing the password to all alpha chars and adding permissions on localhost and 127.0.0.1 but the error stays.

PHP scripts connect fine to the DB using the same user.

Update 26/04/2020

Testing some more … this is the DB error log:
2020-04-26 13:18:53 139635568551680 [Warning] Access denied for user ‘myheal21_manticore’@‘localhost’ (using password: YES)
2020-04-26 13:19:52 139635568244480 [Warning] Access denied for user ‘**********’@‘localhost’ (using password: YES)

the first line is the connection from a terminal (I put the wrong password on purpose)
the second is the failed connection from manticore/indexer

why is the user name showed as ***** and not plain ? can this be related to the connection problem ?

I check the connection used by php (which works for the same user), here are the details:
Server information: 5.5.5-10.2.31-MariaDB
Host information: Localhost via UNIX socket
Protocol information: 10
Client information: mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $

---------- update ---------
Installed Manticore 3.3.0 and 3.1.2 they use the same mysqllib so not sure what difference they would make but I get the same connection problem.

Help very much appreciated.

Thanks
Roberto

2

Hi

Have you tried changing to ‘127.0.0.1’ in the Manticore’s config file?

3

Tried, same problem, I get:
ERROR: index ‘myHB_index2’: sql_connect: Access denied for user '’@‘localhost’ (using password: YES) (DSN=mysql://:@localhost:3306/*******)

127.0.0.1 maps to localhost anyway …

4

Which Linux platform is? And from which mysql/mariadb version you upgraded?

Also check if connection by socket (Manticore Search Manual: Data creation and modification > Adding data from external storages > Fetching from databases > Introduction) works.

5

So you have sql_host = 127.0.0.1, but get Access denied for user ' **********’@‘localhost’ ?

127.0.0.1 maps to localhost anyway

The point is that if you use localhost the mysql lib might prefer to use UNIX sockets, not TCP.

6

Linus: Centos 7

Migrated from mysql: 5.7.26 to MariaDB 10.2.31

Tried enabling socket, same problem.
2020-04-27 12:06:27 139635568551680 [Warning] Access denied for user ‘**********’@‘localhost’ (using password: YES)

The fact that is showing “***” for me points to some encryption …

7

yes, even using 127.0.0.1 I get Access denied @localhost

8

Trying some various configurations from the command line.

mysql -P 9306 -u my_user -pmy_passwd -hlocalhost
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 498616
Server version: 10.2.31-MariaDB MariaDB Server

mysql -P 9306 -u my_user -pmy_passwd -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 3.4.2 6903305@200410 release

Is this normal that with host=127.0.0.1 I connect to Manticore ?

9

Is this normal that with host=127.0.0.1 I connect to Manticore ?

It depends on the listen value in your config, but in most cases - yes, it’s absolutely fine.

yes, even using 127.0.0.1 I get Access denied @localhost

This looks strange. I’ve tried this version of MariaDB (their official docker):

root@283648d9031a:/# mysql -umyheal21_manticore -ppassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.2.31-MariaDB-1:10.2.31+maria~bionic mariadb.org binary distribution

with Manticore and it works fine to me:

root@283648d9031a:/# cat min.conf
source min {
    type = mysql
    sql_host = localhost
    sql_user = myheal21_manticore
    sql_pass = password
    sql_db = test
    sql_query = select floor(rand()*100000), f from t
    sql_field_string = f
}

index idx {
    path = idx
    source = min
}

searchd {
    listen = 9315:mysql41
    log = sphinx_min.log
    pid_file = 9315.pid
    binlog_path =
}

root@283648d9031a:/# indexer -c min.conf --all
Manticore 3.4.2 69033058@200410 release
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
Copyright (c) 2017-2020, Manticore Software LTD (http://manticoresearch.com)

using config file 'min.conf'...
indexing index 'idx'...
collected 2 docs, 0.0 MB
creating lookup: 0.0 Kdocs, 100.0% done
creating histograms: 0.0 Kdocs, 100.0% done
sorted 0.0 Mhits, 100.0% done
total 2 docs, 6 bytes
total 0.126 sec, 47 bytes/sec, 15.77 docs/sec
total 4 reads, 0.000 sec, 8.0 kb/call avg, 0.0 msec/call avg
total 15 writes, 0.000 sec, 0.3 kb/call avg, 0.0 msec/call avg
10

Can you try connecting from a terminal with the wrong password and then looking at the database error.log ?

Do you get a plain username or “*****” ?

Thanks

11

Sure

root@283648d9031a:/# mysql -umyheal21_manticore -ppassword2
ERROR 1045 (28000): Access denied for user 'myheal21_manticore'@'localhost' (using password: YES)
root@283648d9031a:/# mysql -umyheal21_manticore -ppassword2 -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'myheal21_manticore'@'127.0.0.1' (using password: YES)
12

This is what I meant on UNIX socket vs TCP - it can’t connect from outside the container since the correct unix socket is inside the container:

snikolaev@dev:~/maria_manticore$ mysql -P4306 -hlocalhost -umyheal21_manticore -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'myheal21_manticore'@'localhost' (using password: YES)
13

In my error log I see “****” when the connection from manticore fails but plain username when it fails from a terminal.

I believe there must be some encryption going on, why is it showing “******” in the error log, this is driving me crazy !!!

14

Hi Sergey,
could you please send me the error.log from the Maria DB with all the startup options ?
It prints it all out when the DB starts up.

I want to compare it with mine to see if I have some weird options that got activated.

Thanks

15

Could it be something related to the charset used in the connection that is : collation ?

The only reason I can find for seeing hose “****” are encrypted data or wrong collation.

Ideas ?

16

@rlattuad

Do you use docker? Can you try it yourself?

docker run --name mariadb --rm -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p 4306:3306 mariadb:10.2.31

wait a minute for the mariadb to start up

mysql -P4306 -h0 -uroot -pmy-secret-pw -e "create database test; use test; create table t(f text); insert into t values('abc'),('def');"
mysql -P4306 -h0 -uroot -pmy-secret-pw -e "CREATE USER myheal21_manticore@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON test.* TO 'myheal21_manticore'@'%'; FLUSH PRIVILEGES;"

snikolaev@dev:~/maria_manticore$ indexer -c min.conf --all
Manticore 3.4.2 6903305@200410 release
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
Copyright (c) 2017-2020, Manticore Software LTD (http://manticoresearch.com)

using config file 'min.conf'...
indexing index 'idx'...
collected 2 docs, 0.0 MB
creating lookup: 0.0 Kdocs, 100.0% done
creating histograms: 0.0 Kdocs, 100.0% done
sorted 0.0 Mhits, 100.0% done
total 2 docs, 6 bytes
total 0.168 sec, 35 bytes/sec, 11.89 docs/sec
total 4 reads, 0.000 sec, 8.0 kb/call avg, 0.0 msec/call avg
total 15 writes, 0.000 sec, 0.3 kb/call avg, 0.0 msec/call avg

The config is:

snikolaev@dev:~/maria_manticore$ cat min.conf
source min {
    type = mysql
    sql_host = 127.0.0.1
    sql_port = 4306
    sql_user = myheal21_manticore
    sql_pass = password
    sql_db = test
    sql_query = select floor(rand()*100000), f from t
    sql_field_string = f
}

index idx {
    path = idx
    source = min
}

searchd {
    listen = 9315:mysql41
    log = sphinx_min.log
    pid_file = 9315.pid
    binlog_path =
}
17

Hi Sergey,

thanks for your reply, I do not use docker but for sure it works for you.

My user is valid as I can connect from terminal with mysql.

Something goes wrong during authentication using libmysql as in the DB error messages file the use that in manticore.conf is manticore_user becomes “********”.

I need to understand what causes the username to change to ********.

Can you point me to the Manticore source where the connection is created and parameters setup ?

I thought maybe a wrong collation for the connection may cause the string to change but I am not sure, I really do not have a clue what may be causing this.

Thanks
Roberto

18

Hi

I’ve tried to reproduce the issue again in centos 7 on a clean installation of MariaDB 10.2.31 not as a docker container, but couldn’t. I suggest you try few things:

  • first of all make sure the docker way I mentioned above works for you. The same config, the same docker
  • then try the same mariadb in docker with YOUR config
  • then try my config with your mariadb
  • also try to create another user in mariadb with a shorter name (<16 chars)
19

I have being doing some source code browsing, from what I understand:

The error I see is made of 3 parts:

  1. output from indexer when tries to connect to a sourc:
    if ( !sError.IsEmpty() )
    fprintf ( stdout, “ERROR: index ‘%s’: %s\n”, sIndexName, sError.cstr() );

  2. The sError comes from the source:
    bool CSphSource_SQL::Connect ( CSphString & sError )
    {
    // do not connect twice
    if ( m_bSqlConnected )
    return true;

    // try to connect
    if ( !SqlConnect() )
    {
    sError.SetSprintf ( “sql_connect: %s (DSN=%s)”, SqlError(), m_sSqlDSN.cstr() );
    return false;
    }

This means that the ‘**********’ I am seeing come from SqlError(), I assume this is a sphinx definition, and it is overridden by mysql_error() when the dinamix libmysql library is loaded.

So it comes from libmysql, please let me know if this is correct so I start looking at the libmysql source code, do you know where I can find it ? Would it be part of the MariaDB distribution ?

Thanks
Roberto

20

I’ll speak with our dev. team.
I’d also like to suggest again to try find a combination of config/mariadb when you don’t have the issue, so you can narrow down the possible causes. I’ve posted my ideas on what you can try above. If you can find a way to write down the instruction how anyone can reproduce the issue we’ll be glad to debug and fix it.