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

["Jiri Patera (JIRA)" <ji...@apache.org>]

Jiri Patera created AMQ-4836:
--------------------------------

             Summary: Calling Connection.commit() on a Connection with setAutoCommit(true) throws SQLException
                 Key: AMQ-4836
                 URL: https://issues.apache.org/jira/browse/AMQ-4836
             Project: ActiveMQ
          Issue Type: Bug
          Components: Message Store
    Affects Versions: 5.7.0
         Environment: JDK 1.6.0_45, Firebird or HDB (SAP HANA DB) configured as a persistent storage
            Reporter: Jiri Patera


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:

{code}
    /**
     * 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;
{code}

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)}}:

{code}
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
{code}

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

{code}
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
{code}

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

{code}
      c.getConnection().commit();
{code}

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

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

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:

{code}
org.apache.activemq.store.jdbc.TransactionContext.commit()
{code}



--
This message was sent by Atlassian JIRA
(v6.1#6144)