JBoss 4.x fails to create JMS_XXX tables at startup
obender Dec 7, 2004 7:02 PMI'm observing some strange behaviour with JBossMQ when JMS persistance is configured to use MS SQL 2000 data source.
Looks like org.jboss.mq.pm.jdbc2.PersistenceManager creates its tables just fine but org.jboss.mq.sm.jdbc.JDBCStateManager creates only JMS_USERS table. No errors on creation of those missing tables in the log.
mssql-jdbc2-service.xml
<mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager" name="jboss.mq:service=PersistenceManager"> <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=jdbc/ejbPool_mssql4</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 SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB 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=? 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 = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB IMAGE, PRIMARY KEY (MESSAGEID, DESTINATION)) CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER) CREATE_TABLES_ON_STARTUP = TRUE </attribute> </mbean>
mssql-jdbc-state-service.xml
<mbean code="org.jboss.mq.sm.jdbc.JDBCStateManager" name="jboss.mq:service=StateManager"> <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=jdbc/ejbPool_mssql4</depends> <attribute name="SqlProperties"> CREATE_TABLES_ON_STARTUP = TRUE CREATE_USER_TABLE = CREATE TABLE JMS_USERS (USERID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, \ CLIENTID VARCHAR(128), PRIMARY KEY(USERID)) CREATE_ROLE_TABLE = CREATE TABLE JMS_ROLES (ROLEID VARCHAR(32) NOT NULL, USERID VARCHAR(32) NOT NULL, \ PRIMARY KEY(USERID, ROLEID)) CREATE_SUBSCRIPTION_TABLE = CREATE TABLE JMS_SUBSCRIPTIONS (CLIENTID VARCHAR(128) NOT NULL, \ SUBNAME VARCHAR(128) NOT NULL, TOPIC VARCHAR(255) NOT NULL, \ SELECTOR VARCHAR(255), 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=? </attribute> </mbean>
Any ideas? Am I missing something?