[CONJ-741] when only one slave and one master, connector/j only connect to master in some case Created: 2019-10-25  Updated: 2020-05-29

Status: Open
Project: MariaDB Connector/J
Component/s: aurora, Failover
Affects Version/s: 2.2.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Hui Dong Assignee: Diego Dupin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

AWS Aurora with 1 master node and 1 slave node


 Description   

In below scenarios, connector/j only connect to master at last.

Scenario 1:
1, Start aurora with 1 master node and 1 slave node.

2, Start spring boot program using below URL to connect aurora.
jdbc:mysql:aurora://xxx-cluster.cluster-yyy-zzz.rds.amazonaws.com/dbname
Now we can set readOnly to true to access slave node, and set readOnly to false to access master node.

3. Remove slave node from aurora cluster
In this time, no matter what readOnly to set, the connector/j will access master node. It's OK.
Sometimes, SQLException may happen when slave node is not removed completely.

4. Add slave node into aurora cluster again.
In this time, although setting readOnly to true, the connector/j still connect to master node.
It's not OK. We want to connect slave node for read only connection.
Now we can access slave node db from command line's mysql command.

Scenario 2:
1, Start aurora with only 1 master node.

2, Start spring boot program using below URL to connect aurora.
jdbc:mysql:aurora://xxx-cluster.cluster-yyy-zzz.rds.amazonaws.com/dbname
Now no matter what readOnly to set, the connector/j will access master node. It's OK.

3. Add slave node into aurora cluster again.
In this time, although setting readOnly to true, the connector/j still connect to master node.
It's not OK. We want to connect slave node for read only connection.
We can access slave node db from command line's mysql command.

Cause:
In org.mariadb.jdbc.internal.protocol.AuroraProtocol#loop method, when only one slave and connect fail or no slave, it maybe not execute org.mariadb.jdbc.internal.failover.impl.AuroraListener#retrieveAllEndpointsAndSet to retrieve endpoints.

 Comments   
Comment by Hui Dong [ 2019-12-02 ]

We found we set pool=true in jdbcurl and we also use tomcat jdbc pool with spring boot. So I think this make our program create two connection pool. And this make our program not reconnect database normally when new node added into cluster.

Comment by Peter Lebedev [ 2020-05-29 ]

We also have a similar problem with 2.5.4 driver, and we have not had it with 1.7.2 driver.

Here is our use case:
1. We have a task that runs every minute inside tomcat, that can connect to either an Aurora database or a traditional mysql, that is determined when tomcat starts.
2. This task is supposed to read from either an Aurora reader, or a mysql slave running in read-only mode
3. This task creates a read-only connection.
4. For business reasons this task does "SHOW SLAVE STATUS" to determine how far behind the slave is.
When it is executed against Aurora reader, it returns nothing, and when it's executed agains a traditional slave, it returns the status, so we can determine how far behind the slave is.
5. If "SHOW SLAVE STATUS" is executed against the Aurora writer node, it returns values like
Slave_IO_Running='No', Slave_SQL_Running='No', Seconds_Behind_Master='null'
and the task fails, and then re-tried in a minute.

Per documentation, when aurora mode is used, the driver supposed to pick a reader node if a connection is explicitly set to read only:

aurora	This mode supports connection failover in an Amazon Aurora cluster. This mode does support load-balancing reads on slave instances if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a slave instance to execute read queries for a connection.
 
This mode has been available since MariaDB Connector/J 1.2.0

We have been running this task for about two years using 1.7.2 driver w/o any issues. This week we decided to test 2.5.4 and when we just changed the driver, and restarted tomcat, we got a burst of errors because of (5), then after 10s of failed executions, it started to execute normally with occasional failures because of (5).

When we switched back to 1.7.2 driver, all these issues went away. Looks like that there was a regression since that version, so the driver is not always picking a slave for read-only connections.

Generated at Thu Feb 08 03:17:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.