8 Replies Latest reply on Oct 22, 2009 1:35 AM by jaikiran

'table does not exist' error after changing default databse

bwalker55 Oct 9, 2009 1:17 PM

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?

Thanks, Bob
-------------
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

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:182)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:500)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:942)
at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:532)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1037)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2905)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2946)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
at org.jboss.messaging.core.impl.JDBCPersistenceManager$1ReserveIDBlockRunner.doTransaction(JDBCPersistenceManager.java:407)

...snipped for brevity...

at org.jboss.bootstrap.AbstractServerImpl.start(AbstractServerImpl.java:461)
at org.jboss.Main.boot(Main.java:221)
at org.jboss.Main$1.run(Main.java:556)
at java.lang.Thread.run(Thread.java:619)

09:49:48,872 ERROR [JDBCSupport] Retried 25 times, now giving up
09:49:48,875 ERROR [ExceptionUtil] ServerPeer[0] startService
java.lang.IllegalStateException: Failed to execute transaction
at org.jboss.messaging.core.impl.JDBCSupport$JDBCTxRunner2.executeWithRetry(JDBCSupport.java:544)
at org.jboss.messaging.core.impl.JDBCPersistenceManager.reserveIDBlock(JDBCPersistenceManager.java:477)
at org.jboss.messaging.core.impl.IDManager.getNextBigBlock(IDManager.java:87)
at org.jboss.messaging.core.impl.IDManager.start(IDManager.java:74)
at org.jboss.jms.server.ServerPeer.startService(ServerPeer.java:310)
at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:376)

1. Re: 'table does not exist' error after changing default data

jaikiran Oct 12, 2009 3:38 AM (in response to bwalker55)

What's the name of the table which throws this error? And when exactly does this happen?
Actions
2. Re: 'table does not exist' error after changing default data

bwalker55 Oct 12, 2009 7:56 PM (in response to bwalker55)

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.
Bob
Actions
3. Re: 'table does not exist' error after changing default data

bwalker55 Oct 20, 2009 3:54 PM (in response to bwalker55)

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.

Thanks, Bob.
PS Might also want to raise this issue in the appropriate place to get a CREATE TABLE statement added.
Actions
4. Re: 'table does not exist' error after changing default data

jaikiran Oct 21, 2009 1:58 AM (in response to bwalker55)

"bwalker55" wrote:
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.
Actions
5. Re: 'table does not exist' error after changing default data

jaikiran Oct 21, 2009 2:01 AM (in response to bwalker55)

Also have a look at this http://www.jboss.org/community/wiki/ChangeDefaultDataSource
Actions
6. Re: 'table does not exist' error after changing default data

bwalker55 Oct 21, 2009 3:49 PM (in response to bwalker55)

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.
http://www.jboss.org/file-access/default/members/jbossas/freezone/docs/Installation_And_Getting_Started_Guide/5/pdf/Installation_And_Getting_Started_Guide.pdf
Unfortunately, there's no mention of JMS in this document.

I also referenced the following (and translated to Oracle)
http://www.jboss.org/community/wiki/SetUpAMysqlDatasource
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:
http://www.jboss.org/file-access/default/members/jbossas/freezone/docs/Administration_And_Configuration_Guide/5/html/alternative_DBs.html
and more specifically
http://www.jboss.org/file-access/default/members/jbossas/freezone/docs/Administration_And_Configuration_Guide/5/html/ch13s08s01.html

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:
http://www.jboss.org/community/wiki/ConfigDefaultDSJBossMQDB
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.

Thanks, Bob.
Actions
7. Re: 'table does not exist' error after changing default data

bwalker55 Oct 21, 2009 5:28 PM (in response to bwalker55)

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.
Actions
8. Re: 'table does not exist' error after changing default data

jaikiran Oct 22, 2009 1:35 AM (in response to bwalker55)

Bob,

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
Actions

Go to original post