[jboss-user] [JBoss Messaging] - Exception: Did not load correct number of messages

[yuvalry]

Hi,

We are having the exact same problem as described in
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4060737#4060737

But we are using Oracle 10g DB.

We decided to follow Tim Fox advice from http://jira.jboss.com/jira/browse/JBMESSAGING-908 and to add a foreign key to JBM_MSG_REF table.

We modified the oracle-persistence-service.xml file and replaces the CREATE_MESSAGE_REFERENCE with following query:
CREATE TABLE JBM_MSG_REF (MESSAGE_ID INTEGER, CHANNEL_ID INTEGER, TRANSACTION_ID INTEGER, STATE CHAR(1), ORD INTEGER, PAGE_ORD INTEGER, DELIVERY_COUNT INTEGER, SCHED_DELIVERY INTEGER, PRIMARY KEY(MESSAGE_ID, CHANNEL_ID), CONSTRAINT FK_JBM_MSG_REF_MESSAGE_ID FOREIGN KEY (MESSAGE_ID) REFERENCES JBM_MSG(MESSAGE_ID))

But when running application server we get the following exception:
2008-05-06 11:17:19,765 DEBUG [org.jboss.messaging.core.impl.JDBCSupport] Failed to execute: CREATE TABLE JBM_MSG_REF (MESSAGE_ID INTEGER, CHANNEL_ID INTEGER, TRANSACTION_ID INTEGER, STATE CHAR(1), ORD INTEGER, PAGE_ORD INTEGER, DELIVERY_COUNT INTEGER, SCHED_DELIVERY INTEGER, PRIMARY KEY(MESSAGE_ID, CHANNEL_ID), CONSTRAINT FK_JBM_MSG_REF_MESSAGE_ID FOREIGN KEY (MESSAGE_ID) REFERENCES JBM_MSG(MESSAGE_ID))
java.sql.SQLException: ORA-00942: table or view does not exist

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

And of course the table is not created.

Our guess is that it's happening because JBM_MSG_REF table created before JBM_MSG table.

Are we right? How can we change the tables creation order?

Thanks,
Yuval

P.S
We tried to change the order of the queries in the oracle-persistence-service.xml file  and it didn't work :-(

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

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