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

Connection timeout when creating CONNECT tables for maxinfo in Galera cluster with SSL set up

    XMLWordPrintable

    Details

      Description

      It seems some change in MaxScale 2.2.x or maxinfo there comparing to older 2.1.x versions leads to the following problem while creating CONNECT table to monitor MaxScale via maxinfo service in Galera clusters with at least 2 nodes:

      [openxs@fc23 maria10.2]$ bin/mysql --defaults-file=/home/openxs/galera/mynode1.cnf -umyuser -pmypwd --host=127.0.0.1 --port=4008
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MySQL connection id is 10
      Server version: 10.2.12 2.2.4-maxscale Source distribution
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MySQL [(none)]> use maxinfo
      Database changed
      MySQL [maxinfo]> CREATE TABLE IF NOT EXISTS `maxinfo`.`variables` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW VARIABLES';
      Query OK, 0 rows affected (0.13 sec)
       
      MySQL [maxinfo]> CREATE TABLE IF NOT EXISTS `maxinfo`.`status` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW STATUS';
      Query OK, 0 rows affected (0.14 sec)
       
      MySQL [maxinfo]> CREATE TABLE IF NOT EXISTS `maxinfo`.`services` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW SERVICES';
      Query OK, 0 rows affected (0.13 sec)
       
      MySQL [maxinfo]> CREATE TABLE IF NOT EXISTS `maxinfo`.`listeners` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW LISTENERS';
      Query OK, 0 rows affected (0.10 sec)
       
      MySQL [maxinfo]> CREATE TABLE IF NOT EXISTS `maxinfo`.`sessions` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW SESSIONS';
      ERROR 1159 (08S01): Got timeout reading communication packets
      ...
      

      It seems with only one node there is no problem.

      Related settings from my testing environment are:

      1. maxscale.cnf:

      [openxs@fc23 maria10.2]$ cat /etc/maxscale.cnf
      # MaxScale documentation:
      # https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22/
       
      # Global parameters
      #
      # Complete list of configuration options:
      # https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-maxscale-configuration-usage-scenarios/
       
      [maxscale]
      threads=auto
       
      # Server definitions
      #
      # Set the address of the server to the network
      # address of a MariaDB server.
      #
       
      [server1]
      type=server
      address=127.0.0.1
      port=3306
      protocol=MariaDBBackend
      ssl=required
      ssl_ca_cert=/etc/mysql-ssl/ca-cert.pem
      ssl_key=/etc/mysql-ssl/client-key.pem
      ssl_cert=/etc/mysql-ssl/client-cert.pem
       
      [server2]
      type=server
      address=127.0.0.1
      port=3307
      protocol=MariaDBBackend
      ssl=required
      ssl_ca_cert=/etc/mysql-ssl/ca-cert.pem
      ssl_key=/etc/mysql-ssl/client-key.pem
      ssl_cert=/etc/mysql-ssl/client-cert.pem
       
      # Monitor for the servers
      #
      # This will keep MaxScale aware of the state of the servers.
      # MariaDB Monitor documentation:
      # https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-monitor/
       
      [Galera-Monitor]
      type=monitor
      module=galeramon
      servers=server1,server2
      user=myuser
      passwd=mypwd
      monitor_interval=100
      available_when_donor=false
      disable_master_role_setting=true
      disable_master_failback=1
       
      # Service definitions
      #
      # Service Definition for a read-only service and
      # a read/write splitting service.
      #
       
      # ReadConnRoute documentation:
      # https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-readconnroute/
       
      [RCR-Service]
      type=service
      router=readconnroute
      servers=server1,server2
      user=myuser
      passwd=mypwd
      #router_options=slave
       
      # ReadWriteSplit documentation:
      # https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-readwritesplit/
       
      #[Read-Write-Service]
      #type=service
      #router=readwritesplit
      #servers=server1
      #user=myuser
      #passwd=mypwd
       
      # This service enables the use of the MaxAdmin interface
      # MaxScale administration guide:
      # https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-maxadmin-admin-interface/
       
      [MaxAdmin-Service]
      type=service
      router=cli
       
      [MaxInfo]
      passwd=pw
      user=maxinfo
      router=maxinfo
      type=service
       
      # Listener definitions for the services
      #
      # These listeners represent the ports the
      # services will listen on.
      #
       
      [RCR-Listener]
      type=listener
      service=RCR-Service
      protocol=MariaDBClient
      port=4008
      address=127.0.0.1
      ssl=required
      ssl_ca_cert=/etc/mysql-ssl/ca-cert.pem
      ssl_key=/etc/mysql-ssl/server-key.pem
      ssl_cert=/etc/mysql-ssl/server-cert.pem
       
      #[Read-Write-Listener]
      #type=listener
      #service=Read-Write-Service
      #protocol=MariaDBClient
      #port=4006
       
      [MaxAdmin-Listener]
      type=listener
      service=MaxAdmin-Service
      protocol=maxscaled
      socket=default
      ssl=required
      ssl_ca_cert=/etc/mysql-ssl/ca-cert.pem
      ssl_key=/etc/mysql-ssl/server-key.pem
      ssl_cert=/etc/mysql-ssl/server-cert.pem
       
      [MaxInfo-Listener]
      port=9003
      protocol=MySQLClient
      service=MaxInfo
      type=listener
      #ssl=required
      ssl_ca_cert=/etc/mysql-ssl/ca-cert.pem
      ssl_key=/etc/mysql-ssl/server-key.pem
      ssl_cert=/etc/mysql-ssl/server-cert.pem
       
      [openxs@fc23 maria10.2]$
      

      2. Two nodes (10.2.x) have the following configuration files:

      [openxs@fc23 maria10.2]$ cat ~/galera/mynode1.cnf
      [mysqld]
      datadir=/home/openxs/galera/node1
      port=3306
      socket=/tmp/mysql-node1.sock
      pid-file=/tmp/mysql-node1.pid
      log-error=/tmp/mysql-node1.err
      binlog_format=ROW
      innodb_autoinc_lock_mode=2
       
      wsrep_on=ON
      wsrep_provider=/home/openxs/galera/libgalera_smm.so
      wsrep_cluster_name = singlebox
      wsrep_node_name = node1
      wsrep_cluster_address=gcomm://127.0.0.1:4567,127.0.0.1:5020?pc.wait_prim=no
       
      #performance_schema=1
      #innodb_thread_concurrency=4
      #wsrep_slave_threads=4
       
      log_bin=1
      server_id=10
       
      ssl
      ssl_ca=/home/openxs/ssl/ca-cert.pem
      ssl_key=/home/openxs/ssl/server-key.pem
      ssl_cert=/home/openxs/ssl/server-cert.pem
       
      [client]
      socket=/tmp/mysql-node1.sock
       
      ssl
      ssl_ca=/home/openxs/ssl/ca-cert.pem
      ssl_key=/home/openxs/ssl/client-key.pem
      ssl_cert=/home/openxs/ssl/client-cert.pem
       
      [openxs@fc23 maria10.2]$ cat ~/galera/mynode2.cnf
      [mysqld]
      datadir=/home/openxs/galera/node2
      port=3307
      socket=/tmp/mysql-node2.sock
      pid-file=/tmp/mysql-node2.pid
      log-error=/tmp/mysql-node2.err
      binlog_format=ROW
      innodb_autoinc_lock_mode=2
       
      wsrep_on=ON
      wsrep_provider=/home/openxs/galera/libgalera_smm.so
      wsrep_cluster_name = singlebox
      wsrep_node_name = node2
      wsrep_cluster_address=gcomm://127.0.0.1:4567,127.0.0.1:5020?pc.wait_prim=no
      wsrep_provider_options = "base_port=5020;"
       
      #performance_schema=1
      #innodb_thread_concurrency=4
      #wsrep_slave_threads=4
       
      log_bin=1
      server_id=10
       
      ssl
      ssl_ca=/home/openxs/ssl/ca-cert.pem
      ssl_key=/home/openxs/ssl/server-key.pem
      ssl_cert=/home/openxs/ssl/server-cert.pem
       
      [client]
      socket=/tmp/mysql-node2.sock
       
      ssl
      ssl_ca=/home/openxs/ssl/ca-cert.pem
      ssl_key=/home/openxs/ssl/client-key.pem
      ssl_cert=/home/openxs/ssl/client-cert.pem
       
      [openxs@fc23 maria10.2]$
      

      3. To reproduce, install maxscale and start it as a service. Initialize node1, start it as a new cluster, then start another node and make sure they formed the cluster:

      bin/mysqld_safe --defaults-file=/home/openxs/galera/mynode1.cnf --wsrep-new-cluster &
      bin/mysqld_safe --defaults-file=/home/openxs/galera/mynode2.cnf &
      

      Connect to maxinfo at port 9003 and make sure SHOW commands works as expected.

      Then connect to maxscale router at port 4008 as -umyuser -pmypwd (the user is configured for SSL and certificates are created based on this blog post, https://mariadb.com/resources/blog/mariadb-maxscale-21-and-ssl-certificates), load CONNECT engine plugin, and then run this script:

      create database maxinfo;
      use maxinfo;
      CREATE TABLE IF NOT EXISTS `maxinfo`.`variables` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW VARIABLES';
      CREATE TABLE IF NOT EXISTS `maxinfo`.`status` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW STATUS';
      CREATE TABLE IF NOT EXISTS `maxinfo`.`services` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW SERVICES';
      CREATE TABLE IF NOT EXISTS `maxinfo`.`listeners` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW LISTENERS';
      CREATE TABLE IF NOT EXISTS `maxinfo`.`sessions` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW SESSIONS';
      CREATE TABLE IF NOT EXISTS `maxinfo`.`clients` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW CLIENTS';
      CREATE TABLE IF NOT EXISTS `maxinfo`.`servers` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW SERVERS';
      CREATE TABLE IF NOT EXISTS `maxinfo`.`modules` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW MODULES';
      CREATE TABLE IF NOT EXISTS `maxinfo`.`monitors` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW MONITORS';
      CREATE TABLE IF NOT EXISTS `maxinfo`.`eventtimes` ENGINE=CONNECT table_type=mysql dbname='' CONNECTION='mysql://maxinfo:pw@127.0.0.1:9003' SRCDEF='SHOW EVENTTIMES';
      

      You should see error message like this for some tables:

      ERROR 1159 (08S01): Got timeout reading communication packets

      I've seen cases when one of CREATEs just hanged. Galera nodes are avaiable directly then:

      [openxs@fc23 maria10.2]$ bin/mysql --defaults-file=/home/openxs/galera/mynode1.cnf -umyuser -pmypwd
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 13
      Server version: 10.2.15-MariaDB-log Source distribution
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> show tables from maxinfo;
      +-------------------+
      | Tables_in_maxinfo |
      +-------------------+
      | listeners         |
      | services          |
      | status            |
      | variables         |
      +-------------------+
      4 rows in set (0.00 sec)
      

      But everything related to maxscale hangs:

      [openxs@fc23 maria10.2]$ maxscale --version
      MaxScale 2.2.4
      [openxs@fc23 maria10.2]$ maxadmin list servers
      -- hangs
      

        Attachments

          Activity

            People

            Assignee:
            markus makela markus makela
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: