[jboss-user] [JCA/JBoss] - Re: MSSQL XA datasource throws RollbackException on cluster

Fri Dec 14 20:16:46 EST 2007

I tried setting CREATE_TABLES_ON_STARTUP = FALSEthat did not seem to have any impact. 

I was concerned about the error, because it was evident that it stopped processing the initialization at the point of the error, because there were other errors that would have been reported had it continued. i.e. there were other tables that were already created which would have caused identical errors that were not being reported.

So I did a bit of work and changed the SQL in mssql-jdbc2-service.xml config file. hsqldb-jdbc-state-service.xml also contained code which generated the errors, so I made a copy and renamed it to mssql-jdbc-state-service.xml

The changes that I made are as follows:

mssql-jdbc2-service.xml<?xml version="1.0" encoding="UTF-8"?>
  | 
  | 
  | 
  | <server>
  | 
  |   
  |   
  |   
  |   
  |   
  | 
  |   
  |   <mbean code="org.jboss.mq.server.jmx.DestinationManager" name="jboss.mq:service=DestinationManager">
  |     <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
  |     <depends optional-attribute-name="PersistenceManager">jboss.mq:service=PersistenceManager</depends>
  |     <depends optional-attribute-name="StateManager">jboss.mq:service=StateManager</depends>
  |   </mbean>
  | 
  |   
  |   <mbean code="org.jboss.mq.server.MessageCache"
  | 	 name="jboss.mq:service=MessageCache">
  |     <attribute name="HighMemoryMark">50</attribute>
  |     <attribute name="MaxMemoryMark">60</attribute>
  |     <attribute name="CacheStore">jboss.mq:service=PersistenceManager</attribute>
  |   </mbean>
  | 
  |   
  |   
  |   <mbean code="org.jboss.mq.pm.jdbc2.MSSQLPersistenceManager"
  | 	 name="jboss.mq:service=PersistenceManager">
  |     <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
  |     <attribute name="SqlProperties">
  |       BLOB_TYPE=BINARYSTREAM_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
  |       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 TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=?
  |       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 = IF OBJECT_ID (N'dbo.JMS_MESSAGES', N'U') IS NULL CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB IMAGE)
  |       CREATE_IDX_MESSAGE_TXOP_TXID = IF dbo.CHECK_FOR_INDEX('dbo.JMS_MESSAGES','JMS_MESSAGES_TXOP_TXID') = 0 CREATE INDEX JMS_MESSAGES_TXOP_TXID ON JMS_MESSAGES (TXOP, TXID)
  |       CREATE_IDX_MESSAGE_DESTINATION = IF dbo.CHECK_FOR_INDEX('dbo.JMS_MESSAGES','JMS_MESSAGES_DESTINATION') = 0 CREATE INDEX JMS_MESSAGES_DESTINATION ON JMS_MESSAGES (DESTINATION)
  |       CREATE_IDX_MESSAGE_MESSAGEID_DESTINATION = IF dbo.CHECK_FOR_INDEX('dbo.JMS_MESSAGES','JMS_MESSAGES_IDX') = 0 CREATE UNIQUE CLUSTERED INDEX JMS_MESSAGES_IDX ON JMS_MESSAGES (MESSAGEID, DESTINATION)
  |       CREATE_TX_TABLE = IF OBJECT_ID (N'dbo.JMS_TRANSACTIONS', N'U') IS NULL CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )
  |       CREATE_TABLES_ON_STARTUP = TRUE
  |     </attribute>
  |     
  |     
  |     
  |     <attribute name="RecoverMessagesChunk">0</attribute>
  |   </mbean>
  | 
  | </server>

The changes that I made are highlighted in red. What I did was to add a check to verify that the table does not exist prior to creating it. I could find no similar check to verify that the index does not exist , so I wrote a scalar function that would perform the check. I ran the create script as the jboss user. The function definition follows:

  | use jboss
  | go
  | 
  | IF OBJECT_ID(N'dbo.CHECK_FOR_INDEX', N'FN') IS NOT NULL
  |     DROP FUNCTION dbo.CHECK_FOR_INDEX;
  | GO
  | 
  | CREATE FUNCTION dbo.CHECK_FOR_INDEX (@TABLE_NAME VARCHAR(256), @INDEX_NAME VARCHAR(256))
  | RETURNS int
  | AS
  | BEGIN
  | DECLARE @RETV int,
  |         @TABLE_ID int
  | 
  |    set @TABLE_ID=OBJECT_ID(@TABLE_NAME,N'U');
  | 
  | 	IF @TABLE_ID IS NULL
  | 	BEGIN;
  | 	   RETURN 0;
  | 	END;
  | 
  | 	SELECT @RETV = COUNT(*)
  | 	FROM sys.indexes AS i
  | 	INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
  | 	WHERE is_hypothetical = 0 AND i.index_id <> 0 
  | 	AND i.object_id = @TABLE_ID
  | 	AND i.name=@INDEX_NAME;
  | 	--PRINT 'Database = ' + @TABLE_NAME + ' Index = ' + @INDEX_NAME
  | 	--PRINT @RETV;
  | 	RETURN @RETV;
  | END;
  | GO
  | 

To complete the changes I made, to resolve the issue, here is the mssql-jdbc-state-service.xml file, with my changes highlighted in red:

mssql-jdbc-state-service.xml<?xml version="1.0" encoding="UTF-8"?>
  | 
  | 
  | 
  | <server>
  | 
  |   
  |   
  |   
  |   
  |   
  |   
  |   
  |   
  |   
  |   
  |   
  |    
  |   
  |   <mbean code="org.jboss.mq.sm.jdbc.JDBCStateManager"
  | 	      name="jboss.mq:service=StateManager">
  |     <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
  |     <attribute name="SqlProperties">
  |       CREATE_TABLES_ON_STARTUP = TRUE
  |       CREATE_USER_TABLE = IF OBJECT_ID (N'dbo.JMS_USERS', N'U') IS NULL CREATE TABLE JMS_USERS (USERID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, \
  |                                                  CLIENTID VARCHAR(128) NULL, PRIMARY KEY(USERID))
  |       CREATE_ROLE_TABLE = IF OBJECT_ID (N'dbo.JMS_ROLES', N'U') IS NULL CREATE TABLE JMS_ROLES (ROLEID VARCHAR(32) NOT NULL, USERID VARCHAR(32) NOT NULL, \
  |                                                  PRIMARY KEY(USERID, ROLEID))
  |       CREATE_SUBSCRIPTION_TABLE = IF OBJECT_ID (N'dbo.JMS_SUBSCRIPTIONS', N'U') IS NULL CREATE TABLE JMS_SUBSCRIPTIONS (CLIENTID VARCHAR(128) NOT NULL, \
  |                                                  SUBNAME VARCHAR(128) NOT NULL, TOPIC VARCHAR(255) NOT NULL, \
  |                                                  SELECTOR VARCHAR(255) NULL, PRIMARY KEY(CLIENTID, SUBNAME))
  |       GET_SUBSCRIPTION = SELECT TOPIC, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
  |       LOCK_SUBSCRIPTION = SELECT TOPIC, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
  |       GET_SUBSCRIPTIONS_FOR_TOPIC = SELECT CLIENTID, SUBNAME, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE TOPIC=?
  |       INSERT_SUBSCRIPTION = INSERT INTO JMS_SUBSCRIPTIONS (CLIENTID, SUBNAME, TOPIC, SELECTOR) VALUES(?,?,?,?)
  |       UPDATE_SUBSCRIPTION = UPDATE JMS_SUBSCRIPTIONS SET TOPIC=?, SELECTOR=? WHERE CLIENTID=? AND SUBNAME=?
  |       REMOVE_SUBSCRIPTION = DELETE FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
  |       GET_USER_BY_CLIENTID = SELECT USERID, PASSWD, CLIENTID FROM JMS_USERS WHERE CLIENTID=?
  |       GET_USER = SELECT PASSWD, CLIENTID FROM JMS_USERS WHERE USERID=?
  |       POPULATE.TABLES.01 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('guest', 'guest')
  |       POPULATE.TABLES.02 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('j2ee', 'j2ee')
  |       POPULATE.TABLES.03 = INSERT INTO JMS_USERS (USERID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
  |       POPULATE.TABLES.04 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('nobody', 'nobody')
  |       POPULATE.TABLES.05 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('dynsub', 'dynsub')
  |       POPULATE.TABLES.06 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('guest','guest')
  |       POPULATE.TABLES.07 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('j2ee','guest')
  |       POPULATE.TABLES.08 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('john','guest')
  |       POPULATE.TABLES.09 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('subscriber','john')
  |       POPULATE.TABLES.10 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','john')
  |       POPULATE.TABLES.11 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','dynsub')
  |       POPULATE.TABLES.12 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','john')
  |       POPULATE.TABLES.13 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','dynsub')
  |       POPULATE.TABLES.14 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('noacc','nobody')
  |     </attribute>
  |   </mbean>
  | 
  | </server>

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4113114#4113114

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4113114