Session Pinning Issue with RDS Proxy using Spring Boot, Reactor, jOOQ, and R2DBC MariaDB

[yogesh3312]

Description:

Environment:
Java: 21 and 22
Spring Boot Version: 3.2.7
Reactor Framework Version: 3.6.7
jOOQ Version: 3.19.9
R2DBC MariaDB Version: 1.2.0
RDS MySQL Database: 8.4.0

Description of the Issue:
I am using Spring Boot and Reactor Framework along with jOOQ and R2DBC MariaDB for connection to an RDS MySQL database. To enhance scalability and optimize database connection pooling, I am utilizing RDS Proxy.

However, I am encountering a session pinning issue. Despite using jOOQ static statements instead of prepared statements, the issue persists. The logs indicate the following:

The client session was pinned to the database connection [dbConnection=2877674063] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query. Digest: "SET autocommit= ? ,transaction_isolation= ? ,session_track_schema= ? ,session_track_system_variables = ? ."

Steps to Reproduce:

Configure Spring Boot application with Reactor, jOOQ, and R2DBC MariaDB.
Connect to RDS MySQL database via RDS Proxy.
Execute database operations.

Expected Behavior:
The RDS Proxy should efficiently manage database connections without session pinning issues.

Actual Behavior:
The session gets pinned to a database connection, leading to the following log message and affecting connection reuse:

The client session was pinned to the database connection [dbConnection=2877674063] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query. Digest: "SET autocommit= ? ,transaction_isolation= ? ,session_track_schema= ? ,session_track_system_variables = ? ."

Attempts to Resolve:

• Used jOOQ static statements instead of prepared statements. - Not worked
  - Using the session filter exclude the variable sets - This is working, but not fully aware of the consequences like its affect on transaction.
  

Any guidance or solutions to avoid session pinning would be greatly appreciated.

• Is there a recommended approach to configure session settings that the RDS Proxy can track?
• How to avoid session variables being added?