camel-mybatis insert commits failing with Postgres

[jkorab]

Hi,

I have come across an error when using camel-mybatis with Postgres that I
think merits a code change. 

I am using Camel 2.8 in Servicemix 4.4.1, though the issue is reproducible
in Camel 2.10. I have set up the MyBatisComponent to use a mybatis-spring
SqlSessionFactoryBean, which in turn uses an OSGi-configured DataSource:

    <bean id="mybatis"
class="org.apache.camel.component.mybatis.MyBatisComponent">
        <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
    </bean>

    <bean id="sqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="configLocation" value="/META-INF/SqlMapConfig.xml"/>
        <property name="mapperLocations"
value="classpath*:META-INF/mappers/**/*.xml"/>
    </bean>

    <bean id="dataSource" class="org.postgresql.ds.PGSimpleDataSource">
        <property name="serverName" value="${pg.server}"/>
        <property name="databaseName" value="${pg.database}"/>
        <property name="portNumber" value="${pg.port}"/>
        <property name="user" value="${pg.username}"/>
        <property name="password" value="${pg.password}"/>
    </bean>

This setup allows me to externalise the database connection config outside
of MyBatis' Environments construct, so it plays nicer with how ServiceMix
apps are typically structured.

The issue pops up when I attempt to insert an object into the database
through a simple route:

        <from uri="direct:insert"/>
        <to uri="mybatis:myModel.insert?statementType=Insert"/>

When I send a model object in to the route, I get the following exception
(abridged):
org.apache.camel.CamelExecutionException: Exception occurred during
execution on the exchange: Exchange[Message: <snip>]
        <snip>
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error committing transaction.  Cause: org.postgresql.util.PSQLException:
Cannot commit when autoCommit is enabled.
### Cause: org.postgresql.util.PSQLException: Cannot commit when autoCommit
is enabled.
        at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at
org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:143)
        at
org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:135)
        at
org.apache.camel.component.mybatis.MyBatisProducer.doInsert(MyBatisProducer.java:125)
        at
org.apache.camel.component.mybatis.MyBatisProducer.process(MyBatisProducer.java:53)
        at
org.apache.camel.impl.converter.AsyncProcessorTypeConverter$ProcessorToAsyncProcessorBridge.process(AsyncProcessorTypeConverter.java:50)
        at
org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:78)
        <snip>
        ... 33 more
Caused by: org.postgresql.util.PSQLException: Cannot commit when autoCommit
is enabled.
        at
org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:705)
        at
org.mybatis.spring.transaction.SpringManagedTransaction.commit(SpringManagedTransaction.java:97)
        at org.apache.ibatis.executor.BaseExecutor.commit(BaseExecutor.java:152)
        at
org.apache.ibatis.executor.CachingExecutor.commit(CachingExecutor.java:80)
        at
org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:140)
        ... 80 more

I have confirmed that it is not a mere DataSource config thing, as I get the
same results using a more typical:

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="org.postgresql.Driver"/>
        <property name="jdbcUrl"
value="jdbc:postgresql://${pg.server}:${pg.port}/${pg.database}"/>
        <property name="user" value="${pg.username}"/>
        <property name="password" value="${pg.password}"/>
    </bean>

Having taken a look at MyBatisProducer, there are SqlSession.commit() calls
in doInsert(), doInsertList(), doUpdate() and doDelete() - rollback() is
never used. These appear to be the cause of the behaviour I'm seeing. From
what I have read about the autoCommit functionality, the JDBC spec doesn't
say whether it should be turned on or off by default
(http://www.mchange.com/projects/c3p0/index.html#autoCommitOnClose). In
Postgres, it is, whereas in Derby (used in the unit tests) it's not. It's
also not in H2, which I was using for my (successful) integration tests. 

To confirm whether the behaviour would work correctly without the commits, I
wrote up a pair of MyBatis delegate classes for SqlSessionFactory and
SqlSession, that trapped all commits. Inserts were successful both in
Postgres and H2. I would like to propose that the commit() calls be removed
from the MyBatisProducer (happy to provide patch), as whether the
transaction is committed or rolled back should be up to the underlying layer
in which the user might configure a transaction manager to make that
decision. Is there anything that I have missed around the reasoning behind
the commits?

Thanks,

Jakub

--
View this message in context: 
http://camel.465427.n5.nabble.com/camel-mybatis-insert-commits-failing-with-Postgres-tp5716081.html
Sent from the Camel Development mailing list archive at Nabble.com.