Maxscale isuse with mariadb : [Galera Service] Refresh rate limit exceeded for load of users' table.

[shre...@gmail.com]

Hi , I am using Mariadb galera system with maxscale

i have setup two nodes for mariadb galera system

192.168.56.x <= Mariadb galera cluster
192.168.56.y <= Mariadb galera cluster

192.168.56.z <= maxscale server

192.168.56.a <= Client system ( Aegir hostmaster i have installed )

I have installed aegir hosting system on client machine 192.168.56.a,
I have enable root access via maxscale to backend db, When my client going to connect and create site through maxscale, i always get below error

info : [readconnroute] Routed [MYSQL_COM_QUERY] to 'vagrant': GRANT ALL PRIVILEGES ON `test3storeship_0`.* TO `test3`@`127.0.0.1` IDENTIFIED BY 'wdxGDt7YUc'
info : [readconnroute] Routed [MYSQL_COM_QUERY] to 'vagrant': GRANT ALL PRIVILEGES ON `test3storeship_0`.* TO `test3`@`127.0.0.1` IDENTIFIED BY 'wdxGDt7YUc'
info : [readconnroute] Routed [MYSQL_COM_QUERY] to 'vagrant': GRANT ALL PRIVILEGES ON `test3storeship_0`.* TO `test3`@`192.168.56.102` IDENTIFIED BY 'wdxGDt7YUc'
error : [Galera Service] Refresh rate limit exceeded for load of users' table.
warning: [MySQLAuth] Galera Service: login attempt for user 'root'@[192.168.56.a]:37377, authentication failed.
error : [Galera Service] Refresh rate limit exceeded for load of users' table.
warning: [MySQLAuth] Galera Service: login attempt for user 'root'@[192.168.56.a]:37378, authentication failed.
error : [Galera Service] Refresh rate limit exceeded for load of users' table.
warning: [MySQLAuth] Galera Service: login attempt for user 'root'@[192.168.56.a]:37379, authentication failed.
error : [Galera Service] Refresh rate limit exceeded for load of users' table.
able.

Any clue on it ?

[Markus Mäkelä]

Hi,

The authentication failed messages are logged by MaxScale when it sees that there's no correct grant for the connecting client. This usually means that you lack a grant or have not created a user for both the client IP and MaxScale IP. Make sure both 192.168.56.z and 192.168.56.a have the same grants and users.

Markus

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

[shre...@gmail.com]

Hi,

thanks markus,
see i already given grants for root user for
192.168.56.z and 192.168.56.a

for maxscale user granted with maxscale@%

| Grants for maxscale@% |
+--------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'maxscale'@'%' IDENTIFIED BY PASSWORD '*B4C9xxxxxxxxxxxxxxxxxxxxxxxx' |
| GRANT SELECT ON `mysql`.`user` TO 'maxscale'@'%' |
| GRANT SELECT ON `mysql`.`db` TO 'maxscale'@'%'
+----------------------------------------------------------------------------------------------------------------------------------------------------+

====> For maxscale host

Grants for maxs...@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'@'%'
+----------------------------------------------------------------------------------------------------------------------------------------------------+

==> allow root for maxscale host


show grants for ro...@192.168.56.z;
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for ro...@192.168.56.z |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.56.z' IDENTIFIED BY PASSWORD '*B4xxxxxxxxxxxxxxxxxxxf' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------+

===> For client i allow root

show grants for ro...@192.168.56.a
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for ro...@192.168.56.a |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.56.a' IDENTIFIED BY PASSWORD '*B4C9xxxxxxxxxxxxxxx' |
+-------------------------------------------------------------------------------------------------------------------------------+

Btw i have given
192.168.56.z ( maxscale host ) grants with root and maxscale user.
192.168.56.a ( client ) grants with root user

any idea, what is missing here?

[Markus Mäkelä]

Hi,

The usual way to debug authentication problems is to execute SHOW GRANTS
FOR <user>@<host> and check that each user has the correct grants. Also
make sure that the ro...@192.168.56.a user exists and has the same grants
as ro...@192.168.56.z.

Markus

[shre...@gmail.com]

Hi Markus

Thanks for your suggestion.
as mentioned both ro...@192.168.56.a & ro...@192.168.56.z has the same grants.

mariadb galera cluster log i have attached.

2017-07-26 9:49:22 140500398418688 [Warning] Aborted connection 17 to db: 'unconnected' user: 'maxscale' host: '192.168.56.z' (Got an error reading communication packets)

[shre...@gmail.com]

generally case is our client application is automatically creating website.
for that first it's creating user and giving gratns according to website name , that part is automatically.

[Markus Mäkelä]

Hi,

The grants do look correct. Here's what I would do:

1. Go to 192.168.56.a and execute: mysql -uroot -p -h 192.168.56.x -e "SELECT 1"
   

2. Go to 192.168.56.z and execute: mysql -uroot -p -h 192.168.56.x -e "SELECT 1"

3. Go back to 192.168.56.a and execute (add --port <port> if MaxScale is not listening on the default 3306 port): mysql -uroot -p -h 192.168.56.z -e "SELECT 1"
   

If you can successfully connect to the server from both the client host and MaxScale host using the command line client, then we know the grants are OK. Then if connecting to MaxScale fails, we have a problem somewhere in MaxScale. In this case, I would suggest that you open a bug report on the MaxScale Jira: https://jira.mariadb.org/browse/MXS

Another thing you could try is to use a root@% user instead of two users for individual IPs. This could show if there's something wrong in how MaxScale interprets the grants and how the authentication is done in MaxScale.

Markus

[Markus Mäkelä]

Hi,

I think this might be a bug of some sorts unless the enable_root_user parameter is somehow turned off or not set to true. If you have enabled the parameter for the services in MaxScale and it still does not work, please open a bug report on the MariaDB Jira and attach your maxscale.cnf and MaxScale logs with all sensitive information removed.

Another option would be to create a different user, grant it all privileges and use that for the user creation. Something like the following should create a user equivalent to root: GRANT ALL ON *.* TO my-user@xyz  WITH GRANT OPTION

Markus

On 27/07/17 19:43, Shreyu wrote:

Hi markus

I have verified grants on client system and maxscale host

=> From client host 

ro...@192.168.56.a> mysql -h 192.168.56.x -u root -pxxxxx -e "SELECT 1";

+---+

| 1 |

+---+

| 1 |

+---+

ro...@192.168.56.a> > mysql -h 192.168.56.x -P 3306 -u root -pxxxxx -e "SELECT 1";

+---+

| 1 |

+---+

| 1 |

+---+

=> From Maxscale installed host

ro...@192.168.56.z > mysql -h 192.168.56.x -u root -pxxxxx -e "SELECT 1";

+---+

| 1 |

+---+

| 1 |

+---+

See client app going to create website using root access.

so it';s connect client  through maxscale host  '192.168.56.z' 

i.e xyz.com then it first create user and grants to db xyz.  like 

     x...@127.0.0.1 

     x...@192.168.56.z

     x...@192.168.56.a

then create db with name xyz and then importing structure

but as said it's seems every time said authentication failed with ro...@192.168.56.z and reload user table.  

[shre...@gmail.com]

Hi Markus


"Another option would be to create a different user, grant it all privileges and use that for the user creation" - I already tried that ( created different user, granted equivalent as root and map that user to client application to create user and db ).

It's seems everytime it's got authentication failed message, after root authentication failed - if there is a case then i will open ticket for it.

for more information i have attached log.

[shre...@gmail.com]

Created ticket MariaDB Jira - https://jira.mariadb.org/browse/MXS-1342