You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by Stanislaw Kogut <sk...@sistyma.net> on 2010/12/13 19:46:05 UTC
Can't start ActiveMQ 5.4.2 with PostgreSQL 8.4 as persistence backend
Hi.
Here is my activemq.xml part for persistence configuration:
<persistenceAdapter>
<jdbcPersistenceAdapter
dataDirectory="${activemq.base}/data" dataSource="#pgsql-ds" />
</persistenceAdapter>
... </broker>
<bean id="pgsql-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>
Then I create database 'activemq', user 'activemq' with password 'activemq'
and granting it access to database in posgress.
But when I'm starting activemq there is an issue with SQL statements:
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)
........
PostgreSQL documentation confirms "DROP PRIMARY KEY" is wrong statement. It
has 'ALTER TABLE ... DROP CONSTRAINT "name"' statement instead.
Looks like after that whole transaction is rolled back, so tables are not
created at all. Is there any success in using PostgreSQL for ActiveMQ?
--
Regards,
Stanislaw Kogut
Sistyma LLC
Re: Can't start ActiveMQ 5.4.2 with PostgreSQL 8.4 as persistence backend
Posted by Bruce Snyder <br...@gmail.com>.
On Mon, Dec 13, 2010 at 11:46 AM, Stanislaw Kogut <sk...@sistyma.net> wrote:
> Hi.
>
>
> Here is my activemq.xml part for persistence configuration:
>
> <persistenceAdapter>
> <jdbcPersistenceAdapter
> dataDirectory="${activemq.base}/data" dataSource="#pgsql-ds" />
> </persistenceAdapter>
> ... </broker>
>
> <bean id="pgsql-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>
>
>
> Then I create database 'activemq', user 'activemq' with password 'activemq'
> and granting it access to database in posgress.
> But when I'm starting activemq there is an issue with SQL statements:
>
> 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)
> ........
>
> PostgreSQL documentation confirms "DROP PRIMARY KEY" is wrong statement. It
> has 'ALTER TABLE ... DROP CONSTRAINT "name"' statement instead.
> Looks like after that whole transaction is rolled back, so tables are not
> created at all. Is there any success in using PostgreSQL for ActiveMQ?
There was an issue opened for this last week:
https://issues.apache.org/jira/browse/AMQ-3075
Bruce
--
perl -e 'print unpack("u30","D0G)U8V4\@4VYY9&5R\"F)R=6-E+G-N>61E<D\!G;6%I;\"YC;VT*"
);'
ActiveMQ in Action: http://bit.ly/2je6cQ
Blog: http://bruceblog.org/
Twitter: http://twitter.com/brucesnyder
Re: Can't start ActiveMQ 5.4.2 with PostgreSQL 8.4 as persistence backend
Posted by Gary Tully <ga...@gmail.com>.
As a workaround, you can provide the string value for the entire
createSchemaStatements via jdbcPersistenceAdapter config statements
element. You would take inspiration from the existing string
createSchemaStatements in
http://svn.apache.org/repos/asf/activemq/trunk/activemq-core/src/main/java/org/apache/activemq/store/jdbc/Statements.java
You need to replace the dynamic elements using the defaults values,
noting that PostgreSQL uses BYTEA as the binaryDataType.
<statements>
<statements createSchemaStatements ="..."/>
</statements>
more info: http://activemq.apache.org/jdbc-support.html
Wonder if there is a general form of that alter that will work across
the board. The current was validated on oracle, mysql and derby.
Can u open a jira issue to track this?
On 13 December 2010 18:46, Stanislaw Kogut <sk...@sistyma.net> wrote:
> Hi.
>
>
> Here is my activemq.xml part for persistence configuration:
>
> <persistenceAdapter>
> <jdbcPersistenceAdapter
> dataDirectory="${activemq.base}/data" dataSource="#pgsql-ds" />
> </persistenceAdapter>
> ... </broker>
>
> <bean id="pgsql-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>
>
>
> Then I create database 'activemq', user 'activemq' with password 'activemq'
> and granting it access to database in posgress.
> But when I'm starting activemq there is an issue with SQL statements:
>
> 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)
> ........
>
> PostgreSQL documentation confirms "DROP PRIMARY KEY" is wrong statement. It
> has 'ALTER TABLE ... DROP CONSTRAINT "name"' statement instead.
> Looks like after that whole transaction is rolled back, so tables are not
> created at all. Is there any success in using PostgreSQL for ActiveMQ?
>
> --
> Regards,
> Stanislaw Kogut
> Sistyma LLC
>
--
http://blog.garytully.com
http://fusesource.com