You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@activemq.apache.org by "Ned Wolpert (JIRA)" <ji...@apache.org> on 2010/12/08 23:06:00 UTC
[jira] Created: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
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
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.
[jira] [Reopened] (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Gary Tully (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Gary Tully reopened AMQ-3075:
-----------------------------
need to add some dynamic sql to determine the pk for the drop statements.
> 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.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Gary Tully (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Gary Tully reassigned AMQ-3075:
-------------------------------
Assignee: Gary Tully
> 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.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.
[jira] Commented: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Kaiqin Sun (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12983333#action_12983333 ]
Kaiqin Sun commented on AMQ-3075:
---------------------------------
Hi Gary,
SQL Server 2005 has the same problem as PostgreSQL. Could you please update TransactJDBCAdapter as well?
--Kevin
> 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.
[jira] Commented: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Gary Tully (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12971250#action_12971250 ]
Gary Tully commented on AMQ-3075:
---------------------------------
or maybe the "ALTER TABLE activemq_acks drop constraint activemq_acks_pkey" syntax will work across the board.
> 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.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.
[jira] Commented: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Gary Tully (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12983896#action_12983896 ]
Gary Tully commented on AMQ-3075:
---------------------------------
I think we may have to go with the dynamic sql to find the pk, the problem is an upgrade from a schema where the pk name was not provided when the constraint was created. Adding the pk name is probably a good idea though for the future.
> 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.
[jira] Issue Comment Edited: (AMQ-3075) Auto-create database fails
with PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Kaiqin Sun (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12983715#action_12983715 ]
Kaiqin Sun edited comment on AMQ-3075 at 1/19/11 2:37 PM:
----------------------------------------------------------
Hi Gary,
Could we define the primary key constraint name when creating ACTIVEMQ_ACKS? Then we could have a fixed constraint name and this name could be used in ALTER SQL.
For instance, when creating the table we use the following SQL:
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,
CONSTRAINT PK_ACTIVEMQ_ACKS PRIMARY KEY
(
CONTAINER,
CLIENT_ID,
SUB_NAME
)
)
We could have the unique CONSTRAINT name as PK_ACTIVEMQ_ACKS.
Then we could drop the constraint like this:
ALTER TABLE ACTIVEMQ_ACKS DROP CONSTRAINT PK_ACTIVEMQ_ACKS
This solution works for SQL server and could work for Oracle as well. I am not sure if this could work for other DBs.
--Kevin
was (Author: kaiqinsun):
Hi Gary,
For SQL Server, could we add the contraint name when creating ACTIVEMQ tables? For example:
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,
CONSTRAINT PK_ACTIVEMQ_ACKS PRIMARY KEY CLUSTERED
(
CONTAINER,
CLIENT_ID,
SUB_NAME
)
)
Then we could have the unique CONSTRAINT name as PK_ACTIVEMQ_ACKS.
> 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.
[jira] Commented: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Kaiqin Sun (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12983706#action_12983706 ]
Kaiqin Sun commented on AMQ-3075:
---------------------------------
Hi Gary,
You are right. "ACTIVEMQ_ACKS" has a composite primary key and the contraint has the form like this: PK__ACTIVEMQ_ACKS__6DC22B62. This primary key constraint is auto-generated by SQL Server and also everytime the contraint is different.
This script works "ALTER TABLE [dbo].[ACTIVEMQ_ACKS] DROP CONSTRAINT [PK__ACTIVEMQ_ACKS__6DC22B62]" and I have tried "ALTER TABLE [dbo].[ACTIVEMQ_ACKS] DROP CONSTRAINT [PK__ACTIVEMQ_ACKS]" but it doesn't work.
We could use "select name from sysobjects where xtype = 'PK' and parent_obj = object_id('ACTIVEMQ_ACKS')" to get the contraint name. It may need to use dynamic SQL to drop the primary key from SQL Server.
There are also some links may give some hints:
SQL Server: drop table primary key, without knowing its name: http://stackoverflow.com/questions/1587812/sql-server-drop-table-primary-key-without-knowing-its-name
SQL SERVER - How to Drop Primary Key Contraint: http://blog.sqlauthority.com/2009/05/12/sql-server-how-to-drop-primary-key-contraint/
--Kevin
> 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.
[jira] Commented: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Gary Tully (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12983646#action_12983646 ]
Gary Tully commented on AMQ-3075:
---------------------------------
@Kevin, do you know what a sensible default name for the PK in SQLServer is?
As there is no name provided when the constraint is created the name auto-generated by sql server, seems to be of the form "PK..."
> 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.
[jira] Resolved: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Gary Tully (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Gary Tully (JIRA)" <ji...@apache.org>.
[ 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.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.
[jira] Commented: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Kaiqin Sun (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12983715#action_12983715 ]
Kaiqin Sun commented on AMQ-3075:
---------------------------------
Hi Gary,
For SQL Server, could we add the contraint name when creating ACTIVEMQ tables? For example:
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,
CONSTRAINT PK_ACTIVEMQ_ACKS PRIMARY KEY CLUSTERED
(
CONTAINER,
CLIENT_ID,
SUB_NAME
)
)
Then we could have the unique CONSTRAINT name as PK_ACTIVEMQ_ACKS.
> 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.
[jira] Issue Comment Edited: (AMQ-3075) Auto-create database fails
with PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Kaiqin Sun (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12983715#action_12983715 ]
Kaiqin Sun edited comment on AMQ-3075 at 1/19/11 2:45 PM:
----------------------------------------------------------
Hi Gary,
Could we define the primary key constraint name when creating ACTIVEMQ_ACKS? Then we could have a fixed constraint name and this name could be used in ALTER SQL.
For instance, when creating the table we use the following SQL:
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,
CONSTRAINT PK_ACTIVEMQ_ACKS PRIMARY KEY
(
CONTAINER,
CLIENT_ID,
SUB_NAME
)
)
We could have the unique CONSTRAINT name as PK_ACTIVEMQ_ACKS. Then we could drop the constraint like this:ALTER TABLE ACTIVEMQ_ACKS DROP CONSTRAINT PK_ACTIVEMQ_ACKS.
This solution works for SQL server and could work for Oracle as well. I am not sure if this could work for other DBs.
It seems for MySQL we should use this SQL to drop primary key.
ALTER TABLE ACTIVEMQ_ACKS DROP PRIMARY KEY
--Kevin
was (Author: kaiqinsun):
Hi Gary,
Could we define the primary key constraint name when creating ACTIVEMQ_ACKS? Then we could have a fixed constraint name and this name could be used in ALTER SQL.
For instance, when creating the table we use the following SQL:
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,
CONSTRAINT PK_ACTIVEMQ_ACKS PRIMARY KEY
(
CONTAINER,
CLIENT_ID,
SUB_NAME
)
)
We could have the unique CONSTRAINT name as PK_ACTIVEMQ_ACKS.
Then we could drop the constraint like this:
ALTER TABLE ACTIVEMQ_ACKS DROP CONSTRAINT PK_ACTIVEMQ_ACKS
This solution works for SQL server and could work for Oracle as well. I am not sure if this could work for other DBs.
--Kevin
> 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.
[jira] Commented: (AMQ-3075) Auto-create database fails with
PostgreSQL (Error in SQL: 'drop primary key')
Posted by "Ned Wolpert (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/AMQ-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12971290#action_12971290 ]
Ned Wolpert commented on AMQ-3075:
----------------------------------
I'll check out the update and double-check that it solves the issue. I don't know if the alter table statement is thee same for every db AMQ supports, but I can test out psql with what's in there.
> 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.