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.
