[
https://issues.apache.org/jira/browse/AMQ-3189?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15172818#comment-15172818
]
Volker Kleinschmidt edited comment on AMQ-3189 at 3/2/16 10:47 PM:
-------------------------------------------------------------------
It appears this issue is simply due to a case discrepancy between the form of
the table name used during creation and the one used when checking for
existence. Note that the creation statement uses all upper case, but the
existence message reports the tablename in lower case. All identifiers
(including column names) that are not double-quoted are folded to lower case in
PostgreSQL. It does not help that this is exactly the opposite behavior from
Oracle!
A proper table existence query in Oracle:
{noformat}
select count(1) from user_tables where table_name=UPPER(string_to_check);
{noformat}
A proper table existence query in PostGres:
{noformat}
select count(1) from information_schema.tables
where table_catalog = CURRENT_CATALOG
and table_schema = CURRENT_SCHEMA
and table_name = LOWER(string_to_check);
{noformat}
was (Author: volkerk):
It appears this issue is simply due to a case discrepancy between the form of
the table name used during creation and the one used when checking for
existence.
> 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.3.4#6332)
