auto_rejoin with SSL replication fails setting the right parameters

The following testing environment was used:

• one master server, mariadb-node-0, SSL enforced
• one slave, mariadb-node-1, already connected, SSL enforced
• SSL replication

• configuration for mariadb on both nodes is the same:

[mariadb]
; ssl
ssl_ca = /etc/mysql/tls/ca.crt
ssl_key = /etc/mysql/tls/server.key
ssl_cert = /etc/mysql/tls/server.crt
ssl_cipher = TLS_AES_256_GCM_SHA384
require_secure_transport = ON

; replication
server_id = 2000
log_bin
log_basename = mariadb-replica
log_slave_updates = ON
gtid-domain-id = 0
gtid_strict_mode = ON
gtid_ignore_duplicates = OFF

Note: server_id is 2000 on master, 2001 on slave.

• maxscale configuration is as follows:

[maxscale]
threads = auto
admin_host = 0.0.0.0
admin_port = 8989
admin_auth = true
admin_enabled = true
admin_gui = true
admin_secure_gui = true
skip_name_resolve = true
admin_ssl_ca_cert = /var/lib/maxscale/tls/ca.crt
admin_ssl_cert = /var/lib/maxscale/tls/maxscale-server.crt
admin_ssl_key = /var/lib/maxscale/tls/maxscale-server.key

[mariadb-node-0]
type = server
address = mariadb-node-0
port = 3306
protocol = MariaDBBackend
ssl = true
ssl_ca_cert = /var/lib/maxscale/tls/ca.crt
ssl_cert = /var/lib/maxscale/tls/maxscale-client.crt
ssl_key = /var/lib/maxscale/tls/maxscale-client.key
ssl_verify_peer_certificate = true
ssl_verify_peer_host = true

[mariadb-node-1]
type = server
address = mariadb-node-1
port = 3306
protocol = MariaDBBackend
ssl = true
ssl_ca_cert = /var/lib/maxscale/tls/ca.crt
ssl_cert = /var/lib/maxscale/tls/maxscale-client.crt
ssl_key = /var/lib/maxscale/tls/maxscale-client.key
ssl_verify_peer_certificate = true
ssl_verify_peer_host = true

[MariaDB-Monitor]
type = monitor
module = mariadbmon
servers = mariadb-node-0,mariadb-node-1
user = maxscale
password = B6F8EFC865B79ACE2C05AF03DAB768FD59BCBE7335A73CD9F173E67FE7454F31CE04436B7115E5E42809A98F420D2D0362D499467E79C209691F8D08F5F08FA1
monitor_interval = 50
replication_master_ssl = true
auto_failover = true
auto_rejoin = true
enforce_read_only_slaves = true

[Read-Write-Service]
type = service
router = readwritesplit
servers = mariadb-node-0,mariadb-node-1
user = maxscale
password = B6F8EFC865B79ACE2C05AF03DAB768FD59BCBE7335A73CD9F173E67FE7454F31CE04436B7115E5E42809A98F420D2D0362D499467E79C209691F8D08F5F08FA1
enable_root_user = true
max_slave_connections = 2
filters = MaxScaleHint

[Read-Write-Listener]
type = listener
service = Read-Write-Service
protocol = MariaDBClient
port = 4406
ssl = true
ssl_ca_cert = /var/lib/maxscale/tls/ca.crt
ssl_cert = /var/lib/maxscale/tls/maxscale-server.crt
ssl_key = /var/lib/maxscale/tls/maxscale-server.key
ssl_verify_peer_certificate = true
ssl_cipher = TLS_AES_256_GCM_SHA384, ECDHE-ECDSA-AES256-GCM-SHA384, ECDHE-RSA-AES256-GCM-SHA384, ECDHE-ECDSA-CHACHA20-POLY1305, ECDHE-RSA-CHACHA20-POLY1305, ECDHE-ECDSA-AES128-GCM-SHA256, ECDHE-RSA-AES128-GCM-SHA256, ECDHE-ECDSA-AES256-SHA384, ECDHE-RSA-AES256-SHA384, ECDHE-ECDSA-AES128-SHA256, ECDHE-RSA-AES128-SHA256

[MaxScaleHint]
type = filter
module = hintfilter

• the maxscale user is configured as this:
  DROP USER IF EXISTS 'maxscale'@'%';
  CREATE USER 'maxscale'@'%' IDENTIFIED BY '9vTIl8TYzwLkF7RrSzgmHCY1TroNgeTlfvWw7s9o';
  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.roles_mapping TO 'maxscale'@'%';
  GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
  GRANT SELECT ON mysql.user TO 'maxscale'@'%';
  GRANT BINLOG ADMIN, READ_ONLY ADMIN, RELOAD, REPLICA MONITOR, REPLICATION MASTER ADMIN, REPLICATION REPLICA ADMIN, REPLICATION REPLICA, REPLICATION CLIENT, SHOW DATABASES ON . TO 'maxscale'@'%';
  ALTER USER 'maxscale'@'%' REQUIRE ISSUER '/C=US/ST=CA/L=City/O=Whatever Inc./CN=Certificate Authority/emailAddress=certs@localhost';

• note the REQUIRE ISSUER above, this will cause problems later.

• on MASTER (mariadb-node-0):
  MariaDB [(none)]> SHOW MASTER STATUS\G
  File: mariadb-replica-bin.000002
  Position: 29724811
  Binlog_Do_DB:
  Binlog_Ignore_DB:
  1 row in set (0.000 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'gtid_%';
----------------------------------+

----------------------------------+

----------------------------------+
10 rows in set (0.002 sec)

• on SLAVE (mariadb-node-1):
  MariaDB [(none)]> SHOW SLAVE STATUS\G
  Slave_IO_State: Waiting for master to send event
  Master_Host: mariadb-node-0
  Master_User: maxscale
  Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: mariadb-replica-bin.000002
  Read_Master_Log_Pos: 29724811
  Relay_Log_File: mariadb-replica-relay-bin.000002
  Relay_Log_Pos: 11512802
  Relay_Master_Log_File: mariadb-replica-bin.000002
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Replicate_Rewrite_DB:
  Replicate_Do_DB:
  Replicate_Ignore_DB:
  Replicate_Do_Table:
  Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
  Last_Errno: 0
  Last_Error:
  Skip_Counter: 0
  Exec_Master_Log_Pos: 29724811
  Relay_Log_Space: 11513121
  Until_Condition: None
  Until_Log_File:
  Until_Log_Pos: 0
  Master_SSL_Allowed: Yes
  Master_SSL_CA_File: /etc/mysql/tls/ca.crt
  Master_SSL_CA_Path:
  Master_SSL_Cert: /etc/mysql/tls/mariadb-replication-client.crt
  Master_SSL_Cipher:
  Master_SSL_Key: /etc/mysql/tls/mariadb-replication-client.key
  Seconds_Behind_Master: 0
  Master_SSL_Verify_Server_Cert: Yes
  Last_IO_Errno: 0
  Last_IO_Error:
  Last_SQL_Errno: 0
  Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
  Master_Server_Id: 2000
  Master_SSL_Crl:
  Master_SSL_Crlpath:
  Using_Gtid: Slave_Pos
  Gtid_IO_Pos: 0-2000-74
  Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
  Parallel_Mode: optimistic
  SQL_Delay: 0
  SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  Slave_DDL_Groups: 3
  Slave_Non_Transactional_Groups: 0
  Slave_Transactional_Groups: 11
  1 row in set (0.001 sec)

• while shutting down mariadb-node-0 and checking the maxscale log, mariadb-node-1 is promoted, as expected, as the new master:

2023-12-11 06:24:28 notice : Server changed state: mariadb-node-0[mariadb-node-0:3306]: master_down. [Master, Running] -> [Down]
2023-12-11 06:24:28 warning: [mariadbmon] Master has failed. If master does not return in 4 monitor tick(s), failover begins.
2023-12-11 06:24:28 notice : [mariadbmon] Selecting a server to promote and replace 'mariadb-node-0'. Candidates are: 'mariadb-node-1'.
2023-12-11 06:24:28 notice : [mariadbmon] Selected 'mariadb-node-1'.
2023-12-11 06:24:28 notice : [mariadbmon] Performing automatic failover to replace failed master 'mariadb-node-0'.
2023-12-11 06:24:28 notice : [mariadbmon] Failover 'mariadb-node-0' -> 'mariadb-node-1' performed.
2023-12-11 06:24:28 notice : Server changed state: mariadb-node-1[mariadb-node-1:3306]: new_master. [Slave, Running] -> [Master, Running]

• run a few transactions through maxscale to increase the GTID on mariadb-node-1 then start mariadb-node-0, it should automatically reconnect to mariadb-node-1 and continue replication.
  2023-12-11 06:24:58 notice : Server changed state: mariadb-node-0[mariadb-node-0:3306]: server_up. [Down] -> [Running]

• check replication status on mariadb-node-0:
  MariaDB [(none)]> SHOW SLAVE STATUS\G
  Slave_IO_State: Connecting to master
  Master_Host: mariadb-node-1
  Master_User: maxscale
  Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File:
  Read_Master_Log_Pos: 4
  Relay_Log_File: mariadb-replica-relay-bin.000001
  Relay_Log_Pos: 4
  Relay_Master_Log_File:
  Slave_IO_Running: Connecting
  Slave_SQL_Running: Yes
  Replicate_Rewrite_DB:
  Replicate_Do_DB:
  Replicate_Ignore_DB:
  Replicate_Do_Table:
  Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
  Last_Errno: 0
  Last_Error:
  Skip_Counter: 0
  Exec_Master_Log_Pos: 4
  Relay_Log_Space: 256
  Until_Condition: None
  Until_Log_File:
  Until_Log_Pos: 0
  Master_SSL_Allowed: Yes
  Master_SSL_CA_File:
  Master_SSL_CA_Path:
  Master_SSL_Cert:
  Master_SSL_Cipher:
  Master_SSL_Key:
  Seconds_Behind_Master: NULL
  Master_SSL_Verify_Server_Cert: No
  Last_IO_Errno: 1045
  Last_IO_Error: error connecting to master 'maxscale@mariadb-node-1:3306' - retry-time: 60 maximum-retries: 100000 message: Access denied for user 'maxscale'@'10.244.2.189' (using password: YES)
  Last_SQL_Errno: 0
  Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
  Master_Server_Id: 0
  Master_SSL_Crl:
  Master_SSL_Crlpath:
  Using_Gtid: Current_Pos
  Gtid_IO_Pos: 0-2000-74
  Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
  Parallel_Mode: optimistic
  SQL_Delay: 0
  SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  Slave_DDL_Groups: 0
  Slave_Non_Transactional_Groups: 0
  Slave_Transactional_Groups: 0
  1 row in set (0.000 sec)

Everything looks good except for Master_SSL_CA_File, Master_SSL_Cert, Master_SSL_Key and Master_SSL_Verify_Server_Cert. As the maxscale@% user was created with a REQUIRE ISSUER, any connection attempts on mariadb-node-1 will fail.

Altering the maxscale@% user definition to remove ISSUER and start the slave thread on mariadb-node-0, solves the issue:

root@mariadb-node-1:/# mariadb -p${MARIADB_ROOT_PASSWORD} --batch --execute "ALTER USER 'maxscale'@'%' REQUIRE ISSUER ''"

root@mariadb-node-0:/# mariadb -p${MARIADB_ROOT_PASSWORD} --batch --execute "START SLAVE\G"
root@mariadb-node-0:/# mariadb -p${MARIADB_ROOT_PASSWORD} --batch --execute "SHOW SLAVE STATUS\G"
Slave_IO_State: Waiting for master to send event
Master_Host: mariadb-node-1
Master_User: maxscale
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-replica-bin.000001
Read_Master_Log_Pos: 46891394
Relay_Log_File: mariadb-replica-relay-bin.000002
Relay_Log_Pos: 17166667
Relay_Master_Log_File: mariadb-replica-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Rewrite_DB:
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 46891394
Relay_Log_Space: 17166986
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2001
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Current_Pos
Gtid_IO_Pos: 0-2001-99
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 8
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 17

I've already tried using a custom /etc/mysql/conf.d/client.cnf with the appropriate values for ssl_*.

I think that the best approach would be to add new monitor parameters for ssl_ca, ssl_ca_path, ssl_cert, ssl_key, ssl_cipher, ssl_verify_server_cert.