I'm using WildFly 11 and I've configured it to persist the messages into the database but I've already created the tables and when the server is starting it gives this error:
10:50:06,787 ERROR [org.apache.activemq.artemis.jdbc.store.drivers.AbstractJDBCDriver] (ServerService Thread Pool -- 74)
SQL STATEMENTS:
CREATE TABLE PAGE_STORE (ID BIGSERIAL, FILENAME VARCHAR(255), EXTENSION VARCHAR(10), DATA BYTEA, PRIMARY KEY(ID))
SQL EXCEPTIONS:
SQLState: 42P07 ErrorCode: 0 Message: ERROR: relation "page_store" already exists
10:50:06,950 WARN [org.apache.activemq.artemis.core.server] (ServerService Thread Pool -- 74) AMQ222010: Critical IO Error, shutting down the server. file=NULL, message=Unable to start database driver: org.postgresql.util.PSQLException: ERROR: relation "page_store" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:244)
at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:430)
at org.apache.activemq.artemis.jdbc.store.drivers.AbstractJDBCDriver.createTableIfNotExists(AbstractJDBCDriver.java:194) [artemis-jdbc-store-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.jdbc.store.drivers.AbstractJDBCDriver.createTable(AbstractJDBCDriver.java:112) [artemis-jdbc-store-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.jdbc.store.file.JDBCSequentialFileFactoryDriver.createSchema(JDBCSequentialFileFactoryDriver.java:69) [artemis-jdbc-store-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.jdbc.store.drivers.AbstractJDBCDriver.start(AbstractJDBCDriver.java:82) [artemis-jdbc-store-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.jdbc.store.file.JDBCSequentialFileFactory.start(JDBCSequentialFileFactory.java:127) [artemis-jdbc-store-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.core.paging.impl.PagingStoreFactoryDatabase.start(PagingStoreFactoryDatabase.java:124) [artemis-server-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.core.paging.impl.PagingStoreFactoryDatabase.<init>(PagingStoreFactoryDatabase.java:100) [artemis-server-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.getPagingStoreFactory(ActiveMQServerImpl.java:1854) [artemis-server-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.createPagingManager(ActiveMQServerImpl.java:1848) [artemis-server-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.initialisePart1(ActiveMQServerImpl.java:2015) [artemis-server-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.core.server.impl.LiveOnlyActivation.run(LiveOnlyActivation.java:62) [artemis-server-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.internalStart(ActiveMQServerImpl.java:518) [artemis-server-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.core.server.impl.ActiveMQServerImpl.start(ActiveMQServerImpl.java:466) [artemis-server-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.apache.activemq.artemis.jms.server.impl.JMSServerManagerImpl.start(JMSServerManagerImpl.java:413) [artemis-jms-server-1.5.5.jbossorg-008.jar:1.5.5.jbossorg-008]
at org.wildfly.extension.messaging.activemq.jms.JMSService.doStart(JMSService.java:205) [wildfly-messaging-activemq-11.0.0.Final.jar:11.0.0.Final]
at org.wildfly.extension.messaging.activemq.jms.JMSService.access$000(JMSService.java:64) [wildfly-messaging-activemq-11.0.0.Final.jar:11.0.0.Final]
at org.wildfly.extension.messaging.activemq.jms.JMSService$1.run(JMSService.java:99) [wildfly-messaging-activemq-11.0.0.Final.jar:11.0.0.Final]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [rt.jar:1.8.0_241]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_241]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_241]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_241]
at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_241]
at org.jboss.threads.JBossThread.run(JBossThread.java:320) [jboss-threads-2.2.1.Final.jar:2.2.1.Final]
This is the activemq server:
<subsystem xmlns="urn:jboss:domain:messaging-activemq:2.0">
<server name="default">
<journal datasource="PostgresLocalDS"/>
<security-setting name="#">
<role name="guest" send="true" consume="true" create-non-durable-queue="true" delete-non-durable-queue="true"/>
</security-setting>
<address-setting name="#" dead-letter-address="jms.queue.DLQ" expiry-address="jms.queue.ExpiryQueue" max-size-bytes="10485760" page-size-bytes="2097152" message-counter-history-day-limit="10"/>
<http-connector name="http-connector" socket-binding="http" endpoint="http-acceptor"/>
<http-connector name="http-connector-throughput" socket-binding="http" endpoint="http-acceptor-throughput">
<param name="batch-delay" value="50"/>
</http-connector>
<in-vm-connector name="in-vm" server-id="0"/>
<http-acceptor name="http-acceptor" http-listener="default"/>
<http-acceptor name="http-acceptor-throughput" http-listener="default">
<param name="batch-delay" value="50"/>
<param name="direct-deliver" value="false"/>
</http-acceptor>
<in-vm-acceptor name="in-vm" server-id="0"/>
<jms-queue name="ExpiryQueue" entries="java:/jms/queue/ExpiryQueue"/>
<jms-queue name="DLQ" entries="java:/jms/queue/DLQ"/>
<connection-factory name="InVmConnectionFactory" entries="java:/ConnectionFactory" connectors="in-vm"/>
<connection-factory name="RemoteConnectionFactory" connectors="http-connector" entries="java:jboss/exported/jms/RemoteConnectionFactory"/>
<pooled-connection-factory name="activemq-ra" entries="java:/JmsXA java:jboss/DefaultJMSConnectionFactory" connectors="in-vm" transaction="xa"/>
</server>
</subsystem>
And I read the model reference about the ActiveMQ subsystem and I didn't find any property that disables the automatic creation of the journal's table.
Remove the SQL template or delete the file's content from $JBOSS_HOME/modules/system/layers/base/org/wildfly/extension/messaging-activemq/main/database/journal-sql.properties it doesn't work.
So is there a way to disable the automatic creation?
There is no way to disable the schema creation however the AbstractJDBCDriver checks if the table exists before to create it, see the code. Maybe the user used to connect to the database hasn't the permissions to get the metadata.