Uploaded image for project: 'AMQ Broker'
  1. AMQ Broker
  2. ENTMQBR-3491

AMQ224000: Failure in initialisation: java.lang.IllegalStateException: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from timestamp to TIMESTAMP is unsupported.

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • AMQ 7.7.0.CR2
    • AMQ 7.5.0.GA
    • jdbc
    • None
    • Compatibility/Configuration
    • Workaround Exists
    • Hide

      The workaround is to create the AMQ DB Table manually and switch the "HOLDER_EXPIRATION_TIME" to "datetime", then it works.

      USE [amq]
GO

      /****** Object:  Table [dbo].[NODE_MANAGER_TABLE]    Script Date: 21.04.2020 14:01:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NODE_MANAGER_TABLE]') AND type in (N'U'))
DROP TABLE [dbo].[NODE_MANAGER_TABLE]
GO

/****** Object:  Table [dbo].[NODE_MANAGER_TABLE]    Script Date: 21.04.2020 14:01:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NODE_MANAGER_TABLE](
[ID] [int] NOT NULL,
[HOLDER_ID] [varchar](128) NULL,
[HOLDER_EXPIRATION_TIME] [datetime] NULL,
[NODE_ID] [char](36) NULL,
[STATE] [char](1) NULL,
PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
      Show
      The workaround is to create the AMQ DB Table manually and switch the "HOLDER_EXPIRATION_TIME" to "datetime", then it works. USE [amq] GO /****** Object : Table [dbo].[NODE_MANAGER_TABLE] Script Date: 21.04.2020 14:01:55 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N '[dbo].[NODE_MANAGER_TABLE]' ) AND type in (N 'U' )) DROP TABLE [dbo].[NODE_MANAGER_TABLE] GO /****** Object : Table [dbo].[NODE_MANAGER_TABLE] Script Date: 21.04.2020 14:01:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[NODE_MANAGER_TABLE]( [ID] [ int ] NOT NULL, [HOLDER_ID] [varchar](128) NULL, [HOLDER_EXPIRATION_TIME] [datetime] NULL, [NODE_ID] [ char ](36) NULL, [STATE] [ char ](1) NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
    • Hide

      1. Create a SQL Server instance using podman

      #pull the sql server from docker hub
podman pull mcr.microsoft.com/mssql/server:2017-latest

#pull the sql server from docker hub
podman run --name amq-mssql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest
 
#Create database 'amq'
podman exec -it amq-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password'

# Create amq db using sqlcmd
1> CREATE DATABASE amq
2> go

# verify the amq db is created using sqlcmd
1> SELECT Name from sys.Databases
2> go

      2. using broker.xml.single, this works. 

          <store>
       <database-store>
          <jdbc-connection-url>jdbc:sqlserver://localhost:1433;databaseName=amq</jdbc-connection-url>
          <jdbc-user>sa</jdbc-user>
          <jdbc-password>yourStrong(!)Password</jdbc-password>
          <bindings-table-name>BINDINGS_TABLE</bindings-table-name>
          <message-table-name>MESSAGE_TABLE</message-table-name>
          <large-message-table-name>LARGE_MESSAGES_TABLE</large-message-table-name>
          <page-store-table-name>PAGE_TABLE</page-store-table-name>
          <node-manager-store-table-name>NODE_MANAGER_TABLE</node-manager-store-table-name>
          <jdbc-driver-class-name>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbc-driver-class-name>
          <jdbc-network-timeout>10000</jdbc-network-timeout>
          <jdbc-lock-renew-period>2000</jdbc-lock-renew-period>
          <jdbc-lock-expiration>15000</jdbc-lock-expiration>
          <jdbc-journal-sync-period>5</jdbc-journal-sync-period>
       </database-store>
    </store>

      3. using broker.xml.ha, this fails 

           <ha-policy>
            <shared-store>
                <master>
			<failover-on-shutdown>true</failover-on-shutdown>
                </master>
            </shared-store>
        </ha-policy>
      Show
      1. Create a SQL Server instance using podman #pull the sql server from docker hub podman pull mcr.microsoft.com/mssql/server:2017-latest #pull the sql server from docker hub podman run --name amq-mssql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest #Create database 'amq' podman exec -it amq-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' # Create amq db using sqlcmd 1> CREATE DATABASE amq 2> go # verify the amq db is created using sqlcmd 1> SELECT Name from sys.Databases 2> go 2. using broker.xml.single, this works. <store> <database-store> <jdbc-connection-url>jdbc:sqlserver: //localhost:1433;databaseName=amq</jdbc-connection-url> <jdbc-user>sa</jdbc-user> <jdbc-password>yourStrong(!)Password</jdbc-password> <bindings-table-name>BINDINGS_TABLE</bindings-table-name> <message-table-name>MESSAGE_TABLE</message-table-name> <large-message-table-name>LARGE_MESSAGES_TABLE</large-message-table-name> <page-store-table-name>PAGE_TABLE</page-store-table-name> <node-manager-store-table-name>NODE_MANAGER_TABLE</node-manager-store-table-name> <jdbc-driver- class- name>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbc-driver- class- name> <jdbc-network-timeout>10000</jdbc-network-timeout> <jdbc-lock-renew-period>2000</jdbc-lock-renew-period> <jdbc-lock-expiration>15000</jdbc-lock-expiration> <jdbc-journal-sync-period>5</jdbc-journal-sync-period> </database-store> </store> 3. using broker.xml.ha, this fails <ha-policy> <shared-store> <master> <failover-on-shutdown> true </failover-on-shutdown> </master> </shared-store> </ha-policy>

      When using JDBC on SQL server with HA; the broker fails during the initialization. 

      2020-04-19 12:30:12,588 ERROR [org.apache.activemq.artemis.core.server] AMQ224000: Failure in initialisation: java.lang.IllegalStateException: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from timestamp to TIMESTAMP is unsupported.
	at org.apache.activemq.artemis.core.server.impl.jdbc.JdbcLeaseLock.checkValidHolderId(JdbcLeaseLock.java:262) [artemis-server-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.core.server.impl.jdbc.JdbcLeaseLock.isHeld(JdbcLeaseLock.java:216) [artemis-server-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.core.server.impl.jdbc.JdbcNodeManager.isBackupLive(JdbcNodeManager.java:270) [artemis-server-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.core.server.impl.SharedStoreLiveActivation.run(SharedStoreLiveActivation.java:52) [artemis-server-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.internalStart(ActiveMQServerImpl.java:600) [artemis-server-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.start(ActiveMQServerImpl.java:527) [artemis-server-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.integration.FileBroker.start(FileBroker.java:70) [artemis-cli-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.cli.commands.Run.execute(Run.java:85) [artemis-cli-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.cli.Artemis.internalExecute(Artemis.java:150) [artemis-cli-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.cli.Artemis.execute(Artemis.java:98) [artemis-cli-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.cli.Artemis.execute(Artemis.java:125) [artemis-cli-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_242]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_242]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_242]
	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_242]
	at org.apache.activemq.artemis.boot.Artemis.execute(Artemis.java:129) [artemis-boot.jar:2.10.0.redhat-00004]
	at org.apache.activemq.artemis.boot.Artemis.main(Artemis.java:49) [artemis-boot.jar:2.10.0.redhat-00004]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from timestamp to TIMESTAMP is unsupported.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228) [mssql-jdbc-6.4.0.jre8.jar:]
	at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:1647) [mssql-jdbc-6.4.0.jre8.jar:]
	at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:3847) [mssql-jdbc-6.4.0.jre8.jar:]
	at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:289) [mssql-jdbc-6.4.0.jre8.jar:]
	at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:198) [mssql-jdbc-6.4.0.jre8.jar:]
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1919) [mssql-jdbc-6.4.0.jre8.jar:]
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1900) [mssql-jdbc-6.4.0.jre8.jar:]
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getTimestamp(SQLServerResultSet.java:2405) [mssql-jdbc-6.4.0.jre8.jar:]
	at org.apache.activemq.artemis.core.server.impl.jdbc.JdbcLeaseLock.checkValidHolderId(JdbcLeaseLock.java:239) [artemis-server-2.10.0.redhat-00004.jar:2.10.0.redhat-00004]
	... 16 more

        1. 2020-06-25_11-26.png
          361 kB
          Oleg Sushchenko
        2. 2020-06-25_11-27.png
          513 kB
          Oleg Sushchenko
        3. artemis.log
          14 kB
          Roger Hui
        4. broker.xml
          11 kB
          Tiago Bueno
        5. broker.xml
          11 kB
          Roger Hui
        6. log.txt
          9 kB
          Tiago Bueno

              fnigro Francesco Nigro
              rhn-support-whui Roger Hui
              Oleg Sushchenko Oleg Sushchenko
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

                Created:
                Updated:
                Resolved: