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"?>
|
| <!-- $Id: mssql-jdbc2-service.xml 63369 2007-06-05 22:22:14Z dbhole $ -->
|
| <server>
|
| <!-- ====================================================================
-->
| <!-- Persistence and caching using MSSQL
-->
| <!-- IMPORTANT: Remove hsqldb-jdbc2-service.xml
-->
| <!-- Provided by [EMAIL PROTECTED] -->
| <!-- ====================================================================
-->
|
| <!--
| | The destination manager is the core service within JBossMQ
| -->
| <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>
|
| <!--
| | The MessageCache decides where to put JBossMQ message that
| | are sitting around waiting to be consumed by a client.
| |
| | The memory marks are in Megabytes. Once the JVM memory usage hits
| | the high memory mark, the old messages in the cache will start
getting
| | stored in the DataDirectory. As memory usage gets closer to the
| | Max memory mark, the amount of message kept in the memory cache
aproaches 0.
| -->
| <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>
|
| <!-- The PersistenceManager is used to store messages to disk. -->
| <!--
| | The jdbc2 PersistenceManager is the new improved JDBC implementation.
| | This implementation allows you to control how messages are stored in
| | the database.
| |
| | This jdbc2 PM configuration has been tested against MS SQL Server
2000
| |
| -->
| <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>
| <!-- 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 -->
| <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 [EMAIL PROTECTED];
| --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"?>
|
| <!-- $Id: hsqldb-jdbc-state-service.xml 63362 2007-06-05 19:19:17Z [EMAIL
PROTECTED] $ -->
|
| <server>
|
| <!-- ====================================================================
-->
| <!-- JBossMQ State Management
-->
| <!--
-->
| <!-- This configuration uses DefaultDS which by default is HSQLDB
-->
| <!--
-->
| <!-- If you want to use a different database/jndi name then either change
-->
| <!-- the ConnectionManager property, e.g.
-->
| <!-- Oracle: jboss.jca:service=DataSourceBinding,name=OracleDS
-->
| <!-- or
-->
| <!-- change the jndi binding DefaultDS to be the database you want to
use.-->
| <!-- ====================================================================
-->
|
| <!-- A Statemanager that stores state in the database -->
| <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
_______________________________________________
jboss-user mailing list
jboss-user@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/jboss-user
