Ubuntu 14.04
MariaDB 10.0.0.17 / Galera

@André Bauer
This may be configuration issue. If you post on the maxscale google group(maxscale@googlegroups.com) with your configuration file (max.cnf) content - some one will respond,

Thanks for th tip. Will try it.
For completeness reasons also attach it here:

# maxscale.cnf for galera cluster

[maxscale]

threads=4

[Galera Service]

type=service

router=readconnroute

router_options=synced

servers=mysql1, mysql2, mysql3

user=maxscale

passwd=maxscale

[Galera Listener]

type=listener

service=Galera Service

protocol=MySQLClient

address=192.168.10.250

port=3306

[mysql1]

type=server

address=192.168.10.31

port=3306

protocol=MySQLBackend

[mysql2]

type=server

address=192.168.10.32

port=3306

protocol=MySQLBackend

[mysql3]

type=server

address=192.168.10.33

port=3306

protocol=MySQLBackend

[Galera Monitor]

type=monitor

module=galeramon

servers=mysql1, mysql2, mysql3

user=maxscale

passwd=maxscale

monitor_interval=10000

[CLI]

type=service

router=cli

[CLI Listener]

type=listener

service=CLI

protocol=maxscaled

address=localhost

port=6603

The error message about the root user seems to suggest that it's trying to use the root user. MaxScale by default doesn't allow root user but this can be enabled by adding 'enable_root_user=1' to the service configuration. Also adding the 'localhost_match_wildcard_host=1' should help if some of the host names resolve to localhost.

If this is not the case and adding the root user fails, if possible, please provide the error logs for that attempt.

I also have this problem with non root users...

markus makela (JIRA) <jira@mariadb.atlassian.net> schrieb am Mo., 16. März

I tried the "enable_root_user=1" and "localhost_match_wildcard_host=1" options but still have this problem, even for non root users.
I also tested haproxy on port 3307 again which works as expected.

we suggest to issue this command via maxadmin client or telnet interface:

MaxScale> show dbusers Galera\ Service

this will print the users@hosts loaded from backend and will help us understand your authentication issues

example:

MaxScale> show dbusers My_Service
Users table data
Hashtable: 0x31c4830, size 52
No. of entries: 5
Average chain length: 0.1
Longest chain length: 1
User names: repl@%, one@%, maxuser@127.0.0.1, one@127.0.0.1, maxuser@%

/usr/local/skysql/maxscale/bin/maxadmin -pskysql show dbusers Galera\ Service:

Users table data
Hashtable: 0x7f6784001800, size 52
No. of entries: 45
Average chain length: 0.9
Longest chain length: 7
User names: root@%....

I hope it ok to hide the usernames...

I just found: https://github.com/mariadb-corporation/MaxScale/wiki/Install-troubleshooting
Indeed i had some databases with underscores.
I was able to fix the problem for my non root user.
Root user still has the problem.

I understand you don't want to share tat informations.

Could you please check if your 'testuser' or 'root' is loaded with % or 127.0.0.1 or anther IP?

If you are connecting to a remote IP you need root@IP or root@%

You can also try testing the mysql connection without db name, just to understand whether the issue is db name.

I've found 2 root users in "User names": "root@%" and "root@127.0.0.1"
Is this a problem?

Before it worked without db name and did not work with db name for the root user.
For whatever reason it works now also with the db name, which has an underscore in its name.
Im completly sure this did not work before. Strange...

Thanks for your help!

xyz@127.0.0.1 is required for connecting client to maxscale using socket or 127.0.0.1

Depending on your backend mysql.user content it may be also required 'localhost_match_wildcard_host=1

enable_root_user=1 is of course required if you want to load root user.

Could please that summarise whether you have another issue?

We may then try to understand remainig issue or just close the ticket.

Thanks for evaluating MaxScale

Seems to work now.
I only have some errors left in /usr/local/skysql/maxscale/log/skygw_msg1.log:

"2015-03-17 16:37:20 Galera Service: login attempt for user foo_bar, user not found."

But the user exists.

Is the 'foo_bar' user listed in show dbusers Galera\ Service ?

Does it have % and 127.0.0.1, or naother Ip set?

Are you connecting with db name or without it?

Yes, its listed as "foo_bar@%" and has only select rights on "foo.my_table".

What works is:
mysql -uroot -ppass -h mysql.local foo -e "select * from my_table WHERE login = 'my@mail.local'"
mysql -ufoo_bar -ppass -h mysql.local

What not works:
mysql -ufoo_bar -ppass -h mysql.local foo -e "select * from my_table WHERE login = 'my@mail.local'"

It seems an issue with db grants for foo_bar.

Please try

mysql -ufoo_bar -ppass -h mysql.local -D foo -e "select 1"

mysql -ufoo_bar -ppass -h mysql.local -D foo (just to check if you have the mysql prompt) and then issue

SELECT 1

Another check is from mysql.db database, just check the 'foo_bar' user has the grant on db 'foo'

Currently MaxScale supports only grants for all or specific db and not for a table in a db.

The all db grants is checked from mysql.user: select_priv = Y or N

Query example for the specific db grants check for user 'foo_bar':

MySQL> select user,host,db from mysql.db where user='foo_bar';

mysql -ufoo_bar -ppass -h mysql.local -D foo -e "select 1":
ERROR 1045 (28000): Access denied for user...

mysql -ufoo_bar -ppass -h mysql.local -D foo:
ERROR 1045 (28000): Access denied for user...

mysql -ufoo_bar -ppass -h mysql.local:
Welcome to the MySQL monitor...

No, the user foo_bar did not had grant on foo.
Normaly only my root users have grant option set.
I've set grant for this foo_bar now, which did the trick.

Thanks again!

Will this behaviour change in the future?
Never needed to set grant before.

If dbname is set at connection time it will be checked before letting the user get in.

a change we have in mind, for a near future, is that MaxScale will support the dbname auth with at least the table grant, as other users suggested.

The idea is that MaxScale has the same behavior of mysql backend db:

if the user cannot access the db at connect time the same will happen when connecting to MaxScale.

Feel free to start a discussion in maxscale@googlegroups.com about that, if you think the current implementation is too demanding for your current setup.

We can close the ticket for now I guess.

Thanks for all your tests and the time spent here.

Massimiliano

Works as designed as noted in Massmiliano's comment