authentication error of client that writes to mariadb cluster via maxscale

[warri...@gmail.com]

Hi,

Below error is seen in the log of maxscale

2018-01-23 09:26:05   error  : [MySQLAuth] Client hostname lookup failed, getnameinfo() returned: 'Name or service not known'.
2018-01-23 09:26:05   error  : [RW-Service] Refresh rate limit exceeded for load of users' table.
2018-01-23 09:26:05   warning: [MySQLAuth] RW-Service: login attempt for user 'root'@[::ffff:10.0.0.214]:44236, authentication failed.

My setup  has 

1) MariaDB  docker swarm service cluster of 3 nodes configured in multimaster mode

2) Maxscale  docker swarm service  (1 replica) which is configured to monitor and direct writes to MariaDB cluster nodes.

Below is the configuration:

# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md


# Global parameters
#
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md


[maxscale]
threads=1


# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#


[master]
type=server
address=10.0.0.210
port=3306
protocol=MySQLBackend


[master2]
type=server
address=10.0.0.208
port=3306
protocol=MySQLBackend


[master3]
type=server
address=10.0.0.209
port=3306
protocol=MySQLBackend




# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md


[MySQL Monitor]
type=monitor
module=galeramon
servers=master,master2,master3
user=root
passwd=test
monitor_interval=10000


# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#


# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md


# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md


[RW-Service]
type=service
router=readwritesplit
servers=master
user=root
passwd=test
max_slave_connections=100%


# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md




# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#
[Write-Listener]
type=listener
service=RW-Service
port=4306
protocol=MySQLClient
#socket=/tmp/rwsplit.sock




[MaxAdmin]
type=service
router=cli


[MaxAdmin Unix Listener]
type=listener
service=MaxAdmin
protocol=maxscaled
socket=default


[MaxAdmin Inet Listener]
type=listener
service=MaxAdmin
protocol=maxscaled
address=localhost
port=6603

3) Below is the output of maxadmin:

4)  Docker Swarm service  Java Client  connecting to Maxscale service published port 4306

package clients;


import java.sql.*;


public class Client {
    public static void main(String[] args) throws SQLException {
        //create connection for a server installed in localhost, with a user "root" with no password
        try (Connection conn = DriverManager.getConnection("jdbc:mariadb://stack_maxscale:4306/", "root", "test")) {
            // create a Statement
            try (Statement stmt = conn.createStatement()) {
                //execute query
                try (ResultSet rs = stmt.executeQuery("SELECT 'Hello World!'")) {
                    //position result to first
                    rs.first();
                    System.out.println(rs.getString(1)); //result is "Hello World!"
                }
            }
        }
    }
}

All the services are in the same Docker Swarm. 

MariaDB credentials are root/test and regular mysql client succeeds to connect to mysqld process with mysql -uroot -ptest

What's wrong?

[Markus Mäkelä]

Hi,

I think that this might be a case where the somewhat "special" root user is causing the problems. By default, MaxScale will not allow the root user to connect through MaxScale. This is done to prevent users with full access from being used remotely. Since it's possible to grant the same privileges to other users, one could argue that this behavior is not horribly useful.

Please try adding enable_root_user=true to the service definition. The description of the parameter can be found here: https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-21-mariadb-maxscale-configuration-usage-scenarios#enable_root_user

If it still doesn't work, I'd like to ask you to check that the root user has grants for both the client IP (10.0.0.214) as well as the MaxScale IP (by reasonable assumption, root@stack_maxscale). Also check that the passwords and grants are the same.

Markus

--
You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
Markus Mäkelä, Software Engineer
MariaDB Corporation
t: +358 40 7740484 | Skype: markus.j.makela 

[warri...@gmail.com]

Hi, Markus.

Thanks a lot.

1) enabling root user as you suggested has helped, client succeeds to connect to MariaDB via MaxScale

2) There's still error though in maxscale.log:

error  : [MySQLAuth] Client hostname lookup failed, getnameinfo() returned: 'Name or service not known'.

Wondering why is it there? Is it a critical error?

I'm just wondering. If i get it right MaxScale tries to resolve client hostname and doesn't succeed. I suspect that MaxScale doesn't succeed because MariaDB client and MaxScale are deployed as Docker containers and for MaxScale to succeed to resolve client host, it needs to query Docker Swarm DNS server (which contains entries per container virtual ip)

Regards

Roman

[Markus Mäkelä]

Hi,

1) enabling root user as you suggested has helped, client succeeds to connect to MariaDB via MaxScale

OK, good to hear that it works.

2) There's still error though in maxscale.log:

error  : [MySQLAuth] Client hostname lookup failed, getnameinfo() returned: 'Name or service not known'.

Wondering why is it there? Is it a critical error?

The error is not a critical error but it does tell us that a hostname lookup failed. This means that the authentication was not immediately successful on the first try and MaxScale tried to resolve the client hostname to an IP address. If possible, please try and see if adding address=0.0.0.0 for the listener removes this error (the default is address=::, the IPv6 all-interfaces address). If this removes the errors, we might have a minor bug in MaxScale where it does an extra hostname lookup for IPv6 addresses.

Markus

[warri...@gmail.com]

Thanks a lot for fast response, Markus.

Setting  address=0.0.0.0 removed the error from the log.

Just checked, MaxScale version inside the docker image is 2.1.11.

Regards.

[Markus Mäkelä]

Hi,

Could you open up a bug report for that error on the MariaDB Jira under the MaxScale project? We'd love to fix that bug and having a official bug report for it would help refer to this problem in the future.

Markus

--

You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[warri...@gmail.com]

Done