[
https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17266267#comment-17266267
]
Matt Pavlovich commented on AMQ-3075:
-------------------------------------
[~wolpert] [~volkerk][~rui.b.zhang] This issue is quite dated and may be
resolved in the heavy JDBC updates that came up through 5.16.1. Is this still
an issue?
> Auto-create database fails with PostgreSQL (Error in SQL: 'drop primary key')
> -----------------------------------------------------------------------------
>
> Key: AMQ-3075
> URL: https://issues.apache.org/jira/browse/AMQ-3075
> Project: ActiveMQ
> Issue Type: Bug
> Components: Broker
> Affects Versions: 5.4.2
> Environment: ActiveMQ 5.4.2 fresh install, Ubuntu 64-bit OpenJDK
> 6b20-1.9.2-0ubuntu1 PostgreSQL 8.4
> Reporter: Ned Wolpert
> Assignee: Dejan Bosanac
> Priority: Major
> Fix For: 5.5.0
>
>
> Trying to do a fresh install with persistence fails to create the database,
> with a listed database error.
> Persistence support added to activemq.xml file:
> <bean id="postgres-ds" class="org.postgresql.ds.PGPoolingDataSource">
> <property name="serverName" value="localhost"/>
> <property name="databaseName" value="activemq"/>
> <property name="portNumber" value="5432"/>
> <property name="user" value="activemq"/>
> <property name="password" value="activemq"/>
> <property name="dataSourceName" value="postgres"/>
> <property name="initialConnections" value="1"/>
> <property name="maxConnections" value="10"/>
> </bean>
> ....
> <persistenceAdapter>
> <jdbcPersistenceAdapter dataSource="#postgres-ds"
> useDatabaseLock="false"/>
> </persistenceAdapter>
> postgresql-8.4-701.jdbc4.jar added to the lib directory
> Log from startup:
> INFO | Pre-instantiating singletons in
> org.springframework.beans.factory.support.DefaultListableBeanFactory@40b0095d:
> defining beans
> [org.springframework.beans.factory.config.PropertyPlaceholderConfigurer#0,postgres-ds,org.apache.activemq.xbean.XBeanBrokerService#0,securityLoginService,securityConstraint,securityConstraintMapping,securityHandler,contexts,Server];
> root of factory hierarchy
> WARN | destroyApplicationContextOnStop parameter is deprecated, please use
> shutdown hooks instead
> INFO |
> PListStore:/home/wolpert/Downloads/apache-activemq-5.4.2/data/localhost/tmp_storage
> started
> INFO | Using Persistence Adapter:
> JDBCPersistenceAdapter(org.postgresql.ds.PGPoolingDataSource@3302fc5)
> INFO | Database adapter driver override recognized for :
> [postgresql_native_driver] - adapter: class
> org.apache.activemq.store.jdbc.adapter.PostgresqlJDBCAdapter
> WARN | Could not create JDBC tables; they could already exist. Failure was:
> ALTER TABLE ACTIVEMQ_ACKS DROP PRIMARY KEY Message: ERROR: syntax error at or
> near "PRIMARY"
> Position: 32 SQLState: 42601 Vendor code: 0
> WARN | Failure details: ERROR: syntax error at or near "PRIMARY"
> Position: 32
> org.postgresql.util.PSQLException: ERROR: syntax error at or near "PRIMARY"
> Position: 32
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:345)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at
> org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:455)
> at $Proxy5.execute(Unknown Source)
> at
> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doCreateTables(DefaultJDBCAdapter.java:101)
> at
> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.start(JDBCPersistenceAdapter.java:272)
> at
> org.apache.activemq.broker.BrokerService.start(BrokerService.java:485)
> at
> org.apache.activemq.xbean.XBeanBrokerService.afterPropertiesSet(XBeanBrokerService.java:60)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> ...
> Database reports the following with its log turned on full.
> 2010-12-08 14:35:31 MST LOG: execute <unnamed>: SET SESSION CHARACTERISTICS
> AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> 2010-12-08 14:35:31 MST LOG: execute S_1: BEGIN
> 2010-12-08 14:35:31 MST LOG: execute <unnamed>: SELECT NULL AS TABLE_CAT,
> n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_'
> OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname =
> 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN
> 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM
> INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind
> WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX'
> ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN
> 'i' THEN 'TEMPORARY INDEX' ELSE NULL END END WHEN false THEN CASE
> c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN
> 'SEQUENCE' WHEN 'v' THEN 'VIEW' ELSE NULL END ELSE NULL END AS
> TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namespace n,
> pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_description d ON (c.oid =
> d.objoid AND d.objsubid = 0) LEFT JOIN pg_catalog.pg_class dc ON
> (d.classoid=dc.oid AND dc.relname='pg_class') LEFT JOIN
> pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND
> dn.nspname='pg_catalog') WHERE c.relnamespace = n.oid AND c.relname LIKE
> 'ACTIVEMQ_MSGS' AND (false OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND
> n.nspname <> 'information_schema' ) ) ORDER BY
> TABLE_TYPE,TABLE_SCHEM,TABLE_NAME
> 2010-12-08 14:35:31 MST LOG: execute <unnamed>: CREATE TABLE
> ACTIVEMQ_MSGS(ID BIGINT NOT NULL, CONTAINER VARCHAR(250), MSGID_PROD
> VARCHAR(250), MSGID_SEQ BIGINT, EXPIRATION BIGINT, MSG BYTEA, PRIMARY KEY (
> ID ) )
> 2010-12-08 14:35:31 MST LOG: execute <unnamed>: CREATE INDEX
> ACTIVEMQ_MSGS_MIDX ON ACTIVEMQ_MSGS (MSGID_PROD,MSGID_SEQ)
> 2010-12-08 14:35:31 MST LOG: execute <unnamed>: CREATE INDEX
> ACTIVEMQ_MSGS_CIDX ON ACTIVEMQ_MSGS (CONTAINER)
> 2010-12-08 14:35:31 MST LOG: execute <unnamed>: CREATE INDEX
> ACTIVEMQ_MSGS_EIDX ON ACTIVEMQ_MSGS (EXPIRATION)
> 2010-12-08 14:35:31 MST LOG: execute <unnamed>: CREATE TABLE
> ACTIVEMQ_ACKS(CONTAINER VARCHAR(250) NOT NULL, SUB_DEST VARCHAR(250),
> CLIENT_ID VARCHAR(250) NOT NULL, SUB_NAME VARCHAR(250) NOT NULL, SELECTOR
> VARCHAR(250), LAST_ACKED_ID BIGINT, PRIMARY KEY ( CONTAINER, CLIENT_ID,
> SUB_NAME))
> 2010-12-08 14:35:31 MST LOG: execute <unnamed>: CREATE TABLE ACTIVEMQ_LOCK(
> ID BIGINT NOT NULL, TIME BIGINT, BROKER_NAME VARCHAR(250), PRIMARY KEY (ID) )
> 2010-12-08 14:35:32 MST LOG: execute <unnamed>: INSERT INTO
> ACTIVEMQ_LOCK(ID) VALUES (1)
> 2010-12-08 14:35:32 MST LOG: execute <unnamed>: ALTER TABLE ACTIVEMQ_MSGS
> ADD PRIORITY BIGINT
> 2010-12-08 14:35:32 MST LOG: execute <unnamed>: CREATE INDEX
> ACTIVEMQ_MSGS_PIDX ON ACTIVEMQ_MSGS (PRIORITY)
> 2010-12-08 14:35:32 MST LOG: execute <unnamed>: ALTER TABLE ACTIVEMQ_ACKS
> ADD PRIORITY BIGINT DEFAULT 5 NOT NULL
> 2010-12-08 14:35:32 MST ERROR: syntax error at or near "PRIMARY" at
> character 32
> 2010-12-08 14:35:32 MST STATEMENT: ALTER TABLE ACTIVEMQ_ACKS DROP PRIMARY KEY
> 2010-12-08 14:35:32 MST ERROR: current transaction is aborted, commands
> ignored until end of transaction block
> 2010-12-08 14:35:32 MST STATEMENT: ALTER TABLE ACTIVEMQ_ACKS ADD PRIMARY KEY
> (CONTAINER, CLIENT_ID, SUB_NAME, PRIORITY)
> 2010-12-08 14:35:32 MST LOG: execute S_2: COMMIT
> 2010-12-08 14:35:32 MST LOG: execute <unnamed>: SET SESSION CHARACTERISTICS
> AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> 2010-12-08 14:35:32 MST LOG: execute S_1: BEGIN
> 2010-12-08 14:35:32 MST ERROR: relation "activemq_lock" does not exist at
> character 15
> 2010-12-08 14:35:32 MST STATEMENT: SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE
> 2010-12-08 14:35:32 MST LOG: execute S_3: ROLLBACK
> 2010-12-08 14:35:32 MST LOG: unexpected EOF on client connection
> The specific error is: ALTER TABLE ACTIVEMQ_ACKS DROP PRIMARY KEY
> The first obvious question is why is the primary key being created anyways if
> your just dropping it. Though its likely due to upgrading the database for
> 5.4 from an earlier version. If the goal is to drop the 'primary key
> constraint', the code should execute this instead:
> ALTER TABLE activemq_acks drop constraint activemq_acks_pkey;
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
