Unexpected Transaction State Error on Repeated setReadOnly Calls in AWS Aurora Wrapper

[erie0210]

Describe the bug

In an environment using the AWS wrapper driver, we've encountered an intermittent error where calling setReadOnly at the start or end of a transaction triggers the Aurora wrapper's topology, resulting in the error: "Cannot change transaction read-only property in the middle of a transaction."

Expected Behavior

If not strictly necessary, we recommend that the setReadOnly function should avoid executing the topology call, or that the topology function be executed only after the connection is fully established.

What plugins are used? What other connection properties were set?

failover,efm

Current Behavior

Based on our direct analysis of the methods, we’ve inferred the following:

• Topology queries are intermittently executed at the end of a transaction.
• These queries are executed within the AWS-provided JDBC wrapper driver.
• The setReadOnly method in JDBC cannot be called during a transaction.

Given these points, we hypothesize that the error occurs in the following sequence:

1. Transaction ends.
2. TransactionState changes to IDLE.
3. Under certain conditions, a topology query is executed.
4. TransactionState changes back to OPEN.
5. setReadOnly method is called.
6. Error occurs.

Reproduction Steps

When the setReadOnly function is executed repeatedly, the error described above occasionally occurs.

Possible Solution

I’d like to propose that the Aurora wrapper driver don't trigger the topology call by default when setReadOnly is invoked.

Referencing the code in question: SubscribedMethodHelper.java, line 59.

Additional Information/Context

I'd be happy to create a PR to help fix this issue :)

The AWS Advanced JDBC Driver version used

2.2.3

JDK version used

correctto-17 17.0.9

Operating System and version

macOS 15.0.1 (24A348)

[erie0210]

Thank you for the PR above, although it was submitted by someone not directly related to me. I wanted to talk over about this issue if this behavior was intended or not. And why Connection.setReadOnly was included in METHODS_REQUIRING_UPDATED_TOPOLOGY list. Thanks for reviewing this issue.

[sergiyvamz]

Hi @erie0210

Thank you for reaching out and raising this issue. We'll take a look at this and keep you updated as we investigate.

Would you be able to provide driver logs that shows a stack trace of the issue?
If it's possible, could you also provide a sample app that reproduces the issue?

As a possible solution for the reported issue, we can suggest to replace failover plugin by failover2. This new failover2 plugin doesn't use driver connection to update a cluster topology, so it can't cause any side effects on a current transaction.

https://github.com/aws/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/using-plugins/UsingTheFailover2Plugin.md

[erie0210]

Thanks for the comment! @sergiyvamz
I tried failover2 and it wokred for me. But I believe there are still people who uses previous version of failover plugin.
So I digged down about this issue for record.

Made sample app here:

sample app repository

• made cachedHosts null to force a refresh
• added conn.setReadOnly(true); after conn.setAutoCommit(false);
  This will reproduce how driver works when it miss cache while setting connection properties.

Run PgFailoverSample.java.

stack trace:

> Task :driverexample:software.amazon.PgFailoverSample.main() FAILED
Exception in thread "main" org.postgresql.util.PSQLException: Cannot change transaction read-only property in the middle of a transaction.
	at org.postgresql.jdbc.PgConnection.setReadOnly(PgConnection.java:913)
	at software.amazon.jdbc.wrapper.ConnectionWrapper.lambda$setReadOnly$13(ConnectionWrapper.java:369)
	at software.amazon.jdbc.util.WrapperUtils.lambda$runWithPlugins$1(WrapperUtils.java:172)
	at software.amazon.jdbc.plugin.DefaultConnectionPlugin.execute(DefaultConnectionPlugin.java:130)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:339)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$0(ConnectionPluginManager.java:268)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:245)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$1(ConnectionPluginManager.java:268)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:273)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.execute(FailoverConnectionPlugin.java:230)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:339)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:272)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:245)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:272)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithSubscribedPlugins(ConnectionPluginManager.java:235)
	at software.amazon.jdbc.ConnectionPluginManager.execute(ConnectionPluginManager.java:336)
	at software.amazon.jdbc.util.WrapperUtils.executeWithPlugins(WrapperUtils.java:244)
	at software.amazon.jdbc.util.WrapperUtils.runWithPlugins(WrapperUtils.java:165)
	at software.amazon.jdbc.wrapper.ConnectionWrapper.setReadOnly(ConnectionWrapper.java:362)
	at software.amazon.PgFailoverSample.main(PgFailoverSample.java:62)

Execution failed for task ':driverexample:software.amazon.PgFailoverSample.main()'.
> Process 'command '/Users/jay_inflab/Library/Java/JavaVirtualMachines/corretto-17.0.3/Contents/Home/bin/java'' finished with non-zero exit value 1

While working on this, I found an issue, which I think might be related to this issue. #311
In this issue, they came to a conclusion to keep this implementation:

I've done some investigation and while the PG driver does follow the spec, MySQL allows users to call setReadOnly inside transactions without throwing an exception. With the current implementation, the plugin essentially just forwards the setReadOnly call to the underlying driver without doing anything special. So whether an exception is thrown or not depends on the underlying driver. I've chatted with the team and we are proposing to keep this implementation since throwing an exception here could break customer MySQL workflows, even if those workflows are a bit unusual. Let me know if you have any objections or if I can close this issue.
A small caveat: the plugin does throw an exception if setReadOnly(false) is called while connected to a reader and inside a transaction. This is because the user has indicated they want a read-write connection but we cannot switch to a writer, as we are already in a transaction. If setReadOnly(true) is called while in a transaction we just stick with the same connection and forward the call to the underlying driver.

[sergiyvamz]

Hi @erie0210

Thank you for provided code samples and other details. I was able to reproduce the issue. The fix has been merged. You can find the latest change in the snapshot build. More information on how to use the snapshot build is available here.

[aaron-congo]

Hi @erie0210 ,

The fix is now available in the recently released v2.5.3 of the driver.

Could you check it out let us know if the issue still persists?

Thank you!