ActiveMQ具有正确的方言，但SQL语句不正确AUTO_INCREMENT而不是IDENTITY表创建

Question

我正在使用以下内容：

• JBoss 野蝇 15.0.1.决赛
• MSSQL JDBC connector (即 mssql-jdbc-7.2.2.jre11.jar)
• 单机满.xml

我相信我已经正确设置了所有内容。 MSSQL JDBC jar 位于modules目录中，其关联的module.xml。 数据源已正确定义且正常运行。ActiveMQ Artemis已经设置好(使用文件系统工作正常)。但是，当我尝试让它使用 JDBC 时，尝试创建必要的表失败并出现以下错误：

SQLState: S0001 ErrorCode: 102 Message: Incorrect syntax near AUTO_INCREMENT'.

所以我想知道出了什么问题。 ActiveMQ说它支持SQL Server，它根据驱动程序的返回值自动确定使用哪种SQL方言(我已经用6.0版本的驱动程序检查了该名称，它是相同的)，同样在调试输出中它说

[org.apache.activemq.artemis.jdbc.store.journal.JDBCJournalImpl] ServerService Thread Pool -- 80) Detect database dialect as 'MSSQL'".

所以这看起来不错。 所以我对出了什么问题有点困惑，想让它工作。 我正在提供我在standalone-full.xml中拥有的关键元素。 我需要任何其他设置还是这是一个错误？

这也是问题的症状吗？ (即查看日志输出...代理未使用数据源定义指定的正确驱动程序/URL 进行初始化)

AMQ221000: live Message Broker is starting with configuration Broker Configuration (clustered=false,jdbcDriverClassName=org.apache.derby.jdbc.EmbeddedDriver,jdbcConnectionUrl=null,messageTableName=AMQ_MESSAGES,bindingsTableName=AMQ_BINDINGS,largeMessageTableName=AMQ_LARGE_MESSAGES,pageStoreTableName=AMQ_PSTORE,)

独立-完整.xml设置摘录

<extensions> 
<extension module="org.wildfly.extension.messaging-activemq"/>
</extensions>
<datasources> 
<datasource jndi-name="java:/RMBM-DEFAULT-DS" pool-name="RMBM-DEFAULT-DS" enabled="true">
<connection-url>jdbc:sqlserver://${rmbm.default.ds.host}:${rmbm.default.ds.port:1433};DatabaseName=${rmbm.default.ds.dbname}
</connection-url>
<driver>SQLSVR</driver>
</datasource>
<drivers>
<driver name="SQLSVR" module="com.microsoft.sqlserver.jdbc">
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver- class>
</driver>
</drivers>
<subsystem xmlns="urn:jboss:domain:messaging-activemq:5.0">
<server name="default">
<journal datasource="RMBM-DEFAULT-DS" messages-table="AMQ_MESSAGES" bindings-table="AMQ_BINDINGS" 
jms-bindings-table="AMQ_JMS_BINDINGS" large-messages-table="AMQ_LARGE_MESSAGES" page-store-table="AMQ_PGSTORE"/> 
</server>
</subsystem>

以下是日志输出的一部分：

18:36:27,116 INFO  [org.wildfly.extension.messaging-activemq] (MSC service thread 1-8) WFLYMSGAMQ0001: AIO wasn't located on this platform, it will fall back to using pure Java NIO.
18:36:27,665 INFO  [org.wildfly.extension.undertow] (MSC service thread 1-1) WFLYUT0006: Undertow HTTPS listener https listening on 127.0.0.1:443
18:36:27,808 INFO  [org.jboss.ws.common.management] (MSC service thread 1-7) JBWS022052: Starting JBossWS 5.2.4.Final (Apache CXF 3.2.5.jbossorg-1) 
18:36:27,964 DEBUG [org.apache.activemq.artemis.jdbc.store.journal.JDBCJournalImpl] (MSC service thread 1-8) Detect database dialect as 'MSSQL'.
18:36:27,968 INFO  [org.wildfly.extension.messaging-activemq] (MSC service thread 1-8) use JDBC store for Artemis server, bindingsTable:AMQ_BINDINGS
18:36:28,065 DEBUG [org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl] (ServerService Thread Pool -- 80) Starting server ActiveMQServerImpl::
18:36:28,069 DEBUG [org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl] (ServerService Thread Pool -- 80) Detected no Shared Store HA options on JDBC store
18:36:28,121 INFO  [org.apache.activemq.artemis.core.server] (ServerService Thread Pool -- 80) AMQ221000: live Message Broker is starting with configuration Broker Configuration (clustered=false,jdbcDriverClassName=org.apache.derby.jdbc.EmbeddedDriver,jdbcConnectionUrl=null,messageTableName=AMQ_MESSAGES,bindingsTableName=AMQ_BINDINGS,largeMessageTableName=AMQ_LARGE_MESSAGES,pageStoreTableName=AMQ_PSTORE,)
18:36:28,306 DEBUG [org.apache.activemq.artemis.jdbc.store.journal.JDBCJournalImpl] (ServerService Thread Pool -- 80) Detect database dialect as 'MSSQL'.
18:36:28,398 ERROR [org.apache.activemq.artemis.jdbc.store.drivers.AbstractJDBCDriver] (ServerService Thread Pool -- 80) 
SQL STATEMENTS: 
CREATE TABLE AMQ_LARGE_MESSAGES (ID BIGINT AUTO_INCREMENT, FILENAME VARCHAR(255), EXTENSION VARCHAR(10), DATA BLOB, PRIMARY KEY(ID))
SQL EXCEPTIONS: 
SQLState: S0001 ErrorCode: 102 Message: Incorrect syntax near 'AUTO_INCREMENT'.
18:36:28,425 WARN  [org.apache.activemq.artemis.core.server] (ServerService Thread Pool -- 80) AMQ222010: Critical IO Error, shutting down the server. file=NULL, message=Unable to start database driver: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'AUTO_INCREMENT'.
at com.microsoft.sqlserver.jdbc//com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc//com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
at com.microsoft.sqlserver.jdbc//com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
at com.microsoft.sqlserver.jdbc//com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
at com.microsoft.sqlserver.jdbc//com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
at com.microsoft.sqlserver.jdbc//com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2935)
at com.microsoft.sqlserver.jdbc//com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
at com.microsoft.sqlserver.jdbc//com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
at com.microsoft.sqlserver.jdbc//com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:711)
at [e-mail]//org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:430)
at [e-mail]-00014//org.apache.activemq.artemis.jdbc.store.drivers.AbstractJDBCDriver.createTableIfNotExists(AbstractJDBCDriver.java:258)
at [e-mail]-00014//org.apache.activemq.artemis.jdbc.store.drivers.AbstractJDBCDriver.createTable(AbstractJDBCDriver.java:118)
at [e-mail]-00014//org.apache.activemq.artemis.jdbc.store.file.JDBCSequentialFileFactoryDriver.createSchema(JDBCSequentialFileFactoryDriver.java:69)
at [e-mail]-00014//org.apache.activemq.artemis.jdbc.store.drivers.AbstractJDBCDriver.start(AbstractJDBCDriver.java:83)
at [e-mail]-00014//org.apache.activemq.artemis.jdbc.store.file.JDBCSequentialFileFactory.start(JDBCSequentialFileFactory.java:131)
at [e-mail]-00014//org.apache.activemq.artemis.core.persistence.impl.journal.JDBCJournalStorageManager.init(JDBCJournalStorageManager.java:92)
at [e-mail]-00014//org.apache.activemq.artemis.core.persistence.impl.journal.AbstractJournalStorageManager.<init>(AbstractJournalStorageManager.java:229)
at [e-mail]-00014//org.apache.activemq.artemis.core.persistence.impl.journal.JournalStorageManager.<init>(JournalStorageManager.java:106)
at [e-mail]-00014//org.apache.activemq.artemis.core.persistence.impl.journal.JDBCJournalStorageManager.<init>(JDBCJournalStorageManager.java:55)
at [e-mail]-00014//org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.createStorageManager(ActiveMQServerImpl.java:2155)
at [e-mail]-00014//org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.initialisePart1(ActiveMQServerImpl.java:2296)
at [e-mail]-00014//org.apache.activemq.artemis.core.server.impl.LiveOnlyActivation.run(LiveOnlyActivation.java:64)
at [e-mail]-00014//org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.internalStart(ActiveMQServerImpl.java:535)
at [e-mail]-00014//org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.start(ActiveMQServerImpl.java:474)
at [e-mail]-00014//org.apache.activemq.artemis.jms.server.impl.JMSServerManagerImpl.start(JMSServerManagerImpl.java:376)
at org.wildfly.extension.messaging-activemq//org.wildfly.extension.messaging.activemq.jms.JMSService.doStart(JMSService.java:206)
at org.wildfly.extension.messaging-activemq//org.wildfly.extension.messaging.activemq.jms.JMSService.access$000(JMSService.java:65)
at org.wildfly.extension.messaging-activemq//org.wildfly.extension.messaging.activemq.jms.JMSService$1.run(JMSService.java:100)

Answer 1

看起来，即使方言被正确检测为MSSQL，代理也没有相应的 SQL 语句来正确支持它。MSSQL 的 SQL 语句是通过 ARTEMIS-2202 添加到 ActiveMQ Artemis 2.6.4 中的。您使用的是 2.6.3，因此您没有这些。

在Wildfly中升级ActiveMQ Artemis有时会由于两者之间的紧密耦合而失败，因此如果您不想尝试升级，理论上可以打开artemis-jdbc-store jar文件并添加/替换这些行以journal-sql.properties

# MSSQL SQL statements
create-file-table.mssql=CREATE TABLE %s (ID BIGINT NOT NULL IDENTITY, FILENAME VARCHAR(255), EXTENSION VARCHAR(10), DATA VARBINARY(max), PRIMARY KEY(ID))
create-journal-table.mssql=CREATE TABLE %s(id BIGINT,recordType SMALLINT,compactCount SMALLINT,txId BIGINT,userRecordType SMALLINT,variableSize INTEGER,record VARBINARY(max),txDataSize INTEGER,txData VARBINARY(max),txCheckNoRecords INTEGER,seq BIGINT NOT NULL, PRIMARY KEY(seq))
max-blob-size.mssql=2147483647

关于指示 JDBC 驱动程序配置不正确的AMQ221000日志消息...这是因为ActiveMQ Artemis代理在独立运行时有自己的JDBC驱动程序配置。当它嵌入到 Wildfly 中并配置为通过 JNDI 使用datasource时，不使用独立的 JDBC 驱动程序配置。它将简单地查找指定的数据源并使用它。