We have a replication (GTID based) setup as described below:
ms1 = master mariaDB server (v. 10.3.13)
ms2 = slave mariaDB server (v. 10.3.13)
ms3 = MaxScale proxy (v. 2.3.8)
>root@ms3 ~ # maxadmin list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
ms1 | xxx | 3306 | 0 | Master, Running
ms2 | xxx | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
(Below maxscale.cnf)
We have tested the replication setup and was working OK with a simple PHP file with a 'INSERT' statement.
Then we tried a failover with the following scenario but it didn't work.
Below the details:
we stop ms1 server
>root@ms3 ~ # maxadmin list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
ms1 | xxx | 3306 | 0 | Down
ms2 | xxx | 3306 | 0 | Master, Running
-------------------+-----------------+-------+-------------+--------------------
we run the same PHP file and we got the following PHP warning:
PHP Warning: mysqli::__construct(): (HY000/1045): failed to create new session in xxx on line 8
Below is the related MaxScale log message:
2019-07-04 15:05:10 error : Failed to create new routing session. Couldn't find eligible candidate server. Freeing allocated resources.
2019-07-04 15:05:10 error : Failed to create new router session for service 'Write-Service'. See previous errors for more details.
2019-07-04 15:05:10 info : Start Write-Service client session [8] for 'xxx' from xxx failed, will be closed as soon as all related DCBs have been closed.
we tried as solution to enable detect_standalone_master setting (https://mariadb.com/kb/en/mariadb-maxscale-21-mysql-monitor/#detect_standalone_master) but still the same problem.
So it seems that failover does not work
Any help?
maxscale.cnf
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-23/
# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-maxscale-configuration-usage-scenarios/
[maxscale]
threads=auto
query_retries=2
syslog=1
maxlog=1
log_warning=1
log_notice=1
log_info=1
# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#
[ms1]
type=server
address=xxx
port=3306
protocol=MariaDBBackend
[ms2]
type=server
address=xxx
port=3306
protocol=MariaDBBackend
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-monitor/
[Replication-Monitor]
type=monitor
module=mariadbmon
servers=ms1, ms2
user=maxscale
password=max123
monitor_interval=2000
assume_unique_hostnames=ON
#failover_timeout=5
auto_failover=true
auto_rejoin=true
failcount=2
#master_failure_timeout=2
#verify_master_failure=true
switchover_timeout=10
detect_standalone_master=true
events=master_down
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-readconnroute/
[Write-Service]
type=service
router=readconnroute
router_options=master
servers=ms1
user=maxscale
password=max123
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/
[Read-Service]
type=service
router=readconnroute
router_options=slave
servers=ms2
user=maxscale
password=max123
# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://mariadb.com/kb/en/mariadb-maxscale-23-maxadmin-admin-interface/
[MaxAdmin-Service]
type=service
router=cli
# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#
[Write-Listener]
type=listener
service=Write-Service
protocol=MariaDBClient
port=3306
[Read-Listener]
type=listener
service=Read-Service
protocol=MariaDBClient
port=3307
[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default