8 Replies Latest reply on Sep 2, 2007 9:16 AM by tarch82

    ERROR: relation "timers" does not exist PostgreSQL

    tarch82

      Hi all,
      I've tried to install postgreSQL as the DefaultDS in JBoss but I get an error:
      "ERROR: relation "timers" does not exist PostgreSQL" and previously another error: "[GeneralPurposeDatabasePersistencePlugin] Cannot create timer table". I've tried to search on the forum but there is no post related with my problem. I've tried to search the SQL to create this phantomatic timers table but the only code is inherent to timers regard the HSQL datasource. So someone have the code for Postgres???

      Bye and Thank you.

        • 1. Re: ERROR: relation
          dimitris

          You need to show the exact stacktrace.

          • 2. Re: ERROR: relation
            tarch82

            I posted it in another topic but the only response that I recieve doesn't regard the installation of JBoss as default datasource.

            Bye! =)

            This is the log of JBoss, I shortened some StackTrace to reduce the size of the message but if you want or if you need I can post it entirely.

            22:06:53,667 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=ConnectionFactoryBinding,name=JmsXA' to JNDI name 'java:JmsXA'
            22:06:54,684 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=DefaultDS' to JNDI name 'java:DefaultDS'
            22:06:55,184 ERROR [GeneralPurposeDatabasePersistencePlugin] Cannot create timer table
            java.lang.IllegalStateException: Cannot obtain type mapping from: jboss.jdbc:datasource=DefaultDS,service=metadata
            at org.jboss.ejb.txtimer.GeneralPurposeDatabasePersistencePlugin.createTableIfNotExists(GeneralPurposeDatabasePersistencePlugin.java:97)
            at org.jboss.ejb.txtimer.DatabasePersistencePolicy.startService(DatabasePersistencePolicy.java:96)
            ....
            at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
            at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
            at $Proxy5.deploy(Unknown Source)
            at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:453)
            at org.jboss.system.server.ServerImpl.start(ServerImpl.java:330)
            at org.jboss.Main.boot(Main.java:187)
            at org.jboss.Main$1.run(Main.java:438)
            at java.lang.Thread.run(Thread.java:595)
            22:06:55,196 WARN [ServiceController] Problem starting service jboss.ejb:service=EJBTimerService,persistencePolicy=database
            org.postgresql.util.PSQLException: ERROR: relation "timers" does not exist
            at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
            at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
            at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
            ...
            at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
            at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
            at $Proxy5.deploy(Unknown Source)
            at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:453)
            at org.jboss.system.server.ServerImpl.start(ServerImpl.java:330)
            at org.jboss.Main.boot(Main.java:187)
            at org.jboss.Main$1.run(Main.java:438)
            at java.lang.Thread.run(Thread.java:595)
            22:06:55,289 WARN [ServiceController] Problem starting service jboss.mq:service=PersistenceManager
            org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (org.postgresql.util.PSQLException: ERROR: syntax error at or near "TXID")
            at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:416)
            at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1499)
            at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:274)
            ...
            at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
            at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
            at $Proxy5.deploy(Unknown Source)
            at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:453)
            at org.jboss.system.server.ServerImpl.start(ServerImpl.java:330)
            at org.jboss.Main.boot(Main.java:187)
            at org.jboss.Main$1.run(Main.java:438)
            at java.lang.Thread.run(Thread.java:595)
            Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "TXID"
            at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
            at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
            at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
            at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
            at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
            at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:250)
            at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:211)
            at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:384)
            ... 111 more
            22:06:55,348 INFO [TomcatDeployer] deploy, ctxPath=/jmx-console, warUrl=.../deploy/jmx-console.war/
            22:06:55,698 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

            --- MBeans waiting for other MBeans ---
            ObjectName: jboss.ejb:service=EJBTimerService,persistencePolicy=database
            State: FAILED
            Reason: org.postgresql.util.PSQLException: ERROR: relation "timers" does not exist
            I Depend On:
            jboss.jca:service=DataSourceBinding,name=DefaultDS

            ObjectName: jboss.mq:service=PersistenceManager
            State: FAILED
            Reason: org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (org.postgresql.util.PSQLException: ERROR: syntax error at or near "TXID")
            I Depend On:
            jboss.jca:service=DataSourceBinding,name=DefaultDS
            Depends On Me:
            jboss.mq:service=DestinationManager

            --- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
            ObjectName: jboss.mq:service=PersistenceManager
            State: FAILED
            Reason: org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (org.postgresql.util.PSQLException: ERROR: syntax error at or near "TXID")
            I Depend On:
            jboss.jca:service=DataSourceBinding,name=DefaultDS
            Depends On Me:
            jboss.mq:service=DestinationManager

            ObjectName: jboss.ejb:service=EJBTimerService,persistencePolicy=database
            State: FAILED
            Reason: org.postgresql.util.PSQLException: ERROR: relation "timers" does not exist
            I Depend On:
            jboss.jca:service=DataSourceBinding,name=DefaultDS


            22:06:57,501 INFO [Http11Protocol] Starting Coyote HTTP/1.1 on http-0.0.0.0-8080
            22:06:57,631 INFO [ChannelSocket] JK: ajp13 listening on /0.0.0.0:8009
            22:06:57,637 INFO [JkMain] Jk running ID=0 time=0/18 config=null
            22:06:57,643 INFO [Server] JBoss (MX MicroKernel) [4.0.3SP1 (build: CVSTag=JBoss_4_0_3_SP1 date=200510231054)] Started in 40s:142ms

            • 3. Re: ERROR: relation
              tarch82

              Someone has the SQL code??? Please...

              Bye! =)

              • 4. Re: ERROR: relation
                phon

                we had te same problem with some earlier version of JBoss we're not using anymore, my brother fixed the error by adjusting some query in some xml. But as we hadn't any use for these timers until now, we just ignored the error.

                We'll look into it again now and i'll post the solution.

                • 5. Re: ERROR: relation
                  tarch82

                  Thank you.

                  • 6. Re: ERROR: relation
                    tarch82

                    So have you found the solution??? Sorry for the insistence but I need it.

                    Bye! =)

                    • 7. Re: ERROR: relation
                      robhadfield

                      Hi, most probably your have an hsqldb-jdbc2-state-service.xml file lying around in or beneath your deploy/ directory (probably deploy/jms). The SQL code it is failing on is the CREATE CACHED TABLE - so far as I know postgres does not support the CACHED keyword in this context.
                      In my server.log (you should have the same indicator to the smoking gun here) :

                      2007-09-02 10:51:11,622 DEBUG [org.jboss.mq.pm.jdbc2.PersistenceManager] Could not create table with SQL: CREATE CACHED TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )
                      org.postgresql.util.PSQLException: ERROR: syntax error at or near "CACHED" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
                      ...<snip>
                      


                      Don't ask me why this is different to the INFO log output which reports

                      10:51:11,634 WARN [ServiceController] Problem starting service jboss.mq:service=PersistenceManager
                      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (org.postgresql.util.PSQLException: ERROR: syntax error at or near "TXID")

                      Kind regards,
                      ROb

                      • 8. Re: ERROR: relation
                        tarch82

                        Hi,
                        thanks for the reply! Due to the time that have been passed I haven't anymore the necessity of this but maybe it can be helpful for someone else!

                        Bye! ^^