activemq-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gary Tully (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (AMQ-3075) Auto-create database fails with PostgreSQL (Error in SQL: 'drop primary key')
Date Tue, 14 Dec 2010 15:21:01 GMT

     [ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Gary Tully resolved AMQ-3075.
-----------------------------

       Resolution: Fixed
    Fix Version/s: 5.5.0

r1049122

Postgres adapter overrides the alter statement to resolve. new attribute on statements to
expose the variability and acksPkName attribute on posgres adapter in case generated name
of pk constraint changes.


> 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
>             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 is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message