[MXS-1833] Connection timeout when creating CONNECT tables for maxinfo in Galera cluster with SSL set up Created: 2018-04-26  Updated: 2020-08-25  Resolved: 2018-04-27

Status: Closed
Project: MariaDB MaxScale
Component/s: maxinfo
Affects Version/s: 2.2.4
Fix Version/s: 2.2.6

Type: Bug Priority: Critical
Reporter: Valerii Kravchuk Assignee: markus makela
Resolution: Fixed Votes: 0
Labels: galera, regression, ssl


 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



 Comments   
Comment by markus makela [ 2018-04-26 ]

This would appear to be caused by the same problem that caused MXS-1805.

Comment by markus makela [ 2018-04-26 ]

A fix can be found on the 2.2-markusjm branch on GitHub. The commit that fixes the bug is e5f26e2980ff727481ee1640df0146310ab48bf6.

Generated at Thu Feb 08 04:09:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.