Spring JDBC Opening Read-Only Connection

[kuloch]

Quick summary: Spring's JDBC is not allowing me to update tables via SQL, saying that the connection is read-only. Is there a way to force its connection to allow update (and other non-read-only) statements?

I am trying to follow the Spring Framework MVC app tutorial, and while it was written for version 2.5, I've gotten it nearly working under Spring 3.05 - up until adding the database. I have followed the directions exactly, making changes only as needed for newer versions and my specific installation.

After adding the HSQLDB, an "ant dbTests" runs fine (end of part 5), showing that at least the hsqldb.org driver (and my hsqldb setup) works fine. After deploying and reloading my changes at the end of part 6 (making the app use the db for storage), the initial "hello" page works fine, which queries the db for data. But after hitting the "Execute" button on priceincrease.htm (which runs an SQL update), it throws an exception that complains about a read-only SQL-transaction. The error message (truncated) is below.

Just in case the problem was with my HSQLDB setup (never used it before), I set up MySQL and appropriately changed the db connection properties and driver (Connector/J). However, I get essentially the same error: that the connection is read-only and breaks on an SQL update. The error message (truncated) is below.

I also tried writing my own Connection/Statement/ResultSet handlers (bypassing SimpleJdbcDaoSupport), but I got Exceptions along the lines of the "dataSource" not being writable while trying to deploy or run tests. I quickly suspected that this had to do with my initial problem above, so I scrapped that idea.

Setup Details:
Tomcat 7.0.16
Spring 3.0.5
Ant 1.8.2
hsqldb 2.2.5
MySQL 5.5 (with Connector/J 5.1.17)
Windows 7, 32-bit (x86)

[kuloch]

Error Using HSQLDB:

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [update products set description = ?, price = ? where id = ?]; SQL state [25006]; error code [-3706]; invalid transaction state: read-only SQL-transaction; nested exception is java.sql.SQLException: invalid transaction state: read-only SQL-transaction
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:656)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
root cause

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [update products set description = ?, price = ? where id = ?]; SQL state [25006]; error code [-3706]; invalid transaction state: read-only SQL-transaction; nested exception is java.sql.SQLException: invalid transaction state: read-only SQL-transaction
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
	org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
	org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:833)
	org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:259)
	org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:243)
	springapp.repository.JdbcProductDao.saveProduct(JdbcProductDao.java:38)
	springapp.service.SimpleProductManager.increasePrice(SimpleProductManager.java:30)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	java.lang.reflect.Method.invoke(Unknown Source)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	$Proxy13.increasePrice(Unknown Source)
	springapp.web.PriceIncreaseFormController.onSubmit(PriceIncreaseFormController.java:30)
	org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:415)
	org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:387)
	org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:272)
	org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:268)
	org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
	org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
root cause

java.sql.SQLException: invalid transaction state: read-only SQL-transaction
	org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
	org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
	org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
	org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
	org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:245)
	org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:580)
	org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
	org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:833)
	org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:259)
	org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:243)
	springapp.repository.JdbcProductDao.saveProduct(JdbcProductDao.java:38)
	springapp.service.SimpleProductManager.increasePrice(SimpleProductManager.java:30)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	java.lang.reflect.Method.invoke(Unknown Source)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	$Proxy13.increasePrice(Unknown Source)
	springapp.web.PriceIncreaseFormController.onSubmit(PriceIncreaseFormController.java:30)
	org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:415)
	org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:387)
	org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:272)
	org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:268)
	org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
	org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
note The full stack trace of the root cause is available in the Apache Tomcat/7.0.16 logs.

Apache Tomcat/7.0.16

[kuloch]

Error Using MySQL:

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [update products set description = ?, price = ? where id = ?]; Connection is read-only. Queries leading to data modification are not allowed; nested exception is java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:656)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
root cause

org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [update products set description = ?, price = ? where id = ?]; Connection is read-only. Queries leading to data modification are not allowed; nested exception is java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
	org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:107)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
	org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
	org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:833)
	org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:259)
	org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:243)
	springapp.repository.JdbcProductDao.saveProduct(JdbcProductDao.java:38)
	springapp.service.SimpleProductManager.increasePrice(SimpleProductManager.java:30)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	java.lang.reflect.Method.invoke(Unknown Source)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	$Proxy14.increasePrice(Unknown Source)
	springapp.web.PriceIncreaseFormController.onSubmit(PriceIncreaseFormController.java:30)
	org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:415)
	org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:387)
	org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:272)
	org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:268)
	org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
	org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
root cause

java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
	com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2366)
	com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
	com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
	org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:817)
	org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
	org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
	org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
	org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:833)
	org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:259)
	org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:243)
	springapp.repository.JdbcProductDao.saveProduct(JdbcProductDao.java:38)
	springapp.service.SimpleProductManager.increasePrice(SimpleProductManager.java:30)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	java.lang.reflect.Method.invoke(Unknown Source)
	org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	$Proxy14.increasePrice(Unknown Source)
	springapp.web.PriceIncreaseFormController.onSubmit(PriceIncreaseFormController.java:30)
	org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:415)
	org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:387)
	org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:272)
	org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:268)
	org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
	org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
note The full stack trace of the root cause is available in the Apache Tomcat/7.0.16 logs.

Apache Tomcat/7.0.16

[Marten Deinum]

Post your configuration and transaction configuration (in case you use @Transactional).

[kuloch]

I should add that I'm completely new to Spring as of the start of this tutorial. So "configuration" is a hell of a lot of text as far as I'm concerned, spread across several files.

Is there a specific portion of a specific set of files that you're looking for? Do you want anything from my Tomcat configs? The Ant configs? Anything about HSQLDB and/or MySQL?

Please recall that I have followed the tutorial exactly, aside from a few very minor edits just to make things work with current versions. If there is anything you want, please glance through the tutorial and identify the file(s) or portion(s) that might prove helpful. I'll reply with what I have and set edits in boldface.

[Marten Deinum]

WIth configuration I mean your spring application context files...

[kuloch]

As I was preparing to copy paste a whole horde of files that I thought might be potentially in question, I noticed that one of them was actually called "applicationContext.xml". So I looked through it. Right at the end, I happened to notice this suspicious-looking snippet:

    <tx:advice id="txAdvice">
        <tx:attributes>
            <tx:method name="save*"/>
            <tx:method name="*" read-only="true"/>
        </tx:attributes>
    </tx:advice>

After removing the line with the read-only property, the database update suddenly works and all is good. Thanks for your help in narrowing down where I needed to look.

[smaldeniya]

As I was preparing to copy paste a whole horde of files that I thought might be potentially in question, I noticed that one of them was actually called "applicationContext.xml". So I looked through it. Right at the end, I happened to notice this suspicious-looking snippet:

    <tx:advice id="txAdvice">
        <tx:attributes>
            <tx:method name="save*"/>
            <tx:method name="*" read-only="true"/>
        </tx:attributes>
    </tx:advice>

After removing the line with the read-only property, the database update suddenly works and all is good. Thanks for your help in narrowing down where I needed to look.

Yes this is the reason that cause the error. What i have done is just adding another <tx:method> element withou read only false for the method which tries to increase the price like below

<tx:advice id="txAdvice">
		<tx:attributes>
			<tx:method name="save*" />
			<tx:method name="increase*" read-only="false"/>
			<tx:method name="*" read-only="true" />
		</tx:attributes>
</tx:advice>