Spring-MVC tutorial: Exception when changing from HSQLdb to MySQL

[Tom Hansen]

Hi,

when I try to change db from HSQLdb to MySQL I get I get an exception when I try to increase to prices from priceincrease.htm:

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:583)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

The parte of the applicationContext.xml that I think i relatet to db stuff:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="${db.driver}"/>
		<property name="url" value="${db.url}"/>
		<property name="username" value="${db.user}"/>
		<property name="password" value="${db.pw}"/>
		<property name="poolPreparedStatements" value="true"/>
	</bean>


    <bean id="propertyConfigurer" 
          class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
            </list>
        </property>
    </bean>

    <bean id="transactionManager" 
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <aop:config>
        <aop:advisor pointcut="execution(* *..ProductManager.*(..))" advice-ref="txAdvice"/>
    </aop:config>

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

If I change the <tx:advidce> to:

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

then everyting works fine. Can someone point me to the right direction for setting up MySQL?

[denis.zhdanov]

So, you define you transaction as read-only and trying to perform sql UPDATE from it. Why do you wonder the exception then?

[Tom Hansen]

Thanks for your reply.
I don't know what this is doing, that was part of my question.

But I think it is strange that this works with HSQLdb and not MySQL.

This work with HSQLdb and not with MySQL (increase prices from priceincrease.htm):

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

I wonder why?

[denis.zhdanov]

Thanks for your reply.
I don't know what this is doing, that was part of my question.

But I think it is strange that this works with HSQLdb and not MySQL.

The reason for that may be that hsql jdbc driver doesn't support read-only jdbc connection feature.

Anyway, you set that transaction is read-only and trying to perform write action from it. Doesn't it looks strange?

[Tom Hansen]

The reason for that may be that hsql jdbc driver doesn't support read-only jdbc connection feature.

Anyway, you set that transaction is read-only and trying to perform write action from it. Doesn't it looks strange?

Thanks! I must read the section on AOP and try to understand what's going on here.

[denis.zhdanov]

AOP is not related to the problem directly.

Just a pure logic - you define that the data is only read and try to perform write.

[Tom Hansen]

So what you are saying there is not any correllation with:

 <aop:config>
        <aop:advisor pointcut="execution(* *..ProductManager.*(..))" advice-ref="txAdvice"/>
    </aop:config>

and:

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

[denis.zhdanov]

So what you are saying there is not any correllation with:

It does relate to the '<tx:method name="*" read-only="true"/>'

[CA_Coder]

Hey Tom,

Did you ever get a solution to this issue? I followed the tutorial on the site as well and ran into the same issue at the end (I chose to use MySQL from the start). I find it odd that there'd be such a bug in the site tutorial and it has gone unnoticed... even if it happens to work okay in HSQLdb, it still doesn't appear to be doing what the author intended I would assume...

[trisberg]

Odd that this hasn't been reported before. Looks like the tx attributes are targeted at the DAO while the advice is actually applied on the ProductManager. I'll make this change and post the revised version on the web site.

Thanks for reporting this.