[#MXS-1342] 'enable_root_user' parameter is somehow turned off or not set to true & got Refresh rate limit exceeded for load of users' table.

Comment by Shreyas Parikh [ 2017-08-02 ]

My client software getting authentication failed message while creating database through maxscale. it seems somehow "enable_root_user" parameter is turned off or not set to true and

More information
User grants

=> Grants for maxscale@192.168.56.z

GRANT ALL PRIVILEGES ON *.* TO 'maxscale'@'192.168.56.z' IDENTIFIED BY PASSWORD '*B4C9xxxxxxxxxxxxxxxx'

GRANT SELECT ON `mysql`.`db` TO 'maxscale'@'192.168.56.z'

GRANT SELECT ON `mysql`.`tables_priv` TO 'maxscale'@'192.168.56.z'

GRANT SELECT ON `mysql`.`user` TO 'maxscale'@'192.168.56.z'

GRANT SELECT ON `mysql`.`tables_priv` TO 'maxscale'@'%'

=> Grants for root@192.168.56.z

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.56.z' IDENTIFIED BY PASSWORD '*B4xxxxxxxxxxxxxxxxxxxf' WITH GRANT OPTION

=> Grants for root@192.168.56.a

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.56.a' IDENTIFIED BY PASSWORD '*B4C9xxxxxxxxxxxxxxx' |

I have attached maxscale.cnf and log file for detail

Comment by Shreyas Parikh [ 2017-08-02 ]

if you need more information kindly here.

Comment by markus makela [ 2017-08-02 ]

The grants do appear to be correct. I think I'll look into whether the rather curious hostname has anything to do with this.

Comment by Shreyas Parikh [ 2017-08-03 ]

Meanwhile with name resolution i had tried but same issue.

Comment by Shreyas Parikh [ 2017-08-09 ]

any update @markus makela !!

Comment by Shreyas Parikh [ 2017-08-17 ]

any process or is there any other way to use with enable root user ?

Comment by markus makela [ 2017-08-17 ]

Sorry for the lack of reply, seems this issue has fallen off the radar. I'll assign this to myself and investigate it.

Comment by Shreyas Parikh [ 2017-08-17 ]

Ahh ok , Thanks markus.

Comment by markus makela [ 2017-08-21 ]

With the following configuration, I wasn't able to reproduce the issue. My host was on 192.168.121.1, MaxScale was on 192.168.121.242. This was tested with both hostnames and IP addresses. Setting enable_root_user=false caused an error as would be expected. I'll see if testing with identical hostnames to your setup will trigger a problem.

Grants

create user 'root'@'192.168.121.1' identified by 'root';

grant all on *.* to 'root'@'192.168.121.1' with grant option;

create user 'root'@'192.168.121.242' identified by 'root';

grant all on *.* to 'root'@'192.168.121.242' with grant option;

Configuration

[maxscale]

threads=4

log_info=1

log_debug=1

[mariadb1]

type=server

address=192.168.121.126

port=3306

protocol=MySQLBackend

persistpoolmax=100

persistmaxtime=3600

[mariadb2]

type=server

address=192.168.121.106

port=3306

protocol=MySQLBackend

persistpoolmax=100

persistmaxtime=3600

[mariadb3]

type=server

address=192.168.121.62

port=3306

protocol=MySQLBackend

persistpoolmax=100

persistmaxtime=3600

[mariadb4]

type=server

address=192.168.121.251

port=3306

protocol=MySQLBackend

persistpoolmax=100

persistmaxtime=3600

[MaxAdmin Service]

type=service

router=cli

[MaxAdmin Listener]

type=listener

service=MaxAdmin Service

protocol=maxscaled

socket=default

port=3307

[Galera Monitor]

type=monitor

module=galeramon

servers=mariadb1,mariadb2,mariadb3,mariadb4

user=maxskysql

passwd=skysql

monitor_interval=500

[Galera Service]

type=service

router=readconnroute

router_options=synced

servers=mariadb1,mariadb2,mariadb3,mariadb4

user=maxskysql

passwd=skysql

enable_root_user=1

[Galera Listener]

type=listener

service=Galera Service

protocol=MySQLClient

port=3306

address=0.0.0.0

socket=/tmp/DB.Cluster

Test

[markusjm@localhost ~]$ mysql -u root -proot -h $maxscale_IP -e "select user()"

+----------------------+

| user()               |

+----------------------+

| root@192.168.121.242 |

+----------------------+

[markusjm@localhost ~]$ mysql -u root -proot -h $maxscale_IP -e "show grants"

+----------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@192.168.121.242                                                                                                              |

+----------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.121.242' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |

+----------------------------------------------------------------------------------------------------------------------------------------------+

[markusjm@localhost ~]$ echo $maxscale_IP

192.168.121.242

Comment by Shreyas Parikh [ 2017-08-21 ]

seems configuration is ok and same as i configured.
1. Need to check host file can you show your host file.
2. SELECT user,host,password,Grant_priv,Super_priv FROM mysql.user; - to check proper grants.
3. I am using Aegir2 as client application to create website.

To reproduce issue and need to create testing scenario :
Maxscale host: 192.168.121.242
Aegir host : 192.168.121.1

Here, Aegir host connect maxscale and create website through it.

Comment by markus makela [ 2017-08-23 ]

I've discarded my testing setup so the exact hostname I used aren't available. I did rerun the test with a different setup but I'm having problems with getting the hostname resolution to work in MariaDB. With hostnames, MaxScale authentication would work but backend authentication would fail. Without hostnames, everything works as expected.

I suspect that this could be a problem caused by the hostname resolution not working. I recommend confirming this by using explicit IP addresses instead of hostnames.

Comment by Shreyas Parikh [ 2017-08-23 ]

"I suspect that this could be a problem caused by the hostname resolution not working", might be. let me check it .

Ok I will attach my MariaDB system configuration files.

Comment by markus makela [ 2017-09-15 ]

shreyu have you found out the reason for your problems?

Comment by markus makela [ 2017-09-19 ]

I'll close this as we haven't been able to reproduce this. If a systematic way to reproduce is found, it can be reopened.

[MXS-1342] 'enable_root_user' parameter is somehow turned off or not set to true & got Refresh rate limit exceeded for load of users' table. Created: 2017-08-02 Updated: 2017-09-19 Resolved: 2017-09-19
Status:	Closed
Project:	MariaDB MaxScale
Component/s:	N/A
Affects Version/s:	2.1.4
Fix Version/s:	N/A