Connection is read-only. Queries leading to data modification are not allowed

[Tim Edwards]

I have a class Im trying to build out with has a DSLContext Autowired in by Spring with Spring's TransactionAwareDataSourceProxy.  My jOOQ related code looks something along the lines of:

    @Autowired

    private DSLContext create;

    public boolean myMethod(String fieldOne, int fieldTwo) {
            String fieldThree = getFieldThree();

      try {

                          MyTablePojo newPojo = new MyTablePojo();  //This is an auto generated pojo

            newPojo.setFieldOne(fieldOne);

            newPojo.setFieldTwo(fieldTwo);

            newPojo.setFieldThree(fieldThree);

            MyTableRecord newRecord = create.newRecord(MY_TABLE, newPojo);

            newRecord.insert();

            return true;

        } catch (Exception e) {

            log.log(Level.WARNING, "ERROR Occurred", e);

            return false;

        }

    }

I can't seem to figure out why the db connection is always being marked as read only, and I keep getting the exception below:

org.jooq.exception.DataAccessException: SQL [insert into `MyDatabase`.`MyTable` (`field_one`, `field_two`, `field_three`) values (?, ?, ?)]; Connection is read-only. Queries leading to data modification are not allowed

        at org.jooq.impl.Utils.translate(Utils.java:1690)

        at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:660)

        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356)

        at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:177)

        at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:143)

        at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)

        at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:139)

        at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:132)

        at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:127)
        ... [Redacted]

             at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)

        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)

        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)

        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

        at java.lang.Thread.run(Thread.java:745)

Caused by: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)

        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1970)

        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)

        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)

        at org.jooq.tools.jdbc.DefaultPreparedStatement.executeUpdate(DefaultPreparedStatement.java:88)

        at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:349)

        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:342)

        ... 18 more

After several hours of searching online and trying a couple of different changes, I've yet to come up with anything useful.   If anyone has any suggestions as to what to look at next, I would greatly appreciate it. 

--

Tim

[timothy...@gmail.com]

I have a class Im trying to build out that has an DSLContext Autowired from Spring with Spring's TransactionAwareDataSourceProxy, my current code looks something along the lines of:

            MyPoJo newPojo = new MyPoJo();

            newPojo.setFeildOne(fieldOne);

            newPojo.setFieldTwo(fieldTwo);

            newPojo.setFieldThree(fieldThree);

            MyRecord newRecord = create.newRecord(MY_TABLE, newPojo);

            newRecord.insert();

It looks like the connection is for some reason being marked as read-only.  Even after being over explicit and annotating the method above with @Transactional( readOnly = false ) I still get the same stack trace:

org.jooq.exception.DataAccessException: SQL [insert into `Database`.`MyTable` (`field_one`, `field_two`, `field_three`) values (?, ?, ?)]; Connection is read-only. Queries leading to data modification are not allowed

After a couple of hours of searching for an answer to no avail, I've kind of given up and Im assuming that I've missed something terribly wrong.  If anyone can offer up some advice as to what to look at next, I would greatly appreciate it.

--

Tim

[Sascha Pfau]

Hi Tim,

 

do you use immutable pojos?

 

 

  <!-- Generate immutable POJOs for usage of the ResultQuery.fetchInto(Class) API

       This overrides any value set in <pojos/>

       Defaults to false -->

  <immutablePojos>false</immutablePojos>

Cheers

> 17) at java.lang.Thread.run(Thread.java:745)

[Tim Edwards]

Hi Sascha

Im not using immutable pojos.

I think I've narrowed it down to something in the way the DataSource is being generated.

Just for testing, I tried manually constructing a connection and a DSLContext like:

            Class.forName("com.mysql.jdbc.Driver");

            Connection conn = DriverManager.getConnection(url, username, password);

            DSLContext create = DSL.using(conn);

and everything works. 

So I think there is something going on with:

    <tx:annotation-driven transaction-manager="transactionManager"/>

    <bean id="transactionManager"

          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

        <property name="dataSource" ref="dataSource" />

    </bean>

    <!-- Configure jOOQ's ConnectionProvider to use Spring's TransactionAwareDataSourceProxy,

         which can dynamically discover the transaction context -->

    <bean id="transactionAwareDataSource"

          class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">

        <constructor-arg ref="dataSource" />

    </bean>

    <bean class="org.jooq.impl.DataSourceConnectionProvider" name="connectionProvider">

        <constructor-arg ref="transactionAwareDataSource" />

    </bean>

and the way the DataSource is being generated with:

    @Bean(name = "dataSource")

    public DataSource getDataSource() {
        //Internal configuration object

        DataAccessConfiguration dataAccessConfiguration = new DataAccessConfiguration();

        try {

            Context containerContext = (Context) context.lookup("java:comp/env");

            return (DataSource) containerContext.lookup(dataAccessConfiguration.getDbConnectionString());

        } catch (NamingException e) {

            throw new RuntimeException("Could not get dataSource from context, we have no DB connectivity");

        }

    }

Im using the same credentials and connection url in both cases.  So my guess is that something is setting the connection to read-only somewhere along the way.

[Lukas Eder]

Hi Tim,

Sorry for the delay.

It's hard to say from a high level. Certainly, jOOQ doesn't interfere with your MySQL connection / transaction, at least I wouldn't know how that could possibly happen.

There are two main items to check:

The MySQL database and its grants

Your Spring usage.

For instance, this Stack Overflow question here shows how a @Transactional(readOnly=true) annotation can lead to this behaviour:

http://stackoverflow.com/q/4648170/521799

This is independent of your Spring configuration, it overrides it locally for just one method (and all the methods that are called by that one method).

Does this help?

Lukas

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tim Edwards]

Thanks Lukas,

This turned out to be a side effect of using our current DAL and jOOQ at the same time.

--

Tim

[Lukas Eder]

OK, thanks for the feedback, Tim