I've been lurking on this list for a while now, but I have a question now which I haven't been able to answer elsewhere.
I have been trying to use Postgres version 7.3 as the default data source for JBoss version 3.2.1. I'm not the first person to do this I know.
I removed the original hsqldb-ds.xml data source and replaced it with a postgres-ds.xml defining a local-tx-datasource named DefaultDS.
I also edited login-config.xml, standardjbosscmp-jdbc.xml and standardjaws.xml where it seemed appropriate - I followed the recent thread on the PersistenceManager for a lot of this.
All of this seemed to work ok, but I was getting a SQL error and exception arising in org.jboss.mq.pm.jdbc2.PersistenceManager.
2003-10-06 18:55:09,217 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver not yet registered for url: jdbc:postgresql://localhost:5432/jboss
2003-10-06 18:55:09,227 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver already registered for url: jdbc:postgresql://localhost:5432/jboss
2003-10-06 18:55:09,757 DEBUG [org.jboss.mq.pm.jdbc2.PersistenceManager] Could not create table with SQL: CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) ), got : java.sql.SQLException: ERROR: Type "object" does not exist
2003-10-06 18:55:09,783 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.parentTraceEnabled=true
2003-10-06 18:55:09,787 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.nestedTraceEnabled=false
2003-10-06 18:55:09,787 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.detectDuplicateNesting=true
2003-10-06 18:55:09,788 WARN [org.jboss.resource.adapter.jdbc.WrappedConnection] Closing a statement you left open, please do your own housekeeping
2003-10-06 18:55:09,807 ERROR [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting failed
org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (java.sql.SQLException: ERROR: Relation "jms_messages" does not exist
)
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:276)
at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1299)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
at $Proxy11.start(Unknown Source)
...
and so on.
I had a bit of hunt around and decided that the configuration file I missed was jbossmq-service.xml in ${JBOSS_HOME}/server/all/deploy/jms/.
I thought the particular issue was the attribute element in the PersistenceManager mbean element listed below.
<!--
| The jdbc2 PersistenceManager is the new improved JDBC implementation.
| This implementation allows you to control how messages are stored in
| the database.
|
| Use this PM if you want the reliablity a relational database can offer
| you. The default configuration is known to work with hsqldb, other databases
| will requrie teaking of the SqlProperties.
-->
<mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
name="jboss.mq:service=PersistenceManager">
<depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
<depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=DefaultDS</depends>
<attribute name="SqlProperties">
BLOB_TYPE=OBJECT_BLOB
INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=?
DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
MESSAGEBLOB OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )
CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )
</attribute>
</mbean>
It doesn't seem to be very well documented, but I had a look in the source code for the PersistenceManager class and decided the key attribute was "BLOB_TYPE" which could be one of "OBJECT_BLOB", "BYTES_BLOB", "BINARYSTREAM_BLOB" or "BLOB_BLOB". I tried each of them in turn, but it didn't actually seem to make the slightest bit of difference, I got the same error message anyway. It occurs to me that the configuration could be there in the code but that noones bothered to make the options different yet.
This is an area where I believe postgres differs from ANSI SQL. I think the data type required in postgres is probably BYTEA instead of OBJECT.
Of course it's also possible I'm following a red herring here.
Has anyone run into this before? Am I on the right track or is the problem somewhere else? Do I just need to upgrade to a later version of JBoss?
------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
