0 Replies Latest reply on Jul 14, 2006 9:07 PM by Smith Aron

    startup error PSQLException: ERROR: syntax error at or near

    Smith Aron Newbie


      Hi I am stuck on this issue - my hsqldb-jdbc2-service.xml is below. Unmodified from distributed with 4.0.4


      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Messag
      e recovery may not be accurate; - nested throwable: (org.postgresql.util.PSQLExc
      eption: ERROR: syntax error at or near "TXID")
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(Pers
      istenceManager.java:442)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceMana
      ger.java:1571)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanS
      upport.java:289)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMB
      eanSupport.java:245)
      at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
      sorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
      er.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
      java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceControl
      ler.java:978)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:417)
      at org.jboss.system.ServiceController.start(ServiceController.java:435)
      at org.jboss.system.ServiceController.start(ServiceController.java:435)
      at org.jboss.system.ServiceController.start(ServiceController.java:435)
      at org.jboss.system.ServiceController.start(ServiceController.java:435)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
      sorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
      er.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
      java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
      at $Proxy4.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
      java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
      sorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
      er.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
      or.java:133)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
      BeanOperationInterceptor.java:142)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
      java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
      at $Proxy25.start(Unknown Source)
      at org.jboss.deployment.XSLSubDeployer.start(XSLSubDeployer.java:197)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1007)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:808)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:771)
      at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
      sorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
      er.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
      or.java:133)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
      BeanOperationInterceptor.java:142)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
      java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
      at $Proxy6.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymen
      tScanner.java:421)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentS
      canner.java:634)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.
      doScan(AbstractDeploymentScanner.java:263)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(A
      bstractDeploymentScanner.java:336)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanS
      upport.java:289)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMB
      eanSupport.java:245)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
      java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
      sorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
      er.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
      java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceControl
      ler.java:978)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:417)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
      java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
      sorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
      er.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
      java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
      at $Proxy4.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1007)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:808)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:771)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:755)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
      java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
      sorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
      er.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
      or.java:133)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
      BeanOperationInterceptor.java:142)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
      java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
      at $Proxy5.deploy(Unknown Source)
      at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:482)
      at org.jboss.system.server.ServerImpl.start(ServerImpl.java:362)
      at org.jboss.Main.boot(Main.java:200)
      at org.jboss.Main$1.run(Main.java:464)
      at java.lang.Thread.run(Thread.java:595)
      Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "TX
      ID"
      at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
      ecutorImpl.java:1525)
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
      Impl.java:1309)
      at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
      va:188)
      at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
      ement.java:452)
      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
      Jdbc2Statement.java:354)
      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc
      2Statement.java:258)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery
      (WrappedPreparedStatement.java:236)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(Pers
      istenceManager.java:403)
      ... 113 more
      19:45:50,796 INFO [Ejb3Deployment] EJB3 deployment time took: 515
      19:45:50,843 INFO [JmxKernelAbstraction] installing MBean: jboss.j2ee:jar=patie
      ntis.ejb3,name=PatientBean,service=EJB3 with dependencies:
      19:45:51,046 INFO [EJBContainer] STARTED EJB: com.patientis.ejb.patient.Patient
      Bean ejbName: PatientBean
      19:45:51,125 INFO [EJB3Deployer] Deployed: file:/C:/Program Files/jboss_4_0_4/s
      erver/default/deploy/patientis.ejb3
      19:45:51,218 INFO [TomcatDeployer] deploy, ctxPath=/jmx-console, warUrl=.../dep
      loy/jmx-console.war/
      19:45:51,375 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

      --- MBeans waiting for other MBeans ---
      ObjectName: jboss.mq:service=PersistenceManager
      State: FAILED
      Reason: org.jboss.mq.SpyJMSException: Could not resolve uncommited transaction
      s. Message recovery may not be accurate; - nested throwable: (org.postgresql.ut
      il.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 transaction
      s. Message recovery may not be accurate; - nested throwable: (org.postgresql.ut
      il.PSQLException: ERROR: syntax error at or near "TXID")
      I Depend On:
      jboss.jca:service=DataSourceBinding,name=DefaultDS
      Depends On Me:
      jboss.mq:service=DestinationManager




      <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS

      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) TXID FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES)
      DELETE_ALL_TX = DELETE FROM JMS_TRANSACTIONS
      SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
      SELECT_MESSAGE_KEYS_IN_DEST = SELECT MESSAGEID 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 TXOP=? AND JMS_MESSAGES.TXID IN (SELECT TXID FROM JMS_TRANSACTIONS)
      DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
      DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
      DELETE_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
      DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      CREATE_MESSAGE_TABLE = 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) )
      CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX JMS_MESSAGES_TXOP_TXID ON JMS_MESSAGES (TXOP, TXID)
      CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX JMS_MESSAGES_DESTINATION ON JMS_MESSAGES (DESTINATION)
      CREATE_TX_TABLE = CREATE CACHED TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )
      CREATE_TABLES_ON_STARTUP = TRUE

      <!-- Uncomment to override the transaction timeout for recovery per queue/subscription, in seconds -->
      <!--attribute name="RecoveryTimeout">0</attribute-->
      <!-- The number of blobs to load at once during message recovery -->
      0