[AMQ-4836] Calling Connection.commit() on a Connection with setAutoCommit(true) throws SQLException

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 5.7.0
• Fix Version/s: 5.11.0
• Component/s: Message Store

Environment

JDK 1.6.0_45, Firebird or HDB (SAP HANA DB) configured as a persistent storage

• Assignee: Unassigned
• Reporter: Jiri Patera
• Created: 30/Oct/13 09:24
• Updated: 19/Aug/14 16:41
• Resolved: 19/Aug/14 16:41

Description

This issue is similar to AMQ-577 (resolved by adding a MSSQL JDBC URL parameter) and AMQ-711 (resolved as cannot reproduce) issues.

The problem is that the JDBC API (the Connection class JavaDoc) states:

    /**
     * Makes all changes made since the previous
     * commit/rollback permanent and releases any database locks
     * currently held by this <code>Connection</code> object. 
     * This method should be
     * used only when auto-commit mode has been disabled.
     *
     * @exception SQLException if a database access error occurs, 
     * this method is called while participating in a distributed transaction,
     * if this method is called on a closed conection or this
     *            <code>Connection</code> object is in auto-commit mode
     * @see #setAutoCommit 
     */
    void commit() throws SQLException;

However, some JDBC drivers do not throw the SQLException when the Connection.commit() method is called on a Connection instance with setAutoCommit(true). Some others do (Firebird, SAP-HANA, MSSQL without the JDBC URL parameter relaxAutoCommit=true). With these databases the following exceptions can be thrown on the Broker start-up:

1) DefaultJDBCAdapter.doDropTables(DefaultJDBCAdapter.java:148):

Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Connection is currently in auto commit mode.
	at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:334)
	at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateSQLException(SQLExceptionSapDB.java:113)
	at com.sap.db.jdbc.ConnectionSapDB.commit(ConnectionSapDB.java:351)
	at com.sap.db.jdbc.trace.Connection.commit(Connection.java:126)
	at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:211)
	at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doDropTables(DefaultJDBCAdapter.java:148)
	at org.apache.activemq.store.jdbc.adapter.OptimizedDefaultJDBCAdapter.doDropTables(OptimizedDefaultJDBCAdapter.java:68)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.aop.interceptor.AbstractTraceInterceptor.invoke(AbstractTraceInterceptor.java:113)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at com.sun.proxy.$Proxy12.doDropTables(Unknown Source)
	at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.deleteAllMessages(JDBCPersistenceAdapter.java:526)
	... 64 more

2) DefaultJDBCAdapter.doCreateTables(DefaultJDBCAdapter.java:119)

Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Connection is currently in auto commit mode.
	at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:334)
	at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateSQLException(SQLExceptionSapDB.java:113)
	at com.sap.db.jdbc.ConnectionSapDB.commit(ConnectionSapDB.java:351)
	at com.sap.db.jdbc.trace.Connection.commit(Connection.java:126)
	at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:211)
	at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doCreateTables(DefaultJDBCAdapter.java:119)
	at org.apache.activemq.store.jdbc.adapter.OptimizedDefaultJDBCAdapter.doCreateTables(OptimizedDefaultJDBCAdapter.java:62)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.aop.interceptor.AbstractTraceInterceptor.invoke(AbstractTraceInterceptor.java:113)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at com.sun.proxy.$Proxy12.doCreateTables(Unknown Source)
	at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.deleteAllMessages(JDBCPersistenceAdapter.java:528)
	... 64 more

This led us to a workaround where we simply override the 1) and 2) methods and replaced the following code in them:

      c.getConnection().commit();

With the following code (to prevent the exceptions from being thrown):

      if (!c.getConnection().getAutoCommit()) { /* HACK */
        c.getConnection().commit();
      }

I believe that the ActiveMQ code should correspond to the JDBC API specification by calling the Connection.commit() method only on connections with setAutoCommit(false). This approach is already implemented in the ActiveMQ code, for example, in the following method:

org.apache.activemq.store.jdbc.TransactionContext.commit()

Issue Links

• is superceded by: AMQ-5318 JDBC store; commit called on connection that uses autocommit during "deleteAllMessages" .