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

MaxScale Connects to MySQL replica but cannot execute queries

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • None
    • N/A
    • N/A
    • None

    Description

      I am setting up a Master-slave MySQL replication setup using 3 MySQL 8 nodes(Fresh installations). I have setup a master and configured another nodes to replicate from the master which is working fine.

      I have setup MaxScale free version as a load balancer and I am intending to use it as a load balancer with read write splitting. Following is my setup(domain names are anonymized for privacy reasons).

      node1.com - master
      node2.com - slave1
      

      I can succesfully connect both nodes with `mysql -uroot -proot -h <domain-name>. Following is my MaxScale configuration.

      [maxscale]
      threads=auto
      admin_secure_gui=false
      admin_host=0.0.0.0
       
      [node1]
      type=server
      address=node1.com
      port=3306
      protocol=MySQLBackend
       
      [node2]
      type=server
      address=node2.com
      port=3306
      protocol=MySQLBackend
       
      [node3]
      type=server
      address=node3.com
      port=3306
      protocol=MySQLBackend
       
       
      [MySQL-Monitor]
      type=monitor
      module=mariadbmon
      servers=node1,node2,node3
      user=maxscale
      password=password
      monitor_interval=2s
       
      [Read-Write-Service]
      type=service
      router=readwritesplit
      servers=node1,node2
      user=maxscale
      password=password
      enable_root_user=true
       
      [Read-Write-Listener]
      type=listener
      service=Read-Write-Service
      protocol=MariaDBClient
      port=3307
      
      

      When I try `mysql -uroot -proot -h <maxscale-domain>` Maxscale can succesfully connect to the server, but the connection hangs as follows.

      ➜  ~ mysql -uroot -proot -h <maxscale-domain> -P 3307
      mysql: [Warning] Using a password on the command line interface can be insecure.
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      
      

      How can i resolve this?

      I have tried the followings to try to debug this.

      1. Using a new read only service with following configuration

      [Read-Service]
      type=service
      router=readconnroute
      servers=node1,node2
      router_options=master
      user=maxscale
      password=password
      enable_root_user=true
       
      [Read-Listener]
      type=listener
      service=Read-Service
      protocol=MariaDBClient
      port=3306
      
      

      This has led me somewhere with `mysql -uroot -proot` as it allows me to connect to both servers succesfully. When `router_options=master` I can successfully connect to master and query the database. However when `router_options=slave` I can connect to the slave but cannot query it.

      ➜  ~ mysql -uroot -proot -h <maxscale-host> -P 3306
      mysql: [Warning] Using a password on the command line interface can be insecure.
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 7
      Server version: 8.0.32-0ubuntu0.22.04.2
       
      Copyright (c) 2000, 2023, Oracle and/or its affiliates.
       
      Oracle is a registered trademark of Oracle Corporation and/or its
      affiliates. Other names may be trademarks of their respective
      owners.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      mysql> select @@hostname;
      ERROR 2013 (HY000): Lost connection to MySQL server during query
      No connection. Trying to reconnect...
      Connection id:    8
      Current database: *** NONE ***
       
      ERROR 1927 (HY000): Lost connection to backend server: network error (node2, Connection refused)
      

      This led me to believe the issue could be with the node2, but I cannot figure out the issue as it works fine if i connect the mysql client directly to node.

      2. To ensure that this is not a issue related to any slave, i removed node1 from the network and added a new node3. Then node2 is setup as the master of node3 with replication enabled. Results for the Read only service is as follows.

      With `router_options=master` :

      ➜  ~ mysql -uroot -proot -h <maxscale-host> -P 3306
      mysql: [Warning] Using a password on the command line interface can be insecure.
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 1
      Server version: 8.0.32-0ubuntu0.22.04.2
       
      Copyright (c) 2000, 2023, Oracle and/or its affiliates.
       
      Oracle is a registered trademark of Oracle Corporation and/or its
      affiliates. Other names may be trademarks of their respective
      owners.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      mysql> select @@hostname;
      ERROR 2013 (HY000): Lost connection to MySQL server during query
      No connection. Trying to reconnect...
      Connection id:    2
      Current database: *** NONE ***
       
      ERROR 1927 (HY000): Lost connection to backend server: network error (node2, Connection refused)
      

      It provides the same issue for node2 and 3 both now.

      With `router_options=slave` :

      ➜  ~ mysql -uroot -proot -h <maxscale-host> -P 3306
      mysql: [Warning] Using a password on the command line interface can be insecure.
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 1
      Server version: 8.0.32-0ubuntu0.22.04.2
       
      Copyright (c) 2000, 2023, Oracle and/or its affiliates.
       
      Oracle is a registered trademark of Oracle Corporation and/or its
      affiliates. Other names may be trademarks of their respective
      owners.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      mysql> select @@hostname;
      ERROR 2013 (HY000): Lost connection to MySQL server during query
      No connection. Trying to reconnect...
      Connection id:    2
      Current database: *** NONE ***
       
      ERROR 1927 (HY000): Lost connection to backend server: network error (node3, Connection refused)
      

      Note that all 3 are plain MySQL installations with few empty databases added + binlog enabled. Replication works fine and MaxScale GUI reports as such. It looks like the issue comes from MySQL, but actually it should be related to the MaxScale.

      Any thoughts on the matter are highly appreciated.

      Edit : Attaching maxscale.log

      MariaDB MaxScale  /var/log/maxscale/maxscale.log  Fri May  5 13:27:43 2023
      ----------------------------------------------------------------------------
      2023-05-05 13:27:43   notice : Module 'mariadbmon' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libmariadbmon.so'.
      2023-05-05 13:27:43   notice : Module 'readconnroute' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadconnroute.so'.
      2023-05-05 13:27:43   notice : Module 'readwritesplit' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadwritesplit.so'.
      2023-05-05 13:27:43   notice : Using up to 75.53GiB of memory for query classifier cache
      2023-05-05 13:27:43   notice : syslog logging is disabled.
      2023-05-05 13:27:43   notice : maxlog logging is enabled.
      2023-05-05 13:27:43   notice : Host: '<maxscale-host>' OS: Linux@5.4.0-135-generic, #152-Ubuntu SMP Wed Nov 23 20:19:22 UTC 2022, x86_64 with 64 processor cores (64.00 available).
      2023-05-05 13:27:43   notice : Total main memory: 503.56GiB (503.56GiB usable).
      2023-05-05 13:27:43   notice : MariaDB MaxScale 23.02.1 started (Commit: 5de96ecc39da80a992fc8f362d22a030a844bef5)
      2023-05-05 13:27:43   notice : MaxScale is running in process 3097068
      2023-05-05 13:27:43   notice : Configuration file: /etc/maxscale.cnf
      2023-05-05 13:27:43   notice : Log directory: /var/log/maxscale
      2023-05-05 13:27:43   notice : Data directory: /var/lib/maxscale
      2023-05-05 13:27:43   notice : Module directory: /usr/lib/x86_64-linux-gnu/maxscale
      2023-05-05 13:27:43   notice : Service cache: /var/cache/maxscale
      2023-05-05 13:27:43   notice : Working directory: /var/log/maxscale
      2023-05-05 13:27:43   notice : Module 'qc_sqlite' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libqc_sqlite.so'.
      2023-05-05 13:27:43   notice : Query classification results are cached and reused. Memory used per thread: 1.18GiB
      2023-05-05 13:27:43   notice : Password encryption key file '/var/lib/maxscale/.secrets' not found, using configured passwords as plaintext.
      2023-05-05 13:27:43   notice : The systemd watchdog is Enabled. Internal timeout = 30s
      2023-05-05 13:27:43   notice : Using HS256 for JWT signatures
      2023-05-05 13:27:43   notice : Started REST API on [0.0.0.0]:8989
      

      Edit 2:

      Attaching mysql general log for reference. Nothing specific to logging or execution of the query gets logged in the mysql general log either. However when i restart the maxscale server it succesfull y connects it seems which is proven by the statement `2023-05-05T11:45:44.693829Z 19 Connect maxscale@<maxscale-host> on using TCP/IP`

      2023-05-05T11:45:44.226596Z	   18 Query	SHOW STATUS LIKE 'Uptime'
      2023-05-05T11:45:44.228199Z	   18 Query	SELECT @@global.server_id, @@read_only
      2023-05-05T11:45:44.228584Z	   18 Query	SHOW SLAVE STATUS
      2023-05-05T11:45:44.532309Z	   18 Quit	
      2023-05-05T11:45:44.693829Z	   19 Connect	maxscale@<maxscale-host> on  using TCP/IP
      2023-05-05T11:45:44.694243Z	   19 Quit	
      2023-05-05T11:45:44.695671Z	   20 Connect	maxscale@<maxscale-host> on  using TCP/IP
      2023-05-05T11:45:44.696020Z	   20 Query	SET SQL_MODE=''
      2023-05-05T11:45:44.696292Z	   20 Query	SET @@session.autocommit=1
      2023-05-05T11:45:44.696512Z	   20 Query	SET NAMES latin1
      2023-05-05T11:45:44.696768Z	   20 Query	SELECT id, @@global.collation_server FROM information_schema.collations WHERE collation_name=@@global.collation_server
      

      Edit 3: Attaching MaxScale info log as suggested by https://stackoverflow.com/users/7633996/markusjm

      2023-05-08 11:05:34   info   : Routed [COM_QUERY] to 'node-a' select @@version_comment limit 1
      2023-05-08 11:05:34   info   : Storing COM_QUERY while in state 'Handshaking': select @@version_comment limit 1
      2023-05-08 11:05:34   info   : Server 'node-a' failed
      2023-05-08 11:05:34   info   : Stopped Read-Service client session [1]
      2023-05-08 11:05:38   info   : Accept authentication from 'admin', using password. Request: /auth
      2023-05-08 11:05:44   info   : Found matching user 'maxscale'@'%' for client 'maxscale'@'2001:1458:202:56::111:1b' with sufficient privileges.
      2023-05-08 11:05:44   info   : New session for server node-a. Connections : 1
      2023-05-08 11:05:44   info   : Started Read-Service client session [2] for 'maxscale' from 2001:1458:202:56::100:1b
      2023-05-08 11:05:44   info   : Server 'node-a' failed
      2023-05-08 11:05:44   info   : Stopped Read-Service client session [2]
      2023-05-08 11:07:03   info   : Found matching user 'maxscale'@'%' for client 'maxscale'@'2001:1458:202:56::111:1b' with sufficient privileges.
      2023-05-08 11:07:03   info   : New session for server node-a. Connections : 1
      2023-05-08 11:07:03   info   : Started Read-Service client session [3] for 'maxscale' from 2001:1458:202:56::111:1b
      2023-05-08 11:07:03   info   : Server 'node-a' failed
      2023-05-08 11:07:03   info   : Stopped Read-Service client session [3]
      2023-05-08 11:10:39   info   : Found matching user 'maxscale'@'%' for client 'maxscale'@'2001:1458:202:56::111:1b' with sufficient privileges.
      

      This is after setting `log_info=true` for maxscale and recreating maxscale MySQL user with `mysql_native_password`. Following is the user info.

      mysql> select * from user where user='maxscale'\G;
      *************************** 1. row ***************************
                          Host: %
                          User: maxscale
                   Select_priv: N
                   Insert_priv: N
                   Update_priv: N
                   Delete_priv: N
                   Create_priv: N
                     Drop_priv: N
                   Reload_priv: N
                 Shutdown_priv: N
                  Process_priv: N
                     File_priv: N
                    Grant_priv: N
               References_priv: N
                    Index_priv: N
                    Alter_priv: N
                  Show_db_priv: Y
                    Super_priv: N
         Create_tmp_table_priv: N
              Lock_tables_priv: N
                  Execute_priv: N
               Repl_slave_priv: N
              Repl_client_priv: Y
              Create_view_priv: N
                Show_view_priv: N
           Create_routine_priv: N
            Alter_routine_priv: N
              Create_user_priv: N
                    Event_priv: N
                  Trigger_priv: N
        Create_tablespace_priv: N
                      ssl_type: 
                    ssl_cipher: 0x
                   x509_issuer: 0x
                  x509_subject: 0x
                 max_questions: 0
                   max_updates: 0
               max_connections: 0
          max_user_connections: 0
                        plugin: mysql_native_password
         authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2629D1E19
              password_expired: N
         password_last_changed: 2023-05-08 09:02:05
             password_lifetime: NULL
                account_locked: N
              Create_role_priv: N
                Drop_role_priv: N
        Password_reuse_history: NULL
           Password_reuse_time: NULL
      Password_require_current: NULL
               User_attributes: NULL
      1 row in set (0.00 sec)
      

      mysql> show grants for 'maxscale'@'%';
      +-------------------------------------------------------------------+
      | Grants for maxscale@%                                             |
      +-------------------------------------------------------------------+
      | GRANT SHOW DATABASES, REPLICATION CLIENT ON *.* TO `maxscale`@`%` |
      | GRANT SELECT ON `mysql`.`columns_priv` TO `maxscale`@`%`          |
      | GRANT SELECT ON `mysql`.`db` TO `maxscale`@`%`                    |
      | GRANT SELECT ON `mysql`.`procs_priv` TO `maxscale`@`%`            |
      | GRANT SELECT ON `mysql`.`proxies_priv` TO `maxscale`@`%`          |
      | GRANT SELECT ON `mysql`.`tables_priv` TO `maxscale`@`%`           |
      | GRANT SELECT ON `mysql`.`user` TO `maxscale`@`%`                  |
      +-------------------------------------------------------------------+
      7 rows in set (0.00 sec)
      

      Maria

      Attachments

        Activity

          People

            markus makela markus makela
            kalana Kalana Wijethunga
            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.