I've changed the default database (DefaultDS) to Oracle as per instructions elsewhere. jBoss comes up and works. Tables, with what look like sample entries, are created in Oracle. However, the startup logs show an error attempting to access (25 times) a table that does not exist. Any ideas?
09:49:48,870 WARN [JDBCSupport] SQLException caught, SQLState 42000 code:942- assuming deadlock detected, try:25
java.sql.SQLException: ORA-00942: table or view does not exist
...snipped for brevity...
09:49:48,872 ERROR [JDBCSupport] Retried 25 times, now giving up
09:49:48,875 ERROR [ExceptionUtil] ServerPeer startService
java.lang.IllegalStateException: Failed to execute transaction
What's the name of the table which throws this error? And when exactly does this happen?
Thanks for the response,
Unfortunately, all I know is what"s in the log file, as quoted above.
So, all I can say is that it happens sometime during startup.
As well, the Oracle error processor doesn't tell you what table it can"t read. Not much help.
I have turned on audiiting in Oracle. The results are:
OWNER OBJ_NAME ACTION_NAME
JBOSS JMS_USERS CREATE TABLE
JBOSS JMS_ROLES CREATE TABLE
JBOSS JMS_SUBSCRIPTIONS CREATE TABLE
JBOSS JMS_ROLES SESSION REC
JBOSS JMS_USERS SESSION REC
JBOSS JMS_TRANSACTIONS SELECT
It would appear that the table that is missing is JMS_TRANSACTIONS.
Note that the other three tables have a CREATE TABLE attempt (which will only be successful the first time), then a SELECT.
JMS_TRANSACTIONS is never created, so the SELECT statement always fails.
I have verified this by created a dummy JMS_TRANSACTIONS table.
The error in the log file has changed to:
Caused by: java.sql.SQLException: ORA-00904: "TXID": invalid identifier
So obviously TXID is at least one of the column names in JMS_TRANSACTIONS.
Can someone please post the schema for JMS_TRANSACTIONS.
PS Might also want to raise this issue in the appropriate place to get a CREATE TABLE statement added.
I've changed the default database (DefaultDS) to Oracle as per instructions elsewhere.
Which exact JBoss AS version do you use? And can you point us to the exact steps you followed to change the default DB. The creation of appropriate tables should already have been taken care by the xml files for jms.
Thanks for the response. I think I'm getting there.
JBoss version is 4.2.3 on server in question.
For information on changing the default datasource, I used the Installation and Getting Started Guide.
Unfortunately, there's no mention of JMS in this document.
I also referenced the following (and translated to Oracle)
but must admit that I skipped the JMS part (as I was initially attempting to bring up jBoss without messaging, before I realized this would leave me without the admin consoles).
I have since found:
and more specifically
I have followed the advice of these documents and replaced the hsqldb-jdbc2-service.xml with oracle-jdbc2-service.xml
Note that the document you reference:
is similar, but does not indicate that the oracle-jdbc2-service.xml must also be edited, changing OracleDS to DefaultDS (if the DefaultDS is redefined).
If you don't make this change, which I initially missed, there are no database errors on startup, but the JMS_TRANSACTIONS and JMS_MESSAGES tables do not get created. Once this change is made, the startup looks the same from the log file, and the JMS_TRANSACTIONS and JMS_MESSAGES tables are created in Oracle.
Of course, this is all for 4.2. I believe the change for 5.1 is simlar, but the file names and locations of the -service.xml files are different.
Update for jBoss 5.1
The instructions for moving messaging/JMS/persistance to a database other than HSQL(example below is Oracle) on jBoss 5.1 are:
1) remove file server/xxx/deploy/messaging/hsqldb-persistence-service.xml
2) copy docs/examples/jms/oracle-persistance-service* to server/xxx/deploy/messaging
3) restart jBoss.
*Note that this file no longer needs to be edited. The DataSourceBinding is already set to DefaultDS.
Thank you for letting others know about these steps. Feel free to add/edit to wiki any of such relevant information so that it will be easily available to others.
As for JBoss Messaging in AS-5 and the steps to change the default DB, there's a wiki here http://www.jboss.org/community/wiki/ConfigMessagingDB