Removing a connection from the pool when it changes to read-only

1,350 views
Skip to first unread message

Kevin Schmidt

unread,
Jun 5, 2018, 3:25:21 PM6/5/18
to HikariCP
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

Brett Wooldridge

unread,
Jun 6, 2018, 10:13:15 AM6/6/18
to HikariCP
Pretty tricky situation.  I feel like the validation approach is best, but you want to try to be clever about it, so as to not impact performance.  One suggestion is to try the validation query approach using:

SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE

as the query (ref).  My hope would be that this will throw an exception if the database has been made a slave, but not throw on the master -- while also not actually performing any kind of DML.

-Brett

Kevin Schmidt

unread,
Jun 6, 2018, 10:19:00 AM6/6/18
to hika...@googlegroups.com
Thanks for the suggestion.  I'll give it a try.

--
You received this message because you are subscribed to a topic in the Google Groups "HikariCP" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/hikari-cp/VH7nqwGimCs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to hikari-cp+unsubscribe@googlegroups.com.
Visit this group at https://groups.google.com/group/hikari-cp.
For more options, visit https://groups.google.com/d/optout.

Brett Wooldridge

unread,
Jun 9, 2018, 2:56:07 AM6/9/18
to HikariCP
Did it work?

Darren Hobbs

unread,
Sep 2, 2019, 4:53:44 AM9/2/19
to HikariCP
We experienced this exact situation with RDS Aurora Postgres. The checkout test query that worked for us was this one:

SET TRANSACTION READ WRITE

(The SESSION one mentioned in an earlier didn't work for us but was instrumental in finding the one that did.)

It's a slight hack, as no transaction is underway during connection checkout, but it does correctly evict connections that have become read-only.

The relevant postgres docs say,  "If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it emits a warning and otherwise has no effect."


On Saturday, 9 June 2018 07:56:07 UTC+1, Brett Wooldridge wrote:
Did it work?
Reply all
Reply to author
Forward
0 new messages