Ning Kang created ARTEMIS-5789:
----------------------------------
Summary: Artemis SQL SERVER Database Persistence bug
Key: ARTEMIS-5789
URL: https://issues.apache.org/jira/browse/ARTEMIS-5789
Project: Artemis
Issue Type: Bug
Components: ActiveMQ-Artemis-Native
Affects Versions: 2.44.0
Environment: Red Hat Enterprise Linux 8.10
openjdk version "17.0.17"
Artemis 2.44.0
Microsoft SQL Server 2019 (RTM-CU32-GDR) (KB5068404) - 15.0.4455.2 (X64)
Reporter: Ning Kang
Assignee: Clebert Suconic
Hi,
We are trying to use SQL Server as the Persistence for HA Shared Storage, and
got the following issues:
When connecting to the database for the first time, Artemis is trying to create
tables automatically, and shows the following errors
{code:java}
2025-12-03 12:31:33,847 ERROR
[org.apache.activemq.artemis.jdbc.store.drivers.AbstractJDBCDriver]
SQL STATEMENTS:
CREATE TABLE MQ_MESS (ID BIGINT NOT NULL IDENTITY, FILENAME VARCHAR(255),
EXTENSION VARCHAR(10), DATA VARBINARY(max), PRIMARY KEY(ID))
CREATE INDEX MQ_MESS_IDX ON MQ_MESS (fileName)
SQL EXCEPTIONS:
SQLState: S0001 ErrorCode: 1911 Message: Column name 'fileName' does not exist
in the target table or view.{code}
When checking the Artemis code
/activemq-artemis/artemis-jdbc-store/src/main/resources/journal-sql.properties,
we noticed 2 lines are using lowercase fileName, and all the other lines are
using uppercase FILENMAE
{code:java}
create-file-index=CREATE INDEX %1$s_IDX ON %1$s (fileName)
select-file-by-filename=SELECT ID, FILENAME, EXTENSION FROM %s WHERE
fileName=?{code}
So we changed this file (journal-sql.properties) inside
artemis-jdbc-store-2.44.0.jar, replaced the 2 "filename" with "FILENMAE", and
we can see all tables have been created in SQL Server now.
However, After the Artemis broker was started up, we saw another error "Invalid
column name 'id'"
{code:java}
2025-12-03 12:54:12,176 INFO [org.apache.activemq.artemis.core.server]
AMQ221006: Waiting to obtain primary lock 2025-12-03 12:54:12,256 INFO
[org.apache.activemq.artemis.core.server] AMQ221057: Global Max Size is being
adjusted to 1/2 of the JVM max size (-Xmx). being defined as 25769803776
2025-12-03 12:54:12,363 INFO [org.apache.activemq.artemis.core.server]
AMQ221043: Protocol module found: [artemis-server]. Adding protocol support
for: CORE 2025-12-03 12:54:12,364 INFO
[org.apache.activemq.artemis.core.server] AMQ221043: Protocol module found:
[artemis-amqp-protocol]. Adding protocol support for: AMQP 2025-12-03
12:54:12,365 INFO [org.apache.activemq.artemis.core.server] AMQ221043:
Protocol module found: [artemis-hornetq-protocol]. Adding protocol support for:
HORNETQ 2025-12-03 12:54:12,365 INFO [org.apache.activemq.artemis.core.server]
AMQ221043: Protocol module found: [artemis-mqtt-protocol]. Adding protocol
support for: MQTT 2025-12-03 12:54:12,366 INFO
[org.apache.activemq.artemis.core.server] AMQ221043: Protocol module found:
[artemis-openwire-protocol]. Adding protocol support for: OPENWIRE 2025-12-03
12:54:12,366 INFO [org.apache.activemq.artemis.core.server] AMQ221043:
Protocol module found: [artemis-stomp-protocol]. Adding protocol support for:
STOMP 2025-12-03 12:54:12,802 INFO [org.apache.activemq.artemis.core.server]
AMQ221034: Waiting indefinitely to obtain primary lock 2025-12-03 12:54:12,812
INFO [org.apache.activemq.artemis.core.server] AMQ221035: Primary Server
Obtained primary lock 2025-12-03 12:54:12,886 WARN
[org.apache.activemq.artemis.jdbc.store.journal.JDBCJournalImpl] Invalid column
name 'id'. com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name
'id'. at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:620)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:539)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7685)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4048)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:457)
at
org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:123)
at
org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:123)
at
org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:123)
at
org.apache.activemq.artemis.jdbc.store.journal.JDBCJournalImpl.load(JDBCJournalImpl.java:832)
at
org.apache.activemq.artemis.jdbc.store.journal.JDBCJournalImpl.load(JDBCJournalImpl.java:916)
at
org.apache.activemq.artemis.jdbc.store.journal.JDBCJournalImpl.load(JDBCJournalImpl.java:905)
at
org.apache.activemq.artemis.core.journal.Journal.load(Journal.java:276)
at
org.apache.activemq.artemis.core.persistence.impl.journal.AbstractJournalStorageManager.loadMessageJournal(AbstractJournalStorageManager.java:1038)
at
org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.loadJournals(ActiveMQServerImpl.java:3857)
at
org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.initialisePart2(ActiveMQServerImpl.java:3469)
at
org.apache.activemq.artemis.core.server.impl.SharedStorePrimaryActivation.run(SharedStorePrimaryActivation.java:94)
at
org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.internalStart(ActiveMQServerImpl.java:754)
at
org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.start(ActiveMQServerImpl.java:644)
at
org.apache.activemq.artemis.integration.FileBroker.start(FileBroker.java:67)
at org.apache.activemq.artemis.cli.commands.Run.execute(Run.java:132)
at org.apache.activemq.artemis.cli.Artemis.internalExecute(Artemis.java:222)
at org.apache.activemq.artemis.cli.Artemis.execute(Artemis.java:170)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
Method) at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at
org.apache.activemq.artemis.utils.actors.OrderedExecutor.doTask(OrderedExecutor.java:32)
at
org.apache.activemq.artemis.utils.actors.ProcessorBase.executePendingTasks(ProcessorBase.java:69)
at
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at
org.apache.activemq.artemis.utils.ActiveMQThreadFactory$1.run(ActiveMQThreadFactory.java:120)
2025-12-03 13:03:09,357 ERROR [org.apache.activemq.artemis.core.server]
AMQ224018: Failed to create session
org.apache.activemq.artemis.api.core.ActiveMQSessionCreationException:
AMQ229034: Server not started at
org.apache.activemq.artemis.core.protocol.core.impl.ActiveMQPacketHandler.handleCreateSession(ActiveMQPacketHandler.java:155)
at
org.apache.activemq.artemis.core.protocol.core.impl.ActiveMQPacketHandler.internalHandler(ActiveMQPacketHandler.java:106)
at org.apache.activemq.artemis.utils.actors.Actor.doTask(Actor.java:32){code}
Below is the Store settings in broker.xml
{code:java}
<store>
<database-store>
<data-source-properties>
<data-source-property key="driverClassName"
value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
<data-source-property key="url"
value="jdbc:sqlserver://*********:****;database=***" />
<data-source-property key="username"
value="*******" />
<data-source-property key="password"
value="*********" />
<data-source-property
key="poolPreparedStatements" value="true" />
</data-source-properties>
<bindings-table-name>MQ_BIND</bindings-table-name>
<message-table-name>MQ_MESS</message-table-name>
<page-store-table-name>MQ_MESS</page-store-table-name>
<large-message-table-name>MQ_LARG</large-message-table-name>
<node-manager-store-table-name>MQ_NODE</node-manager-store-table-name>
</database-store>
</store>{code}
And below is the HA Policy settings in broker.xml in master node and slave node
MASTER
{code:java}
<ha-policy>
<shared-store>
<primary>
<failover-on-shutdown>true</failover-on-shutdown>
</primary>
</shared-store>
</ha-policy>{code}
SLAVE
{code:java}
<ha-policy>
<shared-store>
<backup>
<failover-on-shutdown>true</failover-on-shutdown>
<allow-failback>true</allow-failback>
</backup>
</shared-store>
</ha-policy>{code}
Is this a bug from Artemis, or is there any configuration we did not set
correctly?
Thank you.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
For further information, visit: https://activemq.apache.org/contact
