Support JDBC connection routing to readonly replica

[ndiaz-addepar]

Description

This PR relates to one of the issues

A lot of people using Spring Transactional annotation use @Transactional(readonly=true) for the benefit of connection routing at the jdbc driver level

More explicitly when connecting to Aurora Mysql the url is something like this: jdbc:mysql:replication://${RW_ENDPOINT}:3306,${RO_ENDPOINT}:3306/DBNAME If the underlying connection is marked as readOnly basically calling: conn.setReadOnly(true); The Driver will automatically redirect queries to the ReadOnly EndPoint with no extra code.

Implementation ideas

Because @TransactionConfiguration already exists to setup timeouts it should be possible to also extend it to setup the readonly property in the connection. and this will unlock the use of Aurora or Replicated MySQL env for RO routing

[loicmathieu]

~Duplicate of https://github.com/quarkusio/quarkus/issues/6414.~ ~I'm closing this one, you can watch 6414 and provides feedback on it (vote with 👍 ).~

[ndiaz-addepar]

@loicmathieu I beg to differ. If you closely look at the thread these are your own words requesting to open a new issue: https://github.com/quarkusio/quarkus/issues/6414#issuecomment-802119021

I am explicitly asking for Connection to be set as read only.

[loicmathieu]

Sorry @ndiaz-addepar the comment was three months old and I forget it ;)

Anyway, your issue title is misleading as it's talking about readonly transaction but what you want it support for readonly replicas in a connection. I'll repoen it with a title reflecting this.

[ndiaz-addepar]

Sorry @ndiaz-addepar the comment was three months old and I forget it ;)

Anyway, your issue title is misleading as it's talking about readonly transaction but what you want it support for readonly replicas in a connection. I'll repoen it with a title reflecting this.

Thank you!!

[kf3in]

Please, at least allow keycloak to start with a readonly database