[jira] [Comment Edited] (AMQ-3189) Postgresql with spring embedded activeMQ has "table already created" exception

["Gary Tully (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/AMQ-3189?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16198531#comment-16198531 ] 

Gary Tully edited comment on AMQ-3189 at 10/10/17 11:30 AM:
------------------------------------------------------------

The simplest thing is to configure a lower case messageTableName in the statements. That will populate the getFullMessageTableName used in the check for existence.

{code}
<amq:statements tablePrefix="@TOKEN.TABLE.PREFIX@" messageTableName="activemq_msgs"  {code}


There are metaData attributes that could filter in to the check around lower/uppercase - but this bit of code is hyper configurable already just for these sorts of reasons. 



was (Author: gtully):
The simplest thing is to specify a lower case messageTableName in the statements.

{code}
<amq:statements tablePrefix="@TOKEN.TABLE.PREFIX@" messageTableName="activemq_msgs"  {code}



> Postgresql with spring embedded activeMQ has "table already created" exception
> ------------------------------------------------------------------------------
>
>                 Key: AMQ-3189
>                 URL: https://issues.apache.org/jira/browse/AMQ-3189
>             Project: ActiveMQ
>          Issue Type: Improvement
>    Affects Versions: 5.4.2
>         Environment: Postgresql 8.4,  latest Postgresql JDBC9.0.x, tomcat 6.x, spring 2.5
>            Reporter: steve neo
>
> This may not a bug as MQ is still workable after this exception warning. However, can you suppress the exception stack in log? It even can not be a kind of "warning" as this is just a normal process to detect if tables exist or not.
> Same configuration works fine with MySQL. For postgresql, first time starting will create table without problem After restart tomcat, log prints some annoying failure message with long exception stack. 
> ============= Exception ================
> 13:38:53] INFO  [JDBCPersistenceAdapter] Database adapter driver override recognized for : [postgresql_native_driver] - adapter: class org.apache.activemq.store.jdbc.adapter.PostgresqlJDBCAdapter
> [13:38:53] WARN  [DefaultJDBCAdapter] Could not create JDBC tables; they could already exist. Failure was: CREATE TABLE EDG_ACTIVEMQ_MSGS(ID BIGINT NOT NULL, CONTAINER VARCHAR(80), MSGID_PROD VARCHAR(80), MSGID_SEQ BIGINT, EXPIRATION BIGINT, MSG BYTEA, PRIMARY KEY ( ID ) ) Message: ERROR: relation "edg_activemq_msgs" already exists SQLState: 42P07 Vendor code: 0
> [13:38:53] WARN  [JDBCPersistenceAdapter] Failure details: ERROR: relation "edg_activemq_msgs" already exists
> org.postgresql.util.PSQLException: ERROR: relation "edg_activemq_msgs" already exists
>  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) ~[postgresql-9.0-801.jdbc4.jar:na]
>  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) ~[postgresql-9.0-801.jdbc4.jar:na]
>  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) ~[postgresql-9.0-801.jdbc4.jar:na]
>  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) ~[postgresql-9.0-801.jdbc4.jar:na]
>  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) ~[postgresql-9.0-801.jdbc4.jar:na]
>  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366) ~[postgresql-9.0-801.jdbc4.jar:na]
>  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_21]
>  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_21]
> ============= End Exception ================
> I try dbcp.DataSource and postgresql Datasource both.
> ============= Datasource ================
> <bean id="activeMQDS" class="org.postgresql.ds.PGPoolingDataSource" destroy-method="close">
>         <property name="serverName" value="10.88.30.144"/> 
>     <property name="databaseName" value="mydb"/> 
>     <property name="portNumber" value="0"/> 
>         <property name="user" value="${server.db.username}"/>
>         <property name="password" value="${server.db.password}"/>
>         <property name="dataSourceName" value="activeMQDS"/> 
>    <property name="initialConnections" value="1"/> 
>     <property name="maxConnections" value="10"/> 
>     </bean>
> ============= broker ================
> 	<amq:broker id="activeMQBroker" useJmx="false" persistent="true" start="true" useShutdownHook="true" dataDirectory="${geniuswiki.tmp.dir}activemq-data">
> 		<amq:persistenceAdapter>
> 			<amq:jdbcPersistenceAdapter id="jdbcAdapter" dataSource="#coreDS" createTablesOnStartup="true" useDatabaseLock="false">
> 					<amq:statements tablePrefix="@TOKEN.TABLE.PREFIX@"	stringIdDataType ="VARCHAR(80)" msgIdDataType="VARCHAR(80)" containerNameDataType="VARCHAR(80)"/>
> 				</amq:statements>
> 			</amq:jdbcPersistenceAdapter>
> 		</amq:persistenceAdapter>
> 		<amq:transportConnectors>
> 			<amq:transportConnector uri="tcp://${mq.server.url}?wireFormat.maxInactivityDuration=0"/>
> 		</amq:transportConnectors>
> 	</amq:broker>



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)