Does MariaDB Connector/J 2.6.x create connections to both master and slave with Aurora?
Cross-posting from https://stackoverflow.com/questions/63406456/does-mariadb-connector-j-2-6-x-create-connections-to-both-master-and-slave-with. Please visit the Stack Overflow question to view the referenced image.
According to other Stack Overflow answers (https://stackoverflow.com/questions/44020489/db-connections-increase-after-setting-aurora-in-mariadb-connector, https://stackoverflow.com/questions/47163044/mariadb-connector-j-aurora-fast-failover-implemantation), as well as some documentation in the mariadb-connector-j repo (https://github.com/mariadb-corporation/mariadb-connector-j/blob/caad0f1682062251595978a454a37b50a3515c40/documentation/failover_loop.creole), the consensus seems to be:
On a master/slave cluster, driver will use underlying 2 connections: one to a master instance, one to a slave instance.
A "connection" to aurora mean 2 underlying connection to instances: one to master, one to slave. Driver will use the underlying connection to master or slave according to Connection.setReadonly().
When using the "aurora" keyword, driver , under the hood, create 2 connections: a connection to the primary server, a connection to one of the replicas if any. The goal is always to save resources on the main server. Generally, only one pool is configured. The driver then uses the connection to the primary / replica according to [Connection.setReadOnly]
However, I am trying this for myself and do not see this behavior with MariaDB Connector/J 2.6.2 (in a Play 2.8.x application, which uses HikariCP as the connection pool). I created a new MySQL Aurora cluster (with a master and read-replica) and modified my test application to connect to this cluster with a HikariCP pool that's configured at a fixed size of 8 connections, using the cluster endpoint url as follows:
`"jdbc:mysql:aurora:test.cluster-abcdefg.us-east-1.rds.amazonaws.com:3306/test"`
From what I can see in the logs, as well as the image from the RDS web dashboard's monitoring tab (shown in my Stack Overflow question and at https://i.stack.imgur.com/8lk0q.png), once the application starts up, the master goes to 8 connections (blue line) and the read-replica has 0 connections (orange line). This seems to be contrary to all the above points that, under the hood, the driver makes 2 underlying connections and chooses between them based on the status of a given Connection's read-only property.
The Play configuration for this connection is as follows:
``` test { driver = org.mariadb.jdbc.Driver url = "jdbc:mysql:aurora:test.cluster-abcdefg.us-east-1.rds.amazonaws.com:3306/test"
hikaricp { autoCommit = false readOnly = false } } ```
If I instead configure the connection like:
``` test { driver = org.mariadb.jdbc.Driver url = "jdbc:mysql:aurora:test.cluster-abcdefg.us-east-1.rds.amazonaws.com:3306/test"
hikaricp { autoCommit = true readOnly = true } } ``` then I do see connections made to both the master and read-replica (see https://i.stack.imgur.com/iXQic.png).
Please help me understand this behavior.
What I'm trying to achieve is to have separate write and read pools. The write pool would always point to the master, and the read pool would always point to a read replica (assuming one exists). I want to avoid dual underlying connections, as the `readOnly` setting is configured at the pool level and will never change on a given `Connection` during its lifetime. How can I achieve this?
Additionally, I would like to know whether I am expected to use the autoReconnect parameter when specifying the aurora failover mode. Does the value of that parameter matter with the aurora failover mode?
