5 Replies Latest reply on Nov 3, 2009 3:30 PM by Peter Johnson

    sybase-persistence-service.xml

    Bob Damato Newbie

      I'm trying to switch from hypersonic to sybase. I've replaced the hsqldb-ds.xml with a copy of the sybase-ds.xml configured as my DefaultDS (it's working fine). I've also replaced the hsqldb-persistence-service.xml with a copy of sybase-persistence-service.xml. For some reason, when the server is booting, it doesn't create the JBM_ID_CACHE table for me and I get this error message (it's the first exception in my server.log).

      10:22:14,629 ERROR [ExceptionUtil] ServerPeer[0] startService
      com.sybase.jdbc3.jdbc.SybSQLException: JBM_ID_CACHE not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
      


      It does manage to create these tables:
      HILOSEQUENCES
      JBM_DUAL
      JBM_USER
      TIMERS

      I'm using Sybase ASE 15, JBoss 5.1.0.GA on solaris using JDK1.6.0_14 64-bit.

      Here are my two files:
      <?xml version="1.0" encoding="UTF-8"?>
      
      <!-- ===================================================================== -->
      <!-- -->
      <!-- JBoss Server Configuration -->
      <!-- -->
      <!-- New ConnectionManager setup for Sybase ASE/ASA jConnect driver -->
      <!-- Thanks to Marc Zampetti <zampetti@aol.net> -->
      <!-- This is a minimal example. See the generic example for other stuff -->
      <!-- ===================================================================== -->
      
      <!-- See http://www.jboss.org/community/wiki/Multiple1PC for information about local-tx-datasource -->
      <!-- $Id: sybase-ds.xml 88948 2009-05-15 14:09:08Z jesper.pedersen $ -->
      
      
      <datasources>
       <local-tx-datasource>
       <jndi-name>DefaultDS</jndi-name>
       <!-- Sybase jConnect URL for the database.
       NOTE: The hostname and port are made up values. The optional
       database name is provided, as well as some additinal Driver
       parameters.
       -->
       <connection-url>jdbc:sybase:Tds:XXXXX:4100/XXXX?JCONNECT_VERSION=6</connection-url>
       <driver-class>com.sybase.jdbc3.jdbc.SybDataSource</driver-class>
       <user-name>XXX</user-name>
       <password>XXXXX</password>
      
       <!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
       <min-pool-size>5</min-pool-size>
       <!-- The maximum connections in a pool/sub-pool -->
       <max-pool-size>50</max-pool-size>
       <!-- The time before an unused connection is destroyed -->
       <idle-timeout-minutes>5</idle-timeout-minutes>
      
       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.SybaseExceptionSorter</exception-sorter-class-name>
       <!-- sql to call when connection is created -->
       <new-connection-sql>select 1</new-connection-sql>
      
       <!-- sql to call on an existing pooled connection when it is obtained from pool -->
       <check-valid-connection-sql>select 1</check-valid-connection-sql>
      
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
       <metadata>
       <type-mapping>Sybase</type-mapping>
       </metadata>
       </local-tx-datasource>
      </datasources>
      
      


      <?xml version="1.0" encoding="UTF-8"?>
      
      <!--
       Sybase persistence deployment descriptor.
      
       Tested with Sybase Adaptive Server Enterprise 12.5.3
      
       $Id$
       -->
      
      <server>
      
       <!-- Persistence Manager MBean configuration
       ======================================== -->
      
       <mbean code="org.jboss.messaging.core.jmx.JDBCPersistenceManagerService"
       name="jboss.messaging:service=PersistenceManager"
       xmbean-dd="xmdesc/JDBCPersistenceManager-xmbean.xml">
      
       <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
      
       <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
      
       <!-- The datasource to use for the persistence manager -->
      
       <attribute name="DataSource">java:/DefaultDS</attribute>
      
       <!-- If true will attempt to create tables and indexes on every start-up -->
      
       <attribute name="CreateTablesOnStartup">true</attribute>
      
       <!-- If true then we will automatically detect and reject duplicate messages sent during failover -->
      
       <attribute name="DetectDuplicates">true</attribute>
      
       <!-- The size of the id cache to use when detecting duplicate messages -->
      
       <attribute name="IDCacheSize">500</attribute>
      
       <!-- If true then will add a trailing byte to all byte arrays otherwise Sybase truncates them
       Only known to be necessary for Sybase -->
      
       <attribute name="UsingTrailingByte">true</attribute>
      
       <attribute name="SqlProperties"><![CDATA[
       CREATE_DUAL=CREATE TABLE JBM_DUAL (DUAL_DUMMY INTEGER NOT NULL, PRIMARY KEY (DUAL_DUMMY))
       CREATE_MESSAGE_REFERENCE=CREATE TABLE JBM_MSG_REF (MESSAGE_ID DECIMAL(19, 0) NOT NULL, CHANNEL_ID DECIMAL(19, 0) NOT NULL, TRANSACTION_ID DECIMAL(19, 0) NULL, STATE CHAR(1), ORD DECIMAL(19, 0), PAGE_ORD DECIMAL(19, 0) NULL, DELIVERY_COUNT INTEGER, SCHED_DELIVERY DECIMAL(19, 0), PRIMARY KEY(MESSAGE_ID, CHANNEL_ID))
       CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID, STATE)
       CREATE_MESSAGE=CREATE TABLE JBM_MSG (MESSAGE_ID DECIMAL(19, 0) NOT NULL, RELIABLE CHAR(1), EXPIRATION DECIMAL(19, 0), TIMESTAMP DECIMAL(19, 0), PRIORITY TINYINT, TYPE TINYINT, HEADERS IMAGE NULL, PAYLOAD IMAGE NULL, PRIMARY KEY (MESSAGE_ID))
       CREATE_TRANSACTION=CREATE TABLE JBM_TX (NODE_ID INTEGER, TRANSACTION_ID DECIMAL(19, 0) NOT NULL, BRANCH_QUAL VARBINARY(254) NULL, FORMAT_ID INTEGER NULL, GLOBAL_TXID VARBINARY(254) NULL, PRIMARY KEY (TRANSACTION_ID))
       CREATE_COUNTER=CREATE TABLE JBM_COUNTER (NAME VARCHAR(255) NOT NULL, NEXT_ID DECIMAL(19, 0), PRIMARY KEY(NAME))
       CREATE_ID_CACHE=CREATE TABLE JBM_ID_CACHE (NODE_ID INTEGER NOT NULL, CNTR INTEGER NOT NULL, JBM_ID VARCHAR(255), PRIMARY KEY(NODE_ID, CNTR))
       INSERT_DUAL=INSERT INTO JBM_DUAL VALUES (1)
       CHECK_DUAL=SELECT 1 FROM JBM_DUAL
       INSERT_MESSAGE_REF=INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
       DELETE_MESSAGE_REF=DELETE FROM JBM_MSG_REF WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
       UPDATE_MESSAGE_REF=UPDATE JBM_MSG_REF SET TRANSACTION_ID=?, STATE='-' WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
       UPDATE_PAGE_ORDER=UPDATE JBM_MSG_REF SET PAGE_ORD = ? WHERE MESSAGE_ID=? AND CHANNEL_ID=?
       COMMIT_MESSAGE_REF1=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='+'
       COMMIT_MESSAGE_REF2=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='-'
       ROLLBACK_MESSAGE_REF1=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='+'
       ROLLBACK_MESSAGE_REF2=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='-'
       LOAD_PAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, PAGE_ORD, SCHED_DELIVERY FROM JBM_MSG_REF WHERE CHANNEL_ID = ? AND PAGE_ORD BETWEEN ? AND ? ORDER BY PAGE_ORD
       LOAD_UNPAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? AND PAGE_ORD IS NULL ORDER BY ORD
       LOAD_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? ORDER BY ORD
       UPDATE_REFS_NOT_PAGED=UPDATE JBM_MSG_REF SET PAGE_ORD = NULL WHERE PAGE_ORD BETWEEN ? AND ? AND CHANNEL_ID=?
       SELECT_MIN_MAX_PAGE_ORD=SELECT MIN(PAGE_ORD), MAX(PAGE_ORD) FROM JBM_MSG_REF WHERE CHANNEL_ID = ?
       UPDATE_DELIVERY_COUNT=UPDATE JBM_MSG_REF SET DELIVERY_COUNT = ? WHERE MESSAGE_ID = ? AND CHANNEL_ID = ?
       UPDATE_CHANNEL_ID=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ?
       MOVE_REFERENCE=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE MESSAGE_ID = ? AND CHANNEL_ID = ?
       LOAD_MESSAGES=SELECT MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, HEADERS, PAYLOAD, TYPE FROM JBM_MSG
       INSERT_MESSAGE=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
       INSERT_MESSAGE_CONDITIONAL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE) SELECT ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
       UPDATE_MESSAGE_4CONDITIONAL=UPDATE JBM_MSG SET HEADERS=?, PAYLOAD=? WHERE MESSAGE_ID=?
       INSERT_MESSAGE_CONDITIONAL_FULL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
       MESSAGE_ID_COLUMN=MESSAGE_ID
       DELETE_MESSAGE=DELETE FROM JBM_MSG WHERE MESSAGE_ID = ? AND NOT EXISTS (SELECT JBM_MSG_REF.MESSAGE_ID FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = ?)
       INSERT_TRANSACTION=INSERT INTO JBM_TX (NODE_ID, TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID) VALUES(?, ?, ?, ?, ?)
       DELETE_TRANSACTION=DELETE FROM JBM_TX WHERE NODE_ID = ? AND TRANSACTION_ID = ?
       SELECT_PREPARED_TRANSACTIONS=SELECT TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID FROM JBM_TX WHERE NODE_ID = ?
       SELECT_MESSAGE_ID_FOR_REF=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '+' ORDER BY ORD
       SELECT_MESSAGE_ID_FOR_ACK=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '-' ORDER BY ORD
       UPDATE_COUNTER=UPDATE JBM_COUNTER SET NEXT_ID = ? WHERE NAME=?
       SELECT_COUNTER=SELECT NEXT_ID FROM JBM_COUNTER HOLDLOCK WHERE NAME=?
       INSERT_COUNTER=INSERT INTO JBM_COUNTER (NAME, NEXT_ID) VALUES (?, ?)
       SELECT_ALL_CHANNELS=SELECT DISTINCT(CHANNEL_ID) FROM JBM_MSG_REF
       UPDATE_TX=UPDATE JBM_TX SET NODE_ID=? WHERE NODE_ID=?
       UPDATE_ID_IN_CACHE=UPDATE JBM_ID_CACHE SET JBM_ID = ? WHERE NODE_ID = ? AND CNTR = ?
       INSERT_ID_IN_CACHE=INSERT INTO JBM_ID_CACHE (NODE_ID, CNTR, JBM_ID) VALUES (?, ?, ?)
       LOAD_ID_CACHE=SELECT CNTR, JBM_ID FROM JBM_ID_CACHE WHERE NODE_ID = ?
       ]]></attribute>
      
       <!-- The maximum number of parameters to include in a prepared statement -->
      
       <attribute name="MaxParams">500</attribute>
      
       </mbean>
      
       <!-- Messaging Post Office MBean configuration
       ========================================= -->
      
       <mbean code="org.jboss.messaging.core.jmx.MessagingPostOfficeService"
       name="jboss.messaging:service=PostOffice"
       xmbean-dd="xmdesc/MessagingPostOffice-xmbean.xml">
      
       <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer</depends>
      
       <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
      
       <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
      
       <!-- The name of the post office -->
      
       <attribute name="PostOfficeName">JMS post office</attribute>
      
       <!-- The datasource used by the post office to access it's binding information -->
      
       <attribute name="DataSource">java:/DefaultDS</attribute>
      
       <!-- If true will attempt to create tables and indexes on every start-up -->
      
       <attribute name="CreateTablesOnStartup">true</attribute>
      
       <attribute name="SqlProperties"><![CDATA[
      CREATE_POSTOFFICE_TABLE=CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(255), NODE_ID SMALLINT, QUEUE_NAME VARCHAR(255), COND VARCHAR(1023), SELECTOR VARCHAR(1023) NULL, CHANNEL_ID INTEGER, CLSTERED CHAR(1), ALL_NODES CHAR(1), PRIMARY KEY(POSTOFFICE_NAME, NODE_ID, QUEUE_NAME))
      INSERT_BINDING=INSERT INTO JBM_POSTOFFICE (POSTOFFICE_NAME, NODE_ID, QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLSTERED, ALL_NODES) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      DELETE_BINDING=DELETE FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=? AND QUEUE_NAME=?
      LOAD_BINDINGS=SELECT QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLSTERED, ALL_NODES FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=?
       ]]></attribute>
      
       <!-- This post office is non clustered. If you want a clustered post office then set to true -->
      
       <attribute name="Clustered">false</attribute>
      
       <!-- All the remaining properties only have to be specified if the post office is clustered.
       You can safely comment them out if your post office is non clustered -->
      
       <!-- The JGroups group name that the post office will use -->
      
       <attribute name="GroupName">${jboss.messaging.groupname:MessagingPostOffice}</attribute>
      
       <!-- Max time to wait for state to arrive when the post office joins the cluster -->
      
       <attribute name="StateTimeout">30000</attribute>
      
       <!-- Max time to wait for a synchronous call to node members using the MessageDispatcher -->
      
       <attribute name="CastTimeout">30000</attribute>
      
       <!-- Set this to true if you want failover of connections to occur when a node is shut down -->
      
       <attribute name="FailoverOnNodeLeave">false</attribute>
      
       <depends optional-attribute-name="ChannelFactoryName">jboss.jgroups:service=ChannelFactory</depends>
       <attribute name="ControlChannelName">jbm-control</attribute>
       <attribute name="DataChannelName">jbm-data</attribute>
       <attribute name="ChannelPartitionName">${jboss.partition.name:DefaultPartition}-JMS</attribute>
       </mbean>
      
       <!-- Messaging JMS User Manager MBean config
       ======================================= -->
      
       <mbean code="org.jboss.jms.server.plugin.JDBCJMSUserManagerService"
       name="jboss.messaging:service=JMSUserManager"
       xmbean-dd="xmdesc/JMSUserManager-xmbean.xml">
       <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
       <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
       <attribute name="DataSource">java:/DefaultDS</attribute>
       <attribute name="CreateTablesOnStartup">true</attribute>
       <attribute name="SqlProperties"><![CDATA[
      CREATE_USER_TABLE=CREATE TABLE JBM_USER (USER_ID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USER_ID))
      CREATE_ROLE_TABLE=CREATE TABLE JBM_ROLE (ROLE_ID VARCHAR(32) NOT NULL, USER_ID VARCHAR(32) NOT NULL, PRIMARY KEY(USER_ID, ROLE_ID))
      SELECT_PRECONF_CLIENTID=SELECT CLIENTID FROM JBM_USER WHERE USER_ID=?
      POPULATE.TABLES.1 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('guest', 'guest')
      POPULATE.TABLES.2 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('j2ee', 'j2ee')
      POPULATE.TABLES.3 = INSERT INTO JBM_USER (USER_ID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
      POPULATE.TABLES.4 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('nobody', 'nobody')
      POPULATE.TABLES.5 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('dynsub', 'dynsub')
      POPULATE.TABLES.6 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('guest','guest')
      POPULATE.TABLES.7 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('j2ee','guest')
      POPULATE.TABLES.8 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('john','guest')
      POPULATE.TABLES.9 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('subscriber','john')
      POPULATE.TABLES.10 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','john')
      POPULATE.TABLES.11 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','dynsub')
      POPULATE.TABLES.12 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','john')
      POPULATE.TABLES.13 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','dynsub')
      POPULATE.TABLES.14 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('noacc','nobody')
       ]]></attribute>
       </mbean>
      
      </server>
      
      


        • 1. Re: sybase-persistence-service.xml
          Peter Johnson Master

          I don't know Sybase, but it sounds as if the DDL statements to create some of the tables are incorrect. The sybase-persistence-service.xml file contains the DDL statements, try entering them in by hand into Sybase (using a sybase query tool). That should tell you what it wrong with them, so that you can fix them in sybase-persistence-service.xml. Oh, and once you have done that, open a JIRA and attach the patch.

          • 2. Re: sybase-persistence-service.xml
            Bob Damato Newbie

            They work when I enter them directly into Sybase. It just appears that they are never being executed.

            • 3. Re: sybase-persistence-service.xml
              Peter Johnson Master

              I would tell you to look in the server.log file because in that file you should see some DEBUG entries (with exception stack traces) that indicate that JMS discovered that the tables did not exist, after which it then creates them. But for some reason AS 5.1.0.GA was released with the logging level set to INFO. So try this - drop the tables from the database and then run the AS using:

              run -Djboss.server.log.threshold=DEBUG

              Then look at the JMS-related entries in the server.log.

              It might also help to turn query logging on in Sybase, that might give more hints as to what it going on.

              • 4. Re: sybase-persistence-service.xml
                Bob Damato Newbie

                 

                2009-11-03 14:21:44,515 DEBUG [org.jboss.messaging.core.impl.JDBCSupport] (main) Failed to execute: CREATE TABLE JBM_MSG_REF (MESSAGE_ID DECIMAL(19, 0) NOT NULL, CHANNEL_ID DECIMAL(19, 0) NOT NULL, TRANSACTION_ID DECIMAL(19, 0) NULL, STATE CHAR(1), ORD DECIMAL(19, 0), PAGE_ORD DECIMAL(19, 0) NULL, DELIVERY_COUNT INTEGER, SCHED_DELIVERY DECIMAL(19, 0), PRIMARY KEY(MESSAGE_ID, CHANNEL_ID))
                com.sybase.jdbc3.jdbc.SybSQLException: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'JBOSS' database.
                


                I found how to configure it in Sybase to allow this (sp_dboption JBOSS, "allow ddl in tran", true), but I'm wondering if there's a way to configure the connection in the connection pool so it doesn't behave like that.



                • 5. Re: sybase-persistence-service.xml
                  Peter Johnson Master

                  There might be a way to add that option to the URL in the *-ds.xml file. You'll have to check with the Sybase JDBC driver documentation for how to add options to the URL. Or you could use the connection-property element in *-ds.xml to set such properties.