Hi Alok, Interesting, I wasn't aware of this behaviour. So, in general, transactions are OK (e.g. to get consistent read behaviour), but savepoints don't work as they're not really useful when you're never writing...
All of this behaviour is implemented in DefaultTransactionProvider. We have a pending feature request that allows for disabling the use of JDBC Savepoint when nesting transactions: https://github.com/jOOQ/jOOQ/issues/3955 This feature request will be useful for you as well. In the meantime, you could implement your own TransactionProvider that accompanies the read-only connection, to replace the out-of-the-box implementation Let me know if this helps, Lukas 2015-05-18 9:15 GMT+02:00 Alok Menghrajani <[email protected]>: > Hi all, > > I have an open source service and I want the ability to easily swap > the backend database. For various reasons, the service has two > connections: a read/write connection and a read-only connection (one > of the reasons being that it makes some forms of replication easier > and allows the service to continue running in degraded mode using a > read-only slave if the read/write database goes down). > > We however noticed that transactions on the read-only connection work > fine with Postgres but fail with MySql (see [1] for a short repro). > > The error message is: > Exception in thread "main" org.jooq.exception.DataAccessException: > Cannot set savepoint > [...] > Caused by: java.sql.SQLException: Connection is read-only. Queries > leading to data modification are not allowed. > > I'm hoping there is some way to change the way jooq or mysql handles > transactions and which would work with a read-only connection? Any > other suggestions? > > Thanks, > Alok > > > [1]: > > package com.squareup.JooqTest; > > import java.sql.Connection; > import java.sql.DriverManager; > import org.jooq.DSLContext; > import org.jooq.impl.DSL; > > import static test.generated.tables.Author.*; > > /** > * create database library; > * use library; > * create table author (id int not null, first_name varchar(255), > last_name varchar(255), primary key (id)); > */ > public class App { > > public static void main(String[] args) throws Exception { > App app = new App(); > app.run(); > } > > public void run() throws Exception { > String userName = ...; > String password = ...; > String url = "jdbc:mysql://localhost:3306/library"; > > try (Connection conn = DriverManager.getConnection(url, userName, > password)) { > conn.setReadOnly(true); > DSLContext jooqContext = DSL.using(conn); > doTransaction(jooqContext); > } > } > > void doTransaction(DSLContext jooqContext) { > jooqContext.transaction(configuration -> > jooqContext.selectFrom(AUTHOR).fetchOne()); > } > > } > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
