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

State Shown was Not Correct by 'maxctrl list servers' Command

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 22.08.3
    • Monitor
    • Red Hat Enterprise Linux Server release 7.7
      MariaDB 10.4.25
      MaxScale 22.08.2

    Description

      Last week on Nov 18, I set up three galera cluster nodes on testing MaxScale functionalities.

      After starting galera clustering and MaxScale, I executed the 'maxctrl' to verify the result:

      node 1

      maxctrl list servers
       ┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
       │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
       ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
       │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Auth Error, Down │ │ MariaDB-Monitor │ 
       ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
       │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │ 
       ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤ 
       │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │ 
       └─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘ 
      

      node2

      maxctrl list servers
      ┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
      │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
      ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
      │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
      ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤ 
      │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │
      ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤ 
      │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
      └─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘ 
      

      node3

      maxctrl list servers
       ┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
       │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
       ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
       │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
       ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
       │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │
       ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
       │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
       └─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘ 
      

      I had checked all configuration on all the nodes and I could not figure out what was wrong. Then I shut them down.

      Today, I tried again. I started the cluster first and then the MaxScale. This time the behaviour was different:

      node 1

      maxctrl list servers 
      ┌─────────┬───────────┬───────┬─────────────┬───────┬──────┬─────────────────┐
      │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
      ├─────────┼───────────┼───────┼─────────────┼───────┼──────┼─────────────────┤
      │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Down │ │ MariaDB-Monitor │
      ├─────────┼───────────┼───────┼─────────────┼───────┼──────┼─────────────────┤
      │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Down │ │ MariaDB-Monitor │ 
      ├─────────┼───────────┼───────┼─────────────┼───────┼──────┼─────────────────┤ 
      │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Down │ │ MariaDB-Monitor │ 
      └─────────┴───────────┴───────┴─────────────┴───────┴──────┴─────────────────┘
      

      Node 2

      maxctrl list servers
       ┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
       │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
       ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
       │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
       ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
       │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
       ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
       │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │
       └─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘
      

      Node 3

      maxctrl list servers 
      ┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬─────────────────┐
      │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
      ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
      │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
      ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
      │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ MariaDB-Monitor │
      ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼─────────────────┤
      │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Master, Synced, Running │ │ MariaDB-Monitor │
      └─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴─────────────────┘
      

      This time, no more 'Auth Error, Down' but all nodes 'Down'. Actually all nodes were up and running. What's wrong? Here attached are my.cnf, server.cnf and maxscale.cnf configuration files from all the nodes for your reference.

      Attachments

        1. node3-maxscale-conf.tar.gz
          172 kB
        2. node3.my.cnf.20221121.tar.gz
          2 kB
        3. node2-maxscale-conf.tar.gz
          198 kB
        4. node2.my.cnf.20221121.tar.gz
          2 kB
        5. node1-maxscale-conf.tar.gz
          171 kB
        6. node1.my.cnf.20221121.tar.gz
          2 kB
        7. maxscale logs.zip
          10 kB
        8. maxscale.log
          21 kB

        Activity

          testjetco Test Jetco added a comment -

          I dropped the db user, 'maxscale'@'%' and created the following db users with appropriate rights granted respectively:

          node 1
          -------
          'maxscale'@'ekycdb1'

          node 2
          -------
          'maxscale'@'ekycdb2'

          node 3
          -------
          'maxscale'@'ekycdb3'

          Final user table was as follows:
          MariaDB [mysql]> select user,host,password from user;
          ---------------------------------------------------------------

          User Host Password

          ---------------------------------------------------------------

          root localhost *698D9A9B433F616F4C603D16C6BFB85B89D05A06
          mysql localhost invalid
            localhost  
            ekycdb1  
          mariadb.sys localhost  
          monitor ekycdb1 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          maxscale ekycdb1 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          monitor ekycdb2 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          maxscale ekycdb2 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          monitor ekycdb3 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          maxscale ekycdb3 *6691484EA6B50DDDE1926A220DA01FA9E575C18A

          ---------------------------------------------------------------
          11 rows in set (0.001 sec)

          I stopped/started maxscale on all the nodes again. No more 'access denial' messages on mariadb.err. Maxscale status was correct on all the nodes.

          =========
          Node 1/2/3
          =========
          > maxctrl list servers;
          ┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
          │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
          ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
          │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
          ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
          │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
          ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
          │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
          └─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘
          > maxctrl list services;
          ┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
          │ Service │ Router │ Connections │ Total Connections │ Targets │
          ├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
          │ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │
          └────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘
          > maxctrl list listeners Read-Write-Service
          ┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐
          │ Name │ Port │ Host │ State │ Service │
          ├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤
          │ Read-Write-Listener │ 4006 │ 0.0.0.0 │ Running │ Read-Write-Service │
          └─────────────────────┴──────┴─────────┴─────────┴────────────────────┘

          testjetco Test Jetco added a comment - I dropped the db user, 'maxscale'@'%' and created the following db users with appropriate rights granted respectively: node 1 ------- 'maxscale'@'ekycdb1' node 2 ------- 'maxscale'@'ekycdb2' node 3 ------- 'maxscale'@'ekycdb3' Final user table was as follows: MariaDB [mysql] > select user,host,password from user; ------------ --------- ------------------------------------------ User Host Password ------------ --------- ------------------------------------------ root localhost *698D9A9B433F616F4C603D16C6BFB85B89D05A06 mysql localhost invalid   localhost     ekycdb1   mariadb.sys localhost   monitor ekycdb1 *6691484EA6B50DDDE1926A220DA01FA9E575C18A maxscale ekycdb1 *6691484EA6B50DDDE1926A220DA01FA9E575C18A monitor ekycdb2 *6691484EA6B50DDDE1926A220DA01FA9E575C18A maxscale ekycdb2 *6691484EA6B50DDDE1926A220DA01FA9E575C18A monitor ekycdb3 *6691484EA6B50DDDE1926A220DA01FA9E575C18A maxscale ekycdb3 *6691484EA6B50DDDE1926A220DA01FA9E575C18A ------------ --------- ------------------------------------------ 11 rows in set (0.001 sec) I stopped/started maxscale on all the nodes again. No more 'access denial' messages on mariadb.err. Maxscale status was correct on all the nodes. ========= Node 1/2/3 ========= > maxctrl list servers; ┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤ │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │ ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤ │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │ ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤ │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │ └─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘ > maxctrl list services; ┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Targets │ ├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤ │ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │ └────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘ > maxctrl list listeners Read-Write-Service ┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐ │ Name │ Port │ Host │ State │ Service │ ├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤ │ Read-Write-Listener │ 4006 │ 0.0.0.0 │ Running │ Read-Write-Service │ └─────────────────────┴──────┴─────────┴─────────┴────────────────────┘
          testjetco Test Jetco added a comment -

          If 'user'@'%' (either user=maxscale or user=monitor) is no longer correct, may you update the documentation on web and maxscale.cnf ? Or if there is bug, may you help to fix so 'user'@'%' alone will work again?

          testjetco Test Jetco added a comment - If 'user'@'%' (either user=maxscale or user=monitor) is no longer correct, may you update the documentation on web and maxscale.cnf ? Or if there is bug, may you help to fix so 'user'@'%' alone will work again?
          markus makela markus makela added a comment -

          Ah, I think I figured out why it fails. I didn't see that you had an anonymous user for the ekycdb1 host:

          root localhost *698D9A9B433F616F4C603D16C6BFB85B89D05A06
          mysql localhost invalid
            localhost  
            ekycdb1  
          mariadb.sys localhost  
          monitor ekycdb1 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          maxscale ekycdb1 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          monitor ekycdb2 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          maxscale ekycdb2 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          monitor ekycdb3 *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          maxscale ekycdb3 *6691484EA6B50DDDE1926A220DA01FA9E575C18A

          Since connections from this host will match the anonymous user first before matching the wildcard user, it ended up causing authentication failures from that host. For whatever reason, all hosts had an anonymous user for that host instead of their local hosts. Once you drop that ''@'ekycdb1' user from all nodes, you should be able to use a wildcard user again. Normally you don't have anonymous users in production environments as they are dropped by the mysql_secure_installation command.

          markus makela markus makela added a comment - Ah, I think I figured out why it fails. I didn't see that you had an anonymous user for the ekycdb1 host: root localhost *698D9A9B433F616F4C603D16C6BFB85B89D05A06 mysql localhost invalid   localhost     ekycdb1   mariadb.sys localhost   monitor ekycdb1 *6691484EA6B50DDDE1926A220DA01FA9E575C18A maxscale ekycdb1 *6691484EA6B50DDDE1926A220DA01FA9E575C18A monitor ekycdb2 *6691484EA6B50DDDE1926A220DA01FA9E575C18A maxscale ekycdb2 *6691484EA6B50DDDE1926A220DA01FA9E575C18A monitor ekycdb3 *6691484EA6B50DDDE1926A220DA01FA9E575C18A maxscale ekycdb3 *6691484EA6B50DDDE1926A220DA01FA9E575C18A Since connections from this host will match the anonymous user first before matching the wildcard user, it ended up causing authentication failures from that host. For whatever reason, all hosts had an anonymous user for that host instead of their local hosts. Once you drop that ''@'ekycdb1' user from all nodes, you should be able to use a wildcard user again. Normally you don't have anonymous users in production environments as they are dropped by the mysql_secure_installation command.
          testjetco Test Jetco added a comment -

          I have dropped all the invalid users and added back 'mascale'@'%' and 'monitor'@'%' with appropriate rights granted.

          Here below is the final status:

          MariaDB [mysql]> select user,host,password from user;
          ---------------------------------------------------------------

          User Host Password

          ---------------------------------------------------------------

          root localhost *698D9A9B433F616F4C603D16C6BFB85B89D05A06
          mysql localhost invalid
            localhost  
          maxscale % *6691484EA6B50DDDE1926A220DA01FA9E575C18A
          mariadb.sys localhost  
          monitor % *6691484EA6B50DDDE1926A220DA01FA9E575C18A

          ---------------------------------------------------------------
          6 rows in set (0.002 sec)

          I tried restarting maxscale again. All things went smoothly.

          node 1/2/3
          ------------
          > maxctrl list servers;
          ┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐
          │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
          ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
          │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │
          ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
          │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
          ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤
          │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │
          └─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘
          > maxctrl list services;
          ┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐
          │ Service │ Router │ Connections │ Total Connections │ Targets │
          ├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤
          │ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │
          └────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘
          > maxctrl list listeners Read-Write-Service
          ┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐
          │ Name │ Port │ Host │ State │ Service │
          ├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤
          │ Read-Write-Listener │ 4006 │ 0.0.0.0 │ Running │ Read-Write-Service │
          └─────────────────────┴──────┴─────────┴─────────┴────────────────────┘

          No error messages displayed on mariadb.err.

          testjetco Test Jetco added a comment - I have dropped all the invalid users and added back 'mascale'@'%' and 'monitor'@'%' with appropriate rights granted. Here below is the final status: MariaDB [mysql] > select user,host,password from user; ------------ --------- ------------------------------------------ User Host Password ------------ --------- ------------------------------------------ root localhost *698D9A9B433F616F4C603D16C6BFB85B89D05A06 mysql localhost invalid   localhost   maxscale % *6691484EA6B50DDDE1926A220DA01FA9E575C18A mariadb.sys localhost   monitor % *6691484EA6B50DDDE1926A220DA01FA9E575C18A ------------ --------- ------------------------------------------ 6 rows in set (0.002 sec) I tried restarting maxscale again. All things went smoothly. node 1/2/3 ------------ > maxctrl list servers; ┌─────────┬───────────┬───────┬─────────────┬─────────────────────────┬──────┬────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤ │ server1 │ 10.0.2.14 │ 33063 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │ ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤ │ server2 │ 10.0.2.15 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │ ├─────────┼───────────┼───────┼─────────────┼─────────────────────────┼──────┼────────────────┤ │ server3 │ 10.0.2.16 │ 33063 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │ └─────────┴───────────┴───────┴─────────────┴─────────────────────────┴──────┴────────────────┘ > maxctrl list services; ┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Targets │ ├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤ │ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1, server2, server3 │ └────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘ > maxctrl list listeners Read-Write-Service ┌─────────────────────┬──────┬─────────┬─────────┬────────────────────┐ │ Name │ Port │ Host │ State │ Service │ ├─────────────────────┼──────┼─────────┼─────────┼────────────────────┤ │ Read-Write-Listener │ 4006 │ 0.0.0.0 │ Running │ Read-Write-Service │ └─────────────────────┴──────┴─────────┴─────────┴────────────────────┘ No error messages displayed on mariadb.err.
          testjetco Test Jetco added a comment -

          It depends on you whether it is better to document this on maxscale.cnf or other documentation relating to maxscale. Please close this case. Thank you.

          testjetco Test Jetco added a comment - It depends on you whether it is better to document this on maxscale.cnf or other documentation relating to maxscale. Please close this case. Thank you.

          People

            markus makela markus makela
            testjetco Test Jetco
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.