[
https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12971249#action_12971249
]
Gary Tully commented on AMQ-3075:
---------------------------------
Thanks for the detailed log information.
Yes, the use case is auto upgrading an existing datastore.
Can you validate that the rest of the statements work on postgresql by making
the changed to the xml configuration using a spring property update of the
form. This can also provide a workaround.
Think we can pull out the alter table statements to have them easily overridden
by the postgresql adapter.
{code}
<persistenceAdapter>
<jdbcPersistenceAdapter dataSource="#postgres-ds" useDatabaseLock="false">
<statements>
<statements>
<property
xmlns="http://www.springframework.org/schema/beans";
name="createSchemaStatements">
<list>
<value>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 ) )</value>
<value>CREATE INDEX ACTIVEMQ_MSGS_MIDX ON
ACTIVEMQ_MSGS (MSGID_PROD,MSGID_SEQ)</value>
<value>CREATE INDEX ACTIVEMQ_MSGS_CIDX ON
ACTIVEMQ_MSGS (CONTAINER)</value>
<value>CREATE INDEX ACTIVEMQ_MSGS_EIDX ON
ACTIVEMQ_MSGS (EXPIRATION)</value>
<value>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))</value>
<value>CREATE TABLE ACTIVEMQ_LOCK( ID BIGINT NOT
NULL, TIME BIGINT, BROKER_NAME VARCHAR(250), PRIMARY KEY (ID) )</value>
<value>INSERT INTO ACTIVEMQ_LOCK(ID) VALUES
(1)</value>
<value>ALTER TABLE ACTIVEMQ_MSGS ADD PRIORITY
BIGINT</value>
<value>CREATE INDEX ACTIVEMQ_MSGS_PIDX ON
ACTIVEMQ_MSGS (PRIORITY)</value>
<value>ALTER TABLE ACTIVEMQ_ACKS ADD PRIORITY
BIGINT DEFAULT 5 NOT NULL</value>
<value> ALTER TABLE activemq_acks drop constraint
activemq_acks_pkey</value>
<value>ALTER TABLE ACTIVEMQ_ACKS ADD PRIMARY KEY
(CONTAINER, CLIENT_ID, SUB_NAME, PRIORITY)</value>
</list>
</property>
</statements>
</statements>
</jdbcPersistenceAdapter>
</persistenceAdapter>
{code}
> 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: Gary Tully
>
> 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.defaultlistablebeanfact...@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.pgpoolingdatasou...@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 is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
