Read only exception on Amazon Aurora MySQL writer failover with vertx-mysql-client

[Akshay Patidar]

Hi,

We are using amazon aurora mysql in our service and use vertx-mysql-client to connect to cluster endpoints in our application.

But after the writer failover (one reader is promoted to writer) the writer endpoint still resolves to old writer which is now reader and we get the following exception:

"java.sql.SQLException: The MySQL server is running with the --read-only option so it cannot execute this statement"

On failover Aurora closes all the connections, and then vertx-mysql-client tries to recreate them, because of DNS caching the writer endpoint resolves to old writer and since connection lifetime is infinite it never gets updated. 

I have tried with DNS TTL=0 in AddressResolverOptions as well as JVM address resolver but the problem persists.

If we could recycle the connections after some time then this problem could be solved but I could not find a way to do so.

Please let me know if there is way to solve this problem.

Thanks and Regards
Akshay

[Billy Yuan]

Hi, in this case you can set up a handler handling the insert query failure, if its error message is "The MySQL server is running with the --read-only option so it cannot execute this statement" and then you close the pool and recreate a new one configuring with address of the right cluser writer node .