Unable to use JDBC Persistence Adapter with Microsoft SQL Server
Environment
- Fuse MQ Enterprise 7.x
- JBoss Fuse MQ 6.0
- Apache ActiveMQ 5.7
Issue
Configuring the ActiveMQ JDBC Persistence Adapter to connect to Microsoft SQL Server results in
Failed to acquire lock. Sleeping for 10000 milli(s) before trying again...
Resolution
The SQL statement
SQL:SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE
is not supported by Microsoft SQL Server.
A possible solution is to use the newer Lease Database Locker of ActiveMQ.
Check the section "Lease Database Locker" on http://activemq.apache.org/pluggable-storage-lockers.html.
Configure the JDBC Persistence Adapter to use that new locker:
<persistenceAdapter>
<jdbcPersistenceAdapter dataDirectory="${activemq.data}" dataSource="#mysql-ds" lockKeepAlivePeriod="5000">
<locker>
<lease-database-locker lockAcquireSleepInterval="10000"/>
</locker>
</jdbcPersistenceAdapter>
</persistenceAdapter>
This locker uses a different SQL statement for locking which does not raise any errors on Microsoft SQL Server
UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=?, TIME=? WHERE (TIME IS NULL OR TIME < ?) AND ID = 1
Root Cause
When enabling DEBUG logging for the package org.apache.activemq.store.jdbc, the root cause is going to be logged.
It may be as follows:
16:36:43,517 | DEBUG | Q Broker: fusemq | SQLServerStatement | 357 - sqljdbc - 1.0.0.SNAPSHOT |
SQLServerPreparedStatement:45: calling sp_prepexec: PreparedHandle:0, SQL:SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE
16:36:43,533 | DEBUG | Q Broker: fusemq | SQLServerException | 357 - sqljdbc - 1.0.0.SNAPSHOT |
*** SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for
DECLARE CURSOR. Msg 1003, Level 15, State 2, Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
16:36:43,534 | DEBUG | Q Broker: fusemq | DefaultDatabaseLocker | 125 -
org.apache.activemq.activemq-core - 5.7.0.fuse-71-047 | Lock failure:
com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)[357:sqljdbc:1.0.0.SNAPSHOT]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)[357:sqljdbc:1.0.0.SNAPSHOT]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)[357:sqljdbc:1.0.0.SNAPSHOT]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)[357:sqljdbc1.0.0.SNAPSHOT]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)[357:sqljdbc:1.0.0.SNAPSHOT]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)[357:sqljdbc:1.0.0.SNAPSHOT]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)[357:sqljdbc:1.0.0.SNAPSHOT]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)[357:sqljdbc:1.0.0.SNAPSHOT]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332)[357:sqljdbc:1.0.0.SNAPSHOT]
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)[355:org.apache.commons.dbcp:1.4]
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)[355:org.apache.commons.dbcp:1.4]
at org.apache.activemq.store.jdbc.DefaultDatabaseLocker.doStart(DefaultDatabaseLocker.java:72)[125:org.apache.activemq.activemq-core:5.7.0.fuse-71-047]
at org.apache.activemq.util.ServiceSupport.start(ServiceSupport.java:55)[125:org.apache.activemq.activemq-core:5.7.0.fuse-71-047]
at org.apache.activemq.broker.LockableServiceSupport.preStart(LockableServiceSupport.java:84)[125:org.apache.activemq.activemq-core:5.7.0.fuse-71-047]
at org.apache.activemq.util.ServiceSupport.start(ServiceSupport.java:54)[125:org.apache.activemq.activemq-core:5.7.0.fuse-71-047]
at org.apache.activemq.store.journal.JournalPersistenceAdapter.start(JournalPersistenceAdapter.java:267)[125:org.apache.activemq.activemq-core:5.7.0.fuse-71-047]
at org.apache.activemq.broker.BrokerService.doStartPersistenceAdapter(BrokerService.java:623)[125:org.apache.activemq.activemq-core:5.7.0.fuse-71-047]
at org.apache.activemq.broker.BrokerService.startPersistenceAdapter(BrokerService.java:612)[125:org.apache.activemq.activemq-core:5.7.0.fuse-71-047]
at org.apache.activemq.broker.BrokerService.start(BrokerService.java:577)[125:org.apache.activemq.activemq-core:5.7.0.fuse-71-047]
at org.fusesource.mq.fabric.ActiveMQServiceFactory$ClusteredConfiguration$$anon$3.run(ActiveMQServiceFactory.scala:307)[145:org.fusesource.mq.mq-fabric:7.1.0.fuse-047]
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.
Comments