JBoss A-MQ 6.1: setting 'deleteMessagesOnStartup' throws "java.sql.SQLException: Could not commit with auto-commit set on"

Solution Verified - Updated -

Environment

  • Red Hat JBoss A-MQ 6.1 / ActiveMQ 5.8

Issue

  • using Oracle v12 driver getting the following exceptions when deleteMessagesOnStartup=true
caused by: java.sql.SQLException: Could not commit with auto-commit set on
    at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4443)
    at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4490)
    at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334)
    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.JDBCPersistenceAdapter.deleteAllMessages(JDBCPersistenceAdapter.java:534)
    ... 40 more

Resolution

Work around: Oracle v12 driver allows the user to turn off commit spec compilance by setting the java system-property "oracle.jdbc.autoCommitSpecCompliant=false"

The issue is address and fixed by ENTMQ-762

Root Cause

The oracle v12 driver throws the following exception if commit() is called on a connection that has auto-commit enabled(see [1]) . This seems to be a change in behaviour since v11.

~~~
caused by: java.sql.SQLException: Could not commit with auto-commit set on
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4443)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4490)
at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334)
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.JDBCPersistenceAdapter.deleteAllMessages(JDBCPersistenceAdapter.java:534)
... 40 more

~~~~

In the org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter#doDropTables() it explicitly calls connection.commit() without checking if auto-commit is enabled. In other places auto-commit does seem to be checked before commit is called - org.apache.activemq.store.jdbc.TransactionContext#commit().

The v12 driver does allow autocommit-SpecCompliance to be turned off by setting system property "oracle.jdbc.autoCommitSpecCompliant=false". But it does seem like org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter#doDropTables() is not strictly adhering to the spec. This is fixed in ENTMQ-762

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.