Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
2.1.9
Description
I am using MaxScale v2.19 to load balance queries to 3-node gallera cluster. I decided to run phpMyAdmin connected to MaxScale for database administration.
The problem occurs when I try to change database user password via phpMyAdmin or via CLI connected to MaxScale using SET password for 'user'@'%' = PASSWORD('****');|
I get confirmation about successfully changed password, but then I am unable to connect to my database using new nor old password. Connecting to the each node directly works as expected with new password. I see that the password hash has changed in every node of cluster.
Only thing that fixes this going to one of the cluster nodes directly and running same SET password for 'user'@'%' = PASSWORD('****') query, then I am able to connect to the database via MaxScale again.
General log of each node on SET password operation:
|
db01-test |
173511 Connect user@172.12.12.64 as anonymous on
|
173511 Query SET CHARACTER SET 'utf8mb4'
|
173511 Query SET collation_connection = 'utf8mb4_unicode_ci'
|
173511 Query SET lc_messages = 'lt_LT'
|
173510 Query SELECT `plugin` FROM `mysql`.`user` WHERE `User` = "user" AND `Host` = "%" LIMIT 1
|
173510 Query SET `old_passwords` = 0
|
173510 Query SET password = PASSWORD('test123')
|
|
db02-test |
171527 Connect user@172.12.12.64 as anonymous on
|
171527 Query SET CHARACTER SET 'utf8mb4'
|
171527 Query SET collation_connection = 'utf8mb4_unicode_ci'
|
171527 Query SET lc_messages = 'lt_LT'
|
171526 Query SET `old_passwords` = 0
|
171526 Query SET password = PASSWORD('test123')
|
171526 Query FLUSH PRIVILEGES
|
|
db03-test |
171399 Connect user@172.12.12.64 as anonymous on
|
171399 Query SET CHARACTER SET 'utf8mb4'
|
171399 Query SET collation_connection = 'utf8mb4_unicode_ci'
|
171399 Query SET lc_messages = 'lt_LT'
|
171398 Query SET `old_passwords` = 0
|
171398 Query SET password = PASSWORD('test123')
|
|
My config:
|
maxscale.cnf |
|
|
[maxscale]
|
threads=2
|
log_debug=1
|
log_auth_warnings=true
|
|
|
|
|
[db01-test]
|
type=server
|
address=172.12.12.101
|
port=3307
|
protocol=MySQLBackend
|
|
|
[db02-test]
|
type=server
|
address=172.12.12.102
|
port=3307
|
protocol=MySQLBackend
|
|
|
[db03-test]
|
type=server
|
address=172.12.12.103
|
port=3307
|
protocol=MySQLBackend
|
|
|
[Galera Monitor]
|
type=monitor
|
module=galeramon
|
servers=db01-test,db02-test,db03-test
|
user=maxscale
|
passwd=***
|
monitor_interval=10000
|
|
|
|
|
[Galera Service]
|
type=service
|
router=readwritesplit
|
servers=db01-test,db02-test,db03-test
|
user=maxscale
|
passwd=***
|
localhost_match_wildcard_host=true
|
auth_all_servers=true
|
|
|
[MaxAdmin Service]
|
type=service
|
router=cli
|
|
|
[Read-Write Listener]
|
type=listener
|
service=Galera Service
|
protocol=MySQLClient
|
port=3306
|
|
|
[MaxAdmin Listener]
|
type=listener
|
service=MaxAdmin Service
|
protocol=maxscaled
|
socket=default
|
I get the following error in MaxScale.log (due to a fact that I can login directly to db03-test):
[MySQLBackend] Invalid authentication message from backend 'db03-test'. Error code: 1045, Msg : #28000Access denied for user 'user'@'172.12.12.64' (using password: YES)