3 Replies Latest reply on Jun 2, 2003 10:10 AM by stephanenicoll

    Postgres problem, could not resolve uncommited transactions.

    jbrosenberg
    jbrosenberg May 30, 2003 2:39 PM

      Ok,

      I'm getting thsi now when trying to startup with jms....It was all working ok against Oracle, but something is wrong with Postgres config....

      On Oracle, it automatically created the tables
      it needs, but on postgres that isn't happening,
      and so it looks like it's trying to read tables that
      haven't been created yet....Ideas?

      I have 2 identical db's on the same postgres
      server....One for ejb's and one for jms...

      The ejb's deploy and talk to the db just fine...

      But jms fails to initialize....

      I've determined a have Postgres version 7.2.1...

      I wonder if I have some sort of driver mismatch?

      Or do I need to have a custom Postgres setting
      for the SqlProperties in jbossmq-service.xml, like
      I needed for Oracle (which was undocumented, only found out about it via this forum)....I have the advanced documentation, but jms config info is very light.....

      14:21:45,502 INFO [TxConnectionManager] Started
      14:21:45,522 INFO [JMSProviderLoader] Starting
      14:21:45,552 INFO [JMSProviderLoader] Bound adapter to java:/DefaultJMSProvider
      14:21:45,552 INFO [JMSProviderLoader] Started
      14:21:45,552 INFO [ServerSessionPoolLoader] Starting
      14:21:45,562 INFO [ServerSessionPoolLoader] pool factory StdJMSPool bound to java:/StdJMSPool
      14:21:45,562 INFO [ServerSessionPoolLoader] Started
      14:21:45,632 INFO [MessageCache] Starting
      14:21:45,632 INFO [MessageCache] Started
      14:21:45,632 INFO [PersistenceManager] Starting
      14:21:45,752 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
      14:21:45,762 ERROR [PersistenceManager] Starting failed
      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (No resu
      lts were returned by the query.)
      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.GeneratedMethodAccessor33.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)

      • 4274 Views
      • Tags:
        • 1. Re: Postgres problem, could not resolve uncommited transacti
          jbrosenberg
          jbrosenberg May 30, 2003 2:54 PM (in response to jbrosenberg)

          Ok, I see this on the console:

          2003-05-30 14:21:45,712 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: Unable to locate type name 'object' in catalog

          2003-05-30 14:21:45,742 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.parentTraceEnabled=true


          Do I need a different set of SqlProperties for Postgres?

            • Actions
          • 2. Re: Postgres problem, could not resolve uncommited transacti
            jbrosenberg
            jbrosenberg May 30, 2003 3:18 PM (in response to jbrosenberg)

            Alright, got it to work, saw an alternate SqlProperties on another post, need to use type 'BYTEA' instead
            of type 'OBJECT' for the MESSSAGEBLOB field....

            I'm amazed that it isn't more documented anywhere...

            Here's what my successful SqlProperties look like now:

            BLOB_TYPE=BYTES_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=?
            DELETE_ALL_MESSAGE_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID=?
            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 BYTEA, PRIMARY KEY (MESSAGEID, DESTINATION))
            CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER)
            CREATE_TABLES_ON_STARTUP=true

              • Actions
            • 3. Re: Postgres problem, could not resolve uncommited transacti
              stephanenicoll Jun 2, 2003 10:10 AM (in response to jbrosenberg)

              exactly ,)

              But still, you have this

              14:21:45,752 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping

              I have this too with Postgres 7.3.2 and Jboss 3.2.1 and I was wondering if you know what's behind this?

              Cheers

                • Actions