Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-1510

Unable to login after SET password

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 2.1.9
    • Fix Version/s: N/A
    • Component/s: N/A
    • Labels:

      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)

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            andriuskulbis Andrius Kulbis
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration