ActiveMQ
  1. ActiveMQ
  2. AMQ-3075

Auto-create database fails with PostgreSQL (Error in SQL: 'drop primary key')

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 5.4.2
    • Fix Version/s: 5.5.0
    • Component/s: Broker
    • Labels:
      None
    • Environment:

      ActiveMQ 5.4.2 fresh install, Ubuntu 64-bit OpenJDK 6b20-1.9.2-0ubuntu1 PostgreSQL 8.4

      Description

      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;

        Activity

        Ned Wolpert created issue -
        Gary Tully made changes -
        Field Original Value New Value
        Assignee Gary Tully [ gtully ]
        Gary Tully made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 5.5.0 [ 12315626 ]
        Resolution Fixed [ 1 ]
        Gary Tully made changes -
        Resolution Fixed [ 1 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Dejan Bosanac made changes -
        Assignee Gary Tully [ gtully ] Dejan Bosanac [ dejanb ]

          People

          • Assignee:
            Dejan Bosanac
            Reporter:
            Ned Wolpert
          • Votes:
            3 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:

              Development