Mailing-List: contact dev-help@activemq.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@activemq.apache.org
Message-ID: <13590155.25321291845960904.JavaMail.jira@thor>
Date: Wed, 8 Dec 2010 17:06:00 -0500 (EST)
From: "Ned Wolpert (JIRA)" <jira@apache.org>
To: dev@activemq.apache.org
Subject: [jira] Created: (AMQ-3075) Auto-create database fails with
 PostgreSQL (Error in SQL: 'drop primary key')
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

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 6=
b20-1.9.2-0ubuntu1 PostgreSQL 8.4
            Reporter: Ned Wolpert


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=3D"postgres-ds" class=3D"org.postgresql.ds.PGPoolingDataSource">
    <property name=3D"serverName" value=3D"localhost"/>
    <property name=3D"databaseName" value=3D"activemq"/>
    <property name=3D"portNumber" value=3D"5432"/>
    <property name=3D"user" value=3D"activemq"/>
    <property name=3D"password" value=3D"activemq"/>
    <property name=3D"dataSourceName" value=3D"postgres"/>
    <property name=3D"initialConnections" value=3D"1"/>
    <property name=3D"maxConnections" value=3D"10"/>
  </bean>
....
        <persistenceAdapter>
           <jdbcPersistenceAdapter dataSource=3D"#postgres-ds" useDatabaseL=
ock=3D"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.s=
upport.DefaultListableBeanFactory@40b0095d: defining beans [org.springframe=
work.beans.factory.config.PropertyPlaceholderConfigurer#0,postgres-ds,org.a=
pache.activemq.xbean.XBeanBrokerService#0,securityLoginService,securityCons=
traint,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/local=
host/tmp_storage started
 INFO | Using Persistence Adapter: JDBCPersistenceAdapter(org.postgresql.ds=
.PGPoolingDataSource@3302fc5)
 INFO | Database adapter driver override recognized for : [postgresql_nativ=
e_driver] - adapter: class org.apache.activemq.store.jdbc.adapter.Postgresq=
lJDBCAdapter
 WARN | Could not create JDBC tables; they could already exist. Failure was=
: ALTER TABLE ACTIVEMQ_ACKS DROP PRIMARY KEY Message: ERROR: syntax error a=
t 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(Qu=
eryExecutorImpl.java:2062)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExe=
cutorImpl.java:1795)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorIm=
pl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc=
2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abs=
tractJdbc2Statement.java:353)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc=
2Statement.java:345)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessor=
Impl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethod=
AccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$Statemen=
tHandler.invoke(AbstractJdbc23PooledConnection.java:455)
        at $Proxy5.execute(Unknown Source)
        at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doCrea=
teTables(DefaultJDBCAdapter.java:101)
        at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.start(JDBC=
PersistenceAdapter.java:272)
        at org.apache.activemq.broker.BrokerService.start(BrokerService.jav=
a:485)
        at org.apache.activemq.xbean.XBeanBrokerService.afterPropertiesSet(=
XBeanBrokerService.java:60)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessor=
Impl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethod=
AccessorImpl.java:25)
...

Database reports the following with its log turned on full.

2010-12-08 14:35:31 MST LOG:  execute <unnamed>: SET SESSION CHARACTERISTIC=
S 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 =3D 'information_schema'  WHEN true THEN CASE  WHEN n.nspname=
 =3D 'pg_catalog' OR n.nspname =3D 'information_schema' THEN CASE c.relkind=
   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' TH=
EN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname =3D 'pg_toast' THEN CAS=
E c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TO=
AST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORAR=
Y TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   ELSE NULL   END  END  WHEN fal=
se 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 =3D d.=
objoid AND d.objsubid =3D 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classo=
id=3Ddc.oid AND dc.relname=3D'pg_class')  LEFT JOIN pg_catalog.pg_namespace=
 dn ON (dn.oid=3Ddc.relnamespace AND dn.nspname=3D'pg_catalog')  WHERE c.re=
lnamespace =3D n.oid  AND c.relname LIKE 'ACTIVEMQ_MSGS'  AND (false  OR ( =
c.relkind =3D 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_sch=
ema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME=20
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 (I=
D) )
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 charac=
ter 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 ig=
nored until end of transaction block
2010-12-08 14:35:32 MST STATEMENT:  ALTER TABLE ACTIVEMQ_ACKS ADD PRIMARY K=
EY (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 CHARACTERISTIC=
S 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 f=
or 5.4 from an earlier version. If the goal is to drop the 'primary key con=
straint', the code should execute this instead:

ALTER TABLE activemq_acks drop constraint activemq_acks_pkey;


--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.