Unable to use JDBC Persistence Adapter with Microsoft SQL Server

Solution Verified - Updated -

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