11 Replies Latest reply on Sep 30, 2003 4:44 AM by Stephane Nicoll

    postgres problem with JMS persistent messages

    Raphael M. Grochtmann Newbie

      Hi,

      i'm currently migrating from JBoss 3.0.8 to 3.2RC2 and I'm
      experiencing a strange postgres error:

      When starting JBoss the 1'st time it will create two tables
      (jms_messages, jms_transactions) in my postgres database
      with no problems.

      When staring JBoss the 2'nd time it tries to create those two
      tables again but fails. It reports:

      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (No results were returned by the query.)

      And in my postgres log I find:
      ERROR: Relation 'jms_messages' already exists
      and succeeding messages:
      NOTICE: current transaction is aborted, queries ignored until end of transaction block

      This leads to the error in JBoss-JMS when trying to recover
      JMS messages on startup. May be it's all done in one transaction
      which is then rolled back alltogether?

      Cheers,
      Raphael

        • 1. Re: postgres problem with JMS persistent messages
          Raphael M. Grochtmann Newbie

          Ok, I don't kown if that's a JBoss bug but here's what I did to
          solve it:

          1'st time I start JBoss I let it create the two JMS tables.
          Then I dump the database.
          Then I reconfiugre JBoss to NOT create the tables on startup.

          When setting up new JBoss instances I use the dump to create
          the tables and configure them to NOT create the tables on startup.

          May be this whole thing is only behaving bad together with
          postgres (because postgres will abort the whole transaction
          if it fails to create the already existing tables and thus fails
          to recover the old messages).

          Regards,
          Raphael

          • 2. Re: postgres problem with JMS persistent messages
            Adrian Brock Master

            Is this really the problem?
            If you can confirm it I will modify jboss to
            create the tables in a different transaction to the
            recovery.

            Regards,
            Adrian

            • 3. Re: postgres problem with JMS persistent messages
              Raphael M. Grochtmann Newbie

              Hi Adrian,

              I've put together a zip file with:
              [AppServer-xc333][all-all]raphael@rigel:~/AppServer/xc333 $ jar tfv JBoss.jar
              JBossTomcat/VERSION.txt
              JBossTomcat/server/default/log/
              JBossTomcat/server/default/log/stdout.log
              JBossTomcat/server/default/log/boot.log
              JBossTomcat/server/default/log/server.log
              JBossTomcat/server/default/log/localhost_access2003-08-14.log
              JBossTomcat/server/default/deploy/jms/jbossmq-service.xml
              JBossTomcat/server/default/deploy/postgres-ds.xml
              JBossTomcat/server/default/conf/standardjbosscmp-jdbc.xml
              JBossTomcat/server/default/conf/standardjaws.xml
              JBossTomcat/server/default/conf/jboss-service.xml
              JBossTomcat/server/default/deploy/jbossweb-tomcat41.sar/META-INF/jboss-service.xml
              JBossTomcat/server/default/conf/log4j.xml
              JBossTomcat/server/default/conf/standardjboss.xml


              Here's what I did:
              1. create new copy of JBoss (named xc333)
              2. create postgres database (named xc333)
              without the two jms tables.
              3. configure JBoss to use this postgres database
              4. START 1'st TIME
              5. banner neustart >> {logfiles}
              6. START 2'nd TIME

              Please let me know if you need more files from this test JBoss instance.


              Cheers
              Raphael

              • 4. Re: postgres problem with JMS persistent messages
                Raphael M. Grochtmann Newbie

                here's the postgres log as well...

                Cheers
                Raphael

                • 5. Re: ANOTHER postgres problem with JMS persistent messages
                  Raphael M. Grochtmann Newbie

                  Now I tried sending the first message over JMS with
                  database persistence on postgres...

                  It seems that it's not enough to create the database and
                  the two JMS tables:


                  It failed with the following exception in JBoss (starting
                  with the creation of a temporary queue for the client):

                  [Thu Aug 14 17:14:39.837 CEST 2003] [WARN ] [org.jboss.mq.security.SecurityManager] No SecurityMetadadata was available for JMS_TQ3 adding default security conf
                  [Thu Aug 14 17:14:42.064 CEST 2003] [WARN ] [org.jboss.mq.il.oil.OILServerILService] Client request resulted in a server exception:
                  org.jboss.mq.SpyJMSException: Could not store message: 2 msg=2 hard NOT_STORED PERSISTENT queue=QUEUE.MessageProcessor priority=4 hashCode=33372731; - nested throwable: (The table for org.jboss.mq.SpyObjectMessage is not in the database. Contact the DBA, as the database is in an inconsistent state.)
                  at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:705)
                  at org.jboss.mq.server.PersistentQueue.addMessage(PersistentQueue.java:39)
                  at org.jboss.mq.server.JMSQueue.addMessage(JMSQueue.java:133)
                  at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:405)
                  at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:385)
                  at org.jboss.mq.server.JMSServerInterceptorSupport.addMessage(JMSServerInterceptorSupport.java:135)
                  at org.jboss.mq.security.ServerSecurityInterceptor.addMessage(ServerSecurityInterceptor.java:162)
                  at org.jboss.mq.server.TracingInterceptor.addMessage(TracingInterceptor.java:234)
                  at org.jboss.mq.server.JMSServerInvoker.addMessage(JMSServerInvoker.java:137)
                  at org.jboss.mq.il.oil.OILServerILService$Client.run(OILServerILService.java:248)
                  at java.lang.Thread.run(Thread.java:536)
                  Caused by: The table for org.jboss.mq.SpyObjectMessage is not in the database. Contact the DBA, as the database is in an inconsistent state.
                  at org.postgresql.Connection.storeObject(Unknown Source)
                  at org.postgresql.jdbc2.PreparedStatement.setObject(Unknown Source)
                  at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:625)
                  at org.jboss.mq.pm.jdbc2.PersistenceManager.setBlob(PersistenceManager.java:798)
                  at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:735)
                  at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:690)
                  ... 10 more


                  Here's what Postgres gives me in it's log:

                  DEBUG: StartTransactionCommand
                  DEBUG: query: begin;
                  DEBUG: ProcessUtility: begin;
                  DEBUG: CommitTransactionCommand
                  DEBUG: StartTransactionCommand
                  DEBUG: query: select typname from pg_type,pg_class where typname=relname and typname='org_jboss_mq_spyobjectmessage'
                  DEBUG: ProcessQuery
                  DEBUG: CommitTransactionCommand
                  DEBUG: StartTransactionCommand
                  DEBUG: query: rollback; begin;
                  DEBUG: ProcessUtility: rollback; begin;
                  DEBUG: CommitTransactionCommand
                  DEBUG: StartTransactionCommand
                  DEBUG: ProcessUtility: rollback; begin;
                  DEBUG: CommitTransactionCommand
                  DEBUG: StartTransactionCommand
                  DEBUG: query: end
                  DEBUG: ProcessUtility: end
                  DEBUG: CommitTransactionCommand
                  DEBUG: StartTransactionCommand
                  DEBUG: query: show xactisolevel
                  DEBUG: ProcessUtility: show xactisolevel
                  NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
                  DEBUG: CommitTransactionCommand



                  Regards,
                  Raphael

                  • 6. Re: ANOTHER postgres problem with JMS persistent messages
                    Adrian Brock Master

                    That sounds like your table is misdefined. It should
                    be saving the messages as a blob, it seems to think
                    it is a postgres class?

                    Regards,
                    Adrian

                    • 7. Re: postgres problem with JMS persistent messages
                      teppic Newbie

                      I was seeing this too until I updated my jdbc driver from from pg73b1jdbc2.jar to pg73jdbc3.jar.

                      • 8. Re: postgres problem with JMS persistent messages
                        teppic Newbie

                        Having the create and the recover in separate txs would be a good idea tho'. There's no reason for them to be together.

                        The recover will only work after the create fails if the DB suspends the tx during the create (which will be the case for most databases, but obviously at least one driver doesn't handle it correctly).

                        • 9. Re: postgres problem with JMS persistent messages
                          Stephane Nicoll Master

                          If you think It's relevant, open a feature request at sourceforge. Jboss team is not supposed to read each and every threads for ideas/suggestions.

                          Regards,

                          Stephane

                          • 10. Re: postgres problem with JMS persistent messages
                            Adrian Brock Master

                            But I read this one. ;-)

                            This is done.

                            Regards,
                            Adrian