You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by deepak_a <an...@gmail.com> on 2013/12/11 10:30:01 UTC
Auto commit issue with SQL Server + ActiveMQ
All,
I am using ActiveMQ 5.6.0 SQL Server 2008 is used in backend. With SQL
Server -
I get the following exception
2013-12-11 09:17:15,085 WARN
[org.apache.activemq.store.jdbc.JDBCPersistenceAdapter] (ActiveMQ Transport:
tcp:///127.0.0.1:62351) Commit failed: commit() should not be called while
in auto-commit mode. java.sql.SQLException: commit() should not be called
while in auto-commit mode. at
net.sourceforge.jtds.jdbc.ConnectionJDBC2.commit(ConnectionJDBC2.java:1878)
....................
....................
2013-12-11 09:17:15,089 WARN
[org.apache.activemq.store.jdbc.JDBCPersistenceAdapter] (ActiveMQ Transport:
tcp:///127.0.0.1:62351) Rollback failed: rollback() should not be called
while in auto-commit mode. java.sql.SQLException: rollback() should not be
called while in auto-commit mode. at
net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:1893)
at
net.sourceforge.jtds.jdbcx.proxy.ConnectionProxy.rollback(ConnectionProxy.java:517)
....................
....................
2013-12-11 09:17:15,109 WARN
[org.apache.activemq.store.jdbc.JDBCPersistenceAdapter] (ActiveMQ Transport:
tcp:///127.0.0.1:62351) Error while closing connection: commit() should not
be called while in auto-commit mode. java.sql.SQLException: commit() should
not be called while in auto-commit mode. at
net.sourceforge.jtds.jdbc.ConnectionJDBC2.commit(ConnectionJDBC2.java:1878)
....................
....................
Caused by: java.sql.SQLException: commit() should not be called while in
auto-commit mode. at
net.sourceforge.jtds.jdbc.ConnectionJDBC2.commit(ConnectionJDBC2.java:1878)
at
net.sourceforge.jtds.jdbcx.proxy.ConnectionProxy.commit(ConnectionProxy.java:87)
This happens when a message is to be persisted to the database.
My broker-config is as follows Datasource is shown below
<persistenceAdapter>
<jdbcPersistenceAdapter dataSource="#reform-ds"
useDatabaseLock="false" >
</jdbcPersistenceAdapter>
</persistenceAdapter>
Datasource is as follows
<xa-datasource>
<jndi-name>ReformDS</jndi-name>
<track-connection-by-tx/>
<isSameRM-override-value>false</isSameRM-override-value>
<xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>
<xa-datasource-property
name="ServerName">LDN-LRM-PC-189</xa-datasource-property>
<xa-datasource-property
name="DatabaseName">reform</xa-datasource-property>
<xa-datasource-property
name="Instance">SQLEXPRESS</xa-datasource-property>
<xa-datasource-property
name="XaEmulation">true</xa-datasource-property>
<connection-property name="autoCommit">false</connection-property>
<track-connection-by-tx>true</track-connection-by-tx>
<no-tx-separate-pools>true</no-tx-separate-pools>
<transaction-isolation>TRANSACTION_READ_UNCOMMITTED</transaction-isolation>
<min-pool-size>10</min-pool-size>
<max-pool-size>30</max-pool-size>
<idle-timeout-minutes>15</idle-timeout-minutes>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<check-valid-connection-sql>select
1</check-valid-connection-sql>
<set-tx-query-timeout/>
<metadata>
<type-mapping>MS SQLSERVER</type-mapping>
</metadata>
<security-domain>EncryptDBPassword</security-domain>
<depends>jboss.security:service=JaasSecurityDomain,domain=ServerMasterPassword</depends>
</xa-datasource>
</datasources>
Is this happening because - my Datasource is attempting to commit in
addition to the auto-commit in SQL Server? -
I don;t see any option to disable auto-commit in the Datasource though...
regards
D
--
View this message in context: http://activemq.2283324.n4.nabble.com/Auto-commit-issue-with-SQL-Server-ActiveMQ-tp4675355.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
Re: Auto commit issue with SQL Server + ActiveMQ
Posted by deepak_a <an...@gmail.com>.
Would appreciate if you can clarify what changeAutoCommitAllowed="false"
means?
The API has an explanation but its unclear.
Does this parameter ensure - SQL Server's default Autocommit is overwritten
- and the actual commit is done by ActiveMQ's datasource?
regards
D
--
View this message in context: http://activemq.2283324.n4.nabble.com/Auto-commit-issue-with-SQL-Server-ActiveMQ-tp4675355p4675407.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
Re: Auto commit issue with SQL Server + ActiveMQ
Posted by deepak_a <an...@gmail.com>.
Hi,
I upgraded to ActiveMQ 5.8.0.
Added changeAutoCommitAllowed="false" to broker-config.xml - as shown below
<persistenceAdapter>
<jdbcPersistenceAdapter dataSource="#reform-ds"
useDatabaseLock="false" changeAutoCommitAllowed="false"/>
</persistenceAdapter>
Now I don't see the exceptions stated above.
However on Jboss start up - I see the following
2013-12-11 16:08:40,865 INFO
[org.apache.activemq.store.kahadb.plist.PListStoreImpl] (Starting ActiveMQ
Broker)
PListStore:[C:\Downloads\REFORM_HOME\jboss-5.1.0.GA\bin\activemq-data\jboss-activemq-broker\tmp_storage]
started
2013-12-11 16:08:40,885 INFO [org.apache.activemq.broker.BrokerService]
(Starting ActiveMQ Broker) Using Persistence Adapter:
JDBCPersistenceAdapter(org.jboss.resource.adapter.jdbc.WrapperDataSource@504266b6)
2013-12-11 16:08:40,896 INFO
[org.apache.activemq.store.jdbc.JDBCPersistenceAdapter] (Starting ActiveMQ
Broker) Database adapter driver override recognized for :
[jtds_type_4_jdbc_driver_for_ms_sql_server_and_sybase] - adapter: class
org.apache.activemq.store.jdbc.adapter.TransactJDBCAdapter
2013-12-11 16:08:40,959 WARN
[org.apache.activemq.store.jdbc.JDBCPersistenceAdapter] (Starting ActiveMQ
Broker) Cannot create tables due to: java.sql.SQLException: You cannot
commit with autocommit set!
2013-12-11 16:08:40,960 WARN
[org.apache.activemq.store.jdbc.JDBCPersistenceAdapter] (Starting ActiveMQ
Broker) Failure Details: You cannot commit with autocommit set!
java.sql.SQLException: You cannot commit with autocommit set!
at
org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcCommit(BaseWrapperManagedConnection.java:661)
**************************
Despite this warning ActiveMQ seems to have come up fine.
regards,
D
--
View this message in context: http://activemq.2283324.n4.nabble.com/Auto-commit-issue-with-SQL-Server-ActiveMQ-tp4675355p4675406.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
Re: Auto commit issue with SQL Server + ActiveMQ
Posted by Christian Posta <ch...@gmail.com>.
I'm not sure, and don't have the code in front of me, but could you
try on the latest release or a snapshot to reproduce?
On Wed, Dec 11, 2013 at 1:30 AM, deepak_a <an...@gmail.com> wrote:
> All,
>
> I am using ActiveMQ 5.6.0 SQL Server 2008 is used in backend. With SQL
> Server -
> I get the following exception
>
> 2013-12-11 09:17:15,085 WARN
> [org.apache.activemq.store.jdbc.JDBCPersistenceAdapter] (ActiveMQ Transport:
> tcp:///127.0.0.1:62351) Commit failed: commit() should not be called while
> in auto-commit mode. java.sql.SQLException: commit() should not be called
> while in auto-commit mode. at
> net.sourceforge.jtds.jdbc.ConnectionJDBC2.commit(ConnectionJDBC2.java:1878)
> ....................
> ....................
> 2013-12-11 09:17:15,089 WARN
> [org.apache.activemq.store.jdbc.JDBCPersistenceAdapter] (ActiveMQ Transport:
> tcp:///127.0.0.1:62351) Rollback failed: rollback() should not be called
> while in auto-commit mode. java.sql.SQLException: rollback() should not be
> called while in auto-commit mode. at
> net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:1893)
> at
> net.sourceforge.jtds.jdbcx.proxy.ConnectionProxy.rollback(ConnectionProxy.java:517)
> ....................
> ....................
> 2013-12-11 09:17:15,109 WARN
> [org.apache.activemq.store.jdbc.JDBCPersistenceAdapter] (ActiveMQ Transport:
> tcp:///127.0.0.1:62351) Error while closing connection: commit() should not
> be called while in auto-commit mode. java.sql.SQLException: commit() should
> not be called while in auto-commit mode. at
> net.sourceforge.jtds.jdbc.ConnectionJDBC2.commit(ConnectionJDBC2.java:1878)
> ....................
> ....................
> Caused by: java.sql.SQLException: commit() should not be called while in
> auto-commit mode. at
> net.sourceforge.jtds.jdbc.ConnectionJDBC2.commit(ConnectionJDBC2.java:1878)
> at
> net.sourceforge.jtds.jdbcx.proxy.ConnectionProxy.commit(ConnectionProxy.java:87)
>
> This happens when a message is to be persisted to the database.
> My broker-config is as follows Datasource is shown below
>
>
> <persistenceAdapter>
> <jdbcPersistenceAdapter dataSource="#reform-ds"
> useDatabaseLock="false" >
> </jdbcPersistenceAdapter>
> </persistenceAdapter>
>
>
> Datasource is as follows
>
>
> <xa-datasource>
> <jndi-name>ReformDS</jndi-name>
> <track-connection-by-tx/>
> <isSameRM-override-value>false</isSameRM-override-value>
>
> <xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>
>
> <xa-datasource-property
> name="ServerName">LDN-LRM-PC-189</xa-datasource-property>
> <xa-datasource-property
> name="DatabaseName">reform</xa-datasource-property>
> <xa-datasource-property
> name="Instance">SQLEXPRESS</xa-datasource-property>
> <xa-datasource-property
> name="XaEmulation">true</xa-datasource-property>
>
> <connection-property name="autoCommit">false</connection-property>
> <track-connection-by-tx>true</track-connection-by-tx>
> <no-tx-separate-pools>true</no-tx-separate-pools>
>
>
> <transaction-isolation>TRANSACTION_READ_UNCOMMITTED</transaction-isolation>
> <min-pool-size>10</min-pool-size>
> <max-pool-size>30</max-pool-size>
> <idle-timeout-minutes>15</idle-timeout-minutes>
> <blocking-timeout-millis>5000</blocking-timeout-millis>
>
> <check-valid-connection-sql>select
> 1</check-valid-connection-sql>
> <set-tx-query-timeout/>
> <metadata>
> <type-mapping>MS SQLSERVER</type-mapping>
> </metadata>
> <security-domain>EncryptDBPassword</security-domain>
>
> <depends>jboss.security:service=JaasSecurityDomain,domain=ServerMasterPassword</depends>
> </xa-datasource>
> </datasources>
>
>
> Is this happening because - my Datasource is attempting to commit in
> addition to the auto-commit in SQL Server? -
> I don;t see any option to disable auto-commit in the Datasource though...
>
>
> regards
> D
>
>
>
> --
> View this message in context: http://activemq.2283324.n4.nabble.com/Auto-commit-issue-with-SQL-Server-ActiveMQ-tp4675355.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
--
Christian Posta
http://www.christianposta.com/blog
twitter: @christianposta
Re: Auto commit issue with SQL Server + ActiveMQ
Posted by deepak_a <an...@gmail.com>.
https://issues.apache.org/jira/browse/AMQ-711
The issue I am facing is same as mentioned above
--
View this message in context: http://activemq.2283324.n4.nabble.com/Auto-commit-issue-with-SQL-Server-ActiveMQ-tp4675355p4675391.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
Re: Auto commit issue with SQL Server + ActiveMQ
Posted by deepak_a <an...@gmail.com>.
One thing to add - I am trying to send messages to activeMQ within an XA
transaction.
--
View this message in context: http://activemq.2283324.n4.nabble.com/Auto-commit-issue-with-SQL-Server-ActiveMQ-tp4675355p4675356.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.