I am using HikariCP on AWS using RDS Aurora/MySQL and have run across an interesting situation on certain fail-overs.
As background, Aurora provides for a cluster with one read/write end-point that uses DNS to connect to the single read-write instance, but there can be up to 15 read-only instances also in the cluster that can be connected to round-robin through a different endpoint. Through the management console, and perhaps in some real failure cases, the read-write instance (1) gets relegated to be a read-only instance, and a prior read-only instance (2) gets promoted to be the primary (read-write) instance.
What I've observed is that when this happens, the original connection (read-write) in the pool to instance 1 gets no connection exception and our application proceeds to use it, only getting an exception when it tries to perform an insert to what is now a read-only instance.
Arguably, Aurora should make the connection invalid so a reconnection is performed that would go to the new read-write instance 2. That is being pursued with AWS. They may think that transparently handling it as it does is a feature.
In the event the Aurora behavior can't be changed, what can be done with HikariCP to help address it?
I can think of a few ideas, I'm interested if either of these make sense or if there is a better way to handle it.
First would be to configure a validation query rather than letting HikariCP default to using Connection.isValid(), and have this query do a simple insert/update which would validate the connection is read-write. I know using this approach for validating connections is discouraged, but I think it would work.
Second is to check/detect in our application if a connection is read-write and if not, use the MBean to soft evict connections so they are reestablished. This should work too, but requires adding specific logic interacting with the MBean to our code.
Is there some better way to do this with some other configuration or hook in HikariCP? Is there some way HikariCP can be configured to evict a connection if a "read-only" exception occurs?
Thanks,
Kevin