Can you try replacing router=readconnroute with router=readwritesplit and then remove router_options from the configuration? The readwritesplit routing module should log a more detailed error whenever a node fails.

I did a quick test with the MySQL 8.0.32 docker image and an identical user but I wasn't able to reproduce the problem. However, it did reveal that readconnroute does not log enough information on the info level which is something that can be improved.

Hi @markus,

I had already tried this with a `router=readwritesplit`. The behaviour was that the MySQL connection would load and just hang in the middle at the following point without no clue on what's going on.

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

However, if I take the replica out of the Maxscale monitoring, it will successfully connect to master which led me to believe that the issue with the replica and led me to bring a 2nd replica to the setup which ultimately gave the same result.

Anyway, after all the changes done to the setup which discussing this question, that issue seems to have gone away but the connection still drops after logging into MySQL. Following is the MaxScale log.

```
2023-05-10 09:55:53
warning: Super user 'root'@'2001:1458:202:56::100:1b' logged in to service 'Read-Write-Service'.
2023-05-10 09:55:53
info : Target connection counts:
2023-05-10 09:55:53
info : current operations : 0 in node-a Master, Running
2023-05-10 09:55:53
info : current operations : 0 in node-b Slave, Running
2023-05-10 09:55:53
info : Connected to 'node-a'
2023-05-10 09:55:53
info : Selected Primary: node-a
2023-05-10 09:55:53
info : Connected to 'node-b'
2023-05-10 09:55:53
info : Selected Replica: node-b
2023-05-10 09:55:53
info : Started Read-Write-Service client session [10] for 'root' from 2001:1458:202:56::100:1b
2023-05-10 09:55:53
info : Primary 'node-a' failed: #HY000: Lost connection to backend server: network error (node-a: 111, Connection refused)
2023-05-10 09:55:53
error : Lost connection to the primary server, closing session. Lost connection to primary server while connection was idle. Connection has been idle for 0 seconds. Error caused by: #HY000: Lost connection to backend server: network error (node-a: 111, Connection refused). Last close reason: <none>. Last error:
2023-05-10 09:55:53
error : Network error in connection to server 'node-b', session in state 'Stopping session' (DCB::State::POLLING): 111, Connection refused
2023-05-10 09:55:57
info : Stopped Read-Write-Service client session [10]
2023-05-10 09:56:01
info : Found matching user 'root'@'%' for client 'root'@'2001:1458:202:56::100:1b' with sufficient privileges.
2023-05-10 09:56:01
warning: Super user 'root'@'2001:1458:202:56::100:1b' logged in to service 'Read-Write-Service'.
2023-05-10 09:56:01
info : Target connection counts:
2023-05-10 09:56:01
info : current operations : 0 in node-a Master, Running
2023-05-10 09:56:01
info : current operations : 0 in node-b Slave, Running
2023-05-10 09:56:01
info : Connected to 'node-a'
2023-05-10 09:56:01
info : Selected Primary: node-a
2023-05-10 09:56:01
info : Connected to 'node-b'
2023-05-10 09:56:01
info : Selected Replica: node-b
2023-05-10 09:56:01
info : Started Read-Write-Service client session [11] for 'root' from 2001:1458:202:56::100:1b
2023-05-10 09:56:01
info : Primary 'node-a' failed: #HY000: Lost connection to backend server: network error (node-a: 111, Connection refused)
2023-05-10 09:56:01
error : Lost connection to the primary server, closing session. Lost connection to primary server while connection was idle. Connection has been idle for 0 seconds. Error caused by: #HY000: Lost connection to backend server: network error (node-a: 111, Connection refused). Last close reason: <none>. Last error:
2023-05-10 09:56:01
error : Network error in connection to server 'node-b', session in state 'Stopping session' (DCB::State::POLLING): 111, Connection refused
2023-05-10 09:56:05
info : Stopped Read-Write-Service client session [11]
```

The server immediately disconnects after the authentication and I can't figure out why.

In the meantime could you verify that if MaxScale free version can only be used with 1 MySQL server? Because that could be the reason behind all this.

No, the license if MaxScale does not affect its functionality and thus this is not caused by it. The MaxScale 23.02 license states:

Additional Use Grant: You may use the Licensed Work when your application
uses the Licensed Work with a total of less than three
server instances in production.

I believe that less than three means either one or two servers.

The Connection refused error suggests that there's a problem connecting to the database and the rejection happens at the network level. Oddly enough, it seems like the monitor is able to connect to the servers and thus the state of the servers seems to be OK. Can you verify that you can connect to the database nodes from the MaxScale host? SSH-ing over to the server and then using the mysql command line client to connect to the database using the root user should verify that the network between the nodes is functional.

Another thing to check would be to see if there are multiple network interfaces on the MaxScale host. If so, you could try to bind to a specific interface using the global local_address parameter in MaxScale. Perhaps the network interface that the client connections from MaxScale use isn't able to connect to the database nodes and the connections used by the monitors in MaxScale somehow end up using a different network interface.

Hi,

This brings something interesting to my attention. node1(working primary) is setup on my local machine. node2, node3 and <maxscale-host> are on actual enteprise servers. The authentication is done via SSH keys. So my personal key is added on each machine, but the machines does not share each other's keys. This leads to the fact that I can SSH from node1 to any of the machines but I cant SSH from any other machine to a different machine because the keys are not configured.

However, I can try directly connecting via MySQL -h command to each machine from the node2 which is working fine. At the moment I am reluctant to install mysql-client libraries on <maxscale-host> as it looks like the limitation of 2 servers will stop me from evaluating the product further.

About trying out the local_address, I got the following error on log. I believe the issue could be here. The server machines are not configured to use ipv6 and they use ipv4 only. The reason could be why it can connect to node1(my personal machine) is because it has a ipv4 but the other machines do not. Is there a way to force MaxScale to use ipv4 instead of ipv6 ?

```
2023-05-10 10:37:55
info : Found matching user 'root'@'%' for client 'root'@'<>' with sufficient privileges.
2023-05-10 10:37:55
warning: Super user 'root'@'<>' logged in to service 'Read-Write-Service'.
2023-05-10 10:37:55
info : Target connection counts:
2023-05-10 10:37:55
info : current operations : 0 in node-a Master, Running
2023-05-10 10:37:55
info : current operations : 0 in node-b Slave, Running
2023-05-10 10:37:55
error : Could not bind connecting socket to local address "<>", connecting to server using default local address: Address family not supported by protocol
2023-05-10 10:37:55
info : Connected to 'node-a'
2023-05-10 10:37:55
info : Selected Primary: node-a
2023-05-10 10:37:55
error : Could not bind connecting socket to local address "<>, connecting to server using default local address: Address family not supported by protocol
2023-05-10 10:37:55
info : Connected to 'node-b'
2023-05-10 10:37:55
info : Selected Replica: node-b
2023-05-10 10:37:55
info : Started Read-Write-Service client session [2] for 'root' from 2001:1458:202:56::100:1b
2023-05-10 10:37:55
info : Primary 'node-a' failed: #HY000: Lost connection to backend server: network error (node-a: 111, Connection refused)
2023-05-10 10:37:55
error : Lost connection to the primary server, closing session. Lost connection to primary server while connection was idle. Connection has been idle for 0 seconds. Error caused by: #HY000: Lost connection to backend server: network error (node-a: 111, Connection refused). Last close reason: <none>. Last error:
2023-05-10 10:37:55
error : Network error in connection to server 'node-b', session in state 'Stopping session' (DCB::State::POLLING): 111, Connection refused
2023-05-10 10:37:58
info : Stopped Read-Write-Service client session [2]

I don't know why or how you have a user with <> as the host but that definitely doesn't seem like something that's a valid network address. What's the value of local_client you used?

So far nothing indicates that IPv6 is the problem. You can bind the listening ports of MaxScale to the IPv4 all interface by using address=0.0.0.0 in the listener sections.

sorry for the confusion. I replaced ip addresses with <> for security reasons. Please see a more sensible log below after adding address = 0.0.0.0 to the listeners and setting ipv4 address of maxscale host to `local_address` when initiating the call to maxscale host from a mysql client in my local machine(node1

```
2023-05-10 10:49:51
info : Found matching user 'root'@'%' for client 'root'@'<node1-ipv4>' with sufficient privileges.
2023-05-10 10:49:51
warning: Super user 'root'@'<node1-ipv4>' logged in to service 'Read-Write-Service'.
2023-05-10 10:49:51
info : Target connection counts:
2023-05-10 10:49:51
info : current operations : 0 in node-a Master, Running
2023-05-10 10:49:51
info : current operations : 0 in node-b Slave, Running
2023-05-10 10:49:51
error : Could not bind connecting socket to local address "<maxscale-host-ipv4>", connecting to server using default local address: Address family not supported by protocol
2023-05-10 10:49:51
info : Connected to 'node-a'
2023-05-10 10:49:51
info : Selected Primary: node-a
2023-05-10 10:49:51
error : Could not bind connecting socket to local address "<maxscale-host-ipv4>", connecting to server using default local address: Address family not supported by protocol
2023-05-10 10:49:51
info : Connected to 'node-b'
2023-05-10 10:49:51
info : Selected Replica: node-b
2023-05-10 10:49:51
info : Started Read-Write-Service client session [2] for 'root' from <node1ipv4>
2023-05-10 10:49:51
info : Primary 'node-a' failed: #HY000: Lost connection to backend server: network error (node-a: 111, Connection refused)
2023-05-10 10:49:51
error : Lost connection to the primary server, closing session. Lost connection to primary server while connection was idle. Connection has been idle for 0 seconds. Error caused by: #HY000: Lost connection to backend server: network error (node-a: 111, Connection refused). Last close reason: <none>. Last error:
2023-05-10 10:49:51
error : Network error in connection to server 'node-b', session in state 'Stopping session' (DCB::State::POLLING): 111, Connection refused
2023-05-10 10:49:54
info : Stopped Read-Write-Service client session [2]
```

Are you using hostnames for the address parameters in the server definitions? If possible, could you try if switching them to IP addresses has any effect?

Oh ! shoot. I should have check this at the beginning. Yup, using IPs resolved the problem which means that this should be related to DNS. Thanks a lot for the pointers

Thanks for reporting back. I think we can improve some of the error and info level messages and maybe even show the IP addresses that hostnames resolve to, hopefully this will help with any future DNS related problems.

I'll close this as Not a Bug since it turned out to be a DNS related issue.

Yes, I think improving logging would reduce a lot of debugging effort.

Can you let me know how I can check the the server limitation of 2 servers. I now have 3 servers being monitored by the maxscale monitor.
When i set route_options=master traffic gets routed to node1 and when i set it up route_options=slave it gets routed to node2 which kind of shows that it handle only 2 servers. But could it be that it does not know how to share the traffic between 2 replicas so it keeps sending to the first replica only?

The readconnroute module only does load balancing at the connection level. When a client connects to the readconnroute service, it picks a server and creates a connection there. From that point onwards, the readconnroute module does no load balancing and routes every request, regardless of its type, to the same server.

The readwritesplit module is more versatile and is capable of connecting to multiple servers and routing reads to different nodes based on various load conditions. This is usually what you want since the load conditions at the connection creation time are not always a good representation of the load conditions later on. The readwritesplit router looks at the load conditions of the replicas before each query and routes it to the one that is the leasy busy. This sort of dynamic load balancing is especially important for connections that exist for longer periods of time (e.g. client-side connection pools like HikariCP) but it is equally as important for proper load distribution even for short connections.

Now that the connection problems have been fixed, I'd recommend using the readwritesplit module if you expect individual requests to be load balanced across multiple nodes. Another thing to keep in mind while testing is that if you only have one client connection, the traffic can end up going only to a single node since the same node always appers as the best candidate. For a proper load balancing test, you need more than one client connection and some load to properly stress the system. A program like mysqlslap or sysbench are useful tools for testing load balancing.

Thanks a lot for the pointers and detailed explanations marcus. I will try them out.