Return-Path: 
 <dev-return-23903-apmail-activemq-dev-archive=activemq.apache.org@activemq.apache.org>
Delivered-To: apmail-activemq-dev-archive@www.apache.org
Received: (qmail 34421 invoked from network); 14 Dec 2010 12:36:28 -0000
Received: from unknown (HELO mail.apache.org) (140.211.11.3)
  by 140.211.11.9 with SMTP; 14 Dec 2010 12:36:28 -0000
Received: (qmail 58287 invoked by uid 500); 14 Dec 2010 12:36:28 -0000
Delivered-To: apmail-activemq-dev-archive@activemq.apache.org
Received: (qmail 58170 invoked by uid 500); 14 Dec 2010 12:36:25 -0000
Mailing-List: contact dev-help@activemq.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:dev-help@activemq.apache.org>
List-Unsubscribe: <mailto:dev-unsubscribe@activemq.apache.org>
List-Post: <mailto:dev@activemq.apache.org>
List-Id: <dev.activemq.apache.org>
Reply-To: dev@activemq.apache.org
Delivered-To: mailing list dev@activemq.apache.org
Received: (qmail 58124 invoked by uid 99); 14 Dec 2010 12:36:25 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 14 Dec 2010 12:36:25 +0000
X-ASF-Spam-Status: No, hits=-2000.0 required=10.0
	tests=ALL_TRUSTED
X-Spam-Check-By: apache.org
Received: from [140.211.11.22] (HELO thor.apache.org) (140.211.11.22)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 14 Dec 2010 12:36:24 +0000
Received: from thor (localhost [127.0.0.1])
	by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id oBECa3PG018170
	for <dev@activemq.apache.org>; Tue, 14 Dec 2010 12:36:03 GMT
Message-ID: <7309466.112801292330163339.JavaMail.jira@thor>
Date: Tue, 14 Dec 2010 07:36:03 -0500 (EST)
From: "Gary Tully (JIRA)" <jira@apache.org>
To: dev@activemq.apache.org
Subject: [jira] Commented: (AMQ-3075) Auto-create database fails with
 PostgreSQL (Error in SQL: 'drop primary key')
In-Reply-To: <13590155.25321291845960904.JavaMail.jira@thor>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394


    [ https://issues.apache.org/jira/browse/AMQ-3075?page=3Dcom.atlassian.j=
ira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D129712=
49#action_12971249 ]=20

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 mak=
ing 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 overri=
dden by the postgresql adapter.
{code}

<persistenceAdapter>
 <jdbcPersistenceAdapter dataSource=3D"#postgres-ds" useDatabaseLock=3D"fal=
se">
         <statements>
            <statements>
                        <property xmlns=3D"http://www.springframework.org/s=
chema/beans" name=3D"createSchemaStatements">
                            <list>
                             <value>CREATE TABLE ACTIVEMQ_MSGS(ID BIGINT NO=
T 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 ACTI=
VEMQ_MSGS (MSGID_PROD,MSGID_SEQ)</value>
                             <value>CREATE INDEX ACTIVEMQ_MSGS_CIDX ON ACTI=
VEMQ_MSGS (CONTAINER)</value>
                             <value>CREATE INDEX ACTIVEMQ_MSGS_EIDX ON ACTI=
VEMQ_MSGS (EXPIRATION)</value>
                             <value>CREATE TABLE ACTIVEMQ_ACKS(CONTAINER VA=
RCHAR(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 BIGI=
NT, PRIMARY KEY ( CONTAINER, CLIENT_ID, SUB_NAME))</value>
                             <value>CREATE TABLE ACTIVEMQ_LOCK( ID BIGINT N=
OT 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 ACTI=
VEMQ_MSGS (PRIORITY)</value>
                             <value>ALTER TABLE ACTIVEMQ_ACKS ADD PRIORITY =
BIGINT DEFAULT 5 NOT NULL</value>
                             <value> ALTER TABLE activemq_acks drop constra=
int activemq_acks_pkey</value>
                             <value>ALTER TABLE ACTIVEMQ_ACKS ADD PRIMARY K=
EY (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 k=
ey')
> -------------------------------------------------------------------------=
----
>
>                 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 databas=
e, 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" useDatabas=
eLock=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=
.support.DefaultListableBeanFactory@40b0095d: defining beans [org.springfra=
mework.beans.factory.config.PropertyPlaceholderConfigurer#0,postgres-ds,org=
.apache.activemq.xbean.XBeanBrokerService#0,securityLoginService,securityCo=
nstraint,securityConstraintMapping,securityHandler,contexts,Server]; root o=
f factory hierarchy
>  WARN | destroyApplicationContextOnStop parameter is deprecated, please u=
se shutdown hooks instead
>  INFO | PListStore:/home/wolpert/Downloads/apache-activemq-5.4.2/data/loc=
alhost/tmp_storage started
>  INFO | Using Persistence Adapter: JDBCPersistenceAdapter(org.postgresql.=
ds.PGPoolingDataSource@3302fc5)
>  INFO | Database adapter driver override recognized for : [postgresql_nat=
ive_driver] - adapter: class org.apache.activemq.store.jdbc.adapter.Postgre=
sqlJDBCAdapter
>  WARN | Could not create JDBC tables; they could already exist. Failure w=
as: 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 "PRIMAR=
Y"
>   Position: 32
>         at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(=
QueryExecutorImpl.java:2062)
>         at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryE=
xecutorImpl.java:1795)
>         at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutor=
Impl.java:257)
>         at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJd=
bc2Statement.java:479)
>         at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(A=
bstractJdbc2Statement.java:353)
>         at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJd=
bc2Statement.java:345)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccess=
orImpl.java:39)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMeth=
odAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:597)
>         at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$Statem=
entHandler.invoke(AbstractJdbc23PooledConnection.java:455)
>         at $Proxy5.execute(Unknown Source)
>         at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doCr=
eateTables(DefaultJDBCAdapter.java:101)
>         at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.start(JD=
BCPersistenceAdapter.java:272)
>         at org.apache.activemq.broker.BrokerService.start(BrokerService.j=
ava:485)
>         at org.apache.activemq.xbean.XBeanBrokerService.afterPropertiesSe=
t(XBeanBrokerService.java:60)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccess=
orImpl.java:39)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMeth=
odAccessorImpl.java:25)
> ...
> Database reports the following with its log turned on full.
> 2010-12-08 14:35:31 MST LOG:  execute <unnamed>: SET SESSION CHARACTERIST=
ICS 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.nspna=
me =3D 'pg_catalog' OR n.nspname =3D 'information_schema' THEN CASE c.relki=
nd   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' =
THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname =3D 'pg_toast' THEN C=
ASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM =
TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPOR=
ARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   ELSE NULL   END  END  WHEN f=
alse THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'i' THEN 'INDEX'  WHE=
N '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.clas=
soid=3Ddc.oid AND dc.relname=3D'pg_class')  LEFT JOIN pg_catalog.pg_namespa=
ce dn ON (dn.oid=3Ddc.relnamespace AND dn.nspname=3D'pg_catalog')  WHERE c.=
relnamespace =3D n.oid  AND c.relname LIKE 'ACTIVEMQ_MSGS'  AND (false  OR =
( c.relkind =3D 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_s=
chema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME=20
> 2010-12-08 14:35:31 MST LOG:  execute <unnamed>: CREATE TABLE ACTIVEMQ_MS=
GS(ID BIGINT NOT NULL, CONTAINER VARCHAR(250), MSGID_PROD VARCHAR(250), MSG=
ID_SEQ BIGINT, EXPIRATION BIGINT, MSG BYTEA, PRIMARY KEY ( ID ) )
> 2010-12-08 14:35:31 MST LOG:  execute <unnamed>: CREATE INDEX ACTIVEMQ_MS=
GS_MIDX ON ACTIVEMQ_MSGS (MSGID_PROD,MSGID_SEQ)
> 2010-12-08 14:35:31 MST LOG:  execute <unnamed>: CREATE INDEX ACTIVEMQ_MS=
GS_CIDX ON ACTIVEMQ_MSGS (CONTAINER)
> 2010-12-08 14:35:31 MST LOG:  execute <unnamed>: CREATE INDEX ACTIVEMQ_MS=
GS_EIDX ON ACTIVEMQ_MSGS (EXPIRATION)
> 2010-12-08 14:35:31 MST LOG:  execute <unnamed>: CREATE TABLE ACTIVEMQ_AC=
KS(CONTAINER VARCHAR(250) NOT NULL, SUB_DEST VARCHAR(250), CLIENT_ID VARCHA=
R(250) NOT NULL, SUB_NAME VARCHAR(250) NOT NULL, SELECTOR VARCHAR(250), LAS=
T_ACKED_ID BIGINT, PRIMARY KEY ( CONTAINER, CLIENT_ID, SUB_NAME))
> 2010-12-08 14:35:31 MST LOG:  execute <unnamed>: CREATE TABLE ACTIVEMQ_LO=
CK( 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_LOC=
K(ID) VALUES (1)
> 2010-12-08 14:35:32 MST LOG:  execute <unnamed>: ALTER TABLE ACTIVEMQ_MSG=
S ADD PRIORITY BIGINT
> 2010-12-08 14:35:32 MST LOG:  execute <unnamed>: CREATE INDEX ACTIVEMQ_MS=
GS_PIDX ON ACTIVEMQ_MSGS (PRIORITY)
> 2010-12-08 14:35:32 MST LOG:  execute <unnamed>: ALTER TABLE ACTIVEMQ_ACK=
S ADD PRIORITY BIGINT DEFAULT 5 NOT NULL
> 2010-12-08 14:35:32 MST ERROR:  syntax error at or near "PRIMARY" at char=
acter 32
> 2010-12-08 14:35:32 MST STATEMENT:  ALTER TABLE ACTIVEMQ_ACKS DROP PRIMAR=
Y 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 CHARACTERIST=
ICS 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 a=
t character 15
> 2010-12-08 14:35:32 MST STATEMENT:  SELECT * FROM ACTIVEMQ_LOCK FOR UPDAT=
E
> 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 anyway=
s 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 c=
onstraint', 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.