9 Replies Latest reply on May 16, 2011 7:19 AM by rmontag

    Problems changing DefaultDS

    drathnow


      JBoss Version is 4.2.0.GA

      I searched the Wiki and forums for a solution to this problem. I've seen two other posts with the same problem but nobody responded with a solution to I'll try.

      I'm trying to change the DefaultDS datasource from hsql to Oracle. I've followed the instructions I found in the wiki at http://wiki.jboss.org/auth/wiki/ConfigJBossMQDB?action=e&windowstate=normal&mode=view.

      All the JMS tables are being created but the KeyGeneratorFactory fails to deploy. I get the following stack trace in the log:

      2008-09-26 08:35:26,847 DEBUG [org.jboss.ejb.plugins.keygenerator.hilo.HiLoKeyGeneratorFactory] Starting jboss:service=KeyGeneratorFactory,type=HiLo
      2008-09-26 08:35:26,863 DEBUG [org.jboss.ejb.plugins.keygenerator.hilo.HiLoKeyGeneratorFactory] Executing SQL: select HIGHVALUES from HILOSEQUENCES where SEQUENCENAME='general'
      2008-09-26 08:35:26,894 DEBUG [org.jboss.ejb.plugins.keygenerator.hilo.HiLoKeyGeneratorFactory] Starting failed jboss:service=KeyGeneratorFactory,type=HiLo
      java.sql.SQLException: ORA-00942: table or view does not exist

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
      at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
      at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:790)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1037)
      at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:830)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
      at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1272)
      at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:171)
      at org.jboss.ejb.plugins.keygenerator.hilo.HiLoKeyGeneratorFactory.initSequence(HiLoKeyGeneratorFactory.java:320)
      at org.jboss.ejb.plugins.keygenerator.hilo.HiLoKeyGeneratorFactory.startService(HiLoKeyGeneratorFactory.java:281)


      If anyone is interested in the entire stack trace I will post it.

      Can anyone tell me what I need to do to get this to fix this?

      Thanks,
      Dave.

        • 1. Re: Problems changing DefaultDS
          kevintaute

          The wiki does not mention that you need to ensure the tables are available on the new data source. The error message

          java.sql.SQLException: ORA-00942: table or view does not exist
          indicates that you are trying to retrieve date from a table that does not exist. Most likely the
          HILOSEQUENCES
          table is not visable in the Oracle Instance.

          Have you tried to retrieve anything from the
          HILOSEQUENCES
          table directly (using PL/SQL or Toad or SquirrelSql or something like that).

          Best bet is to look at the sample DS for Oracle, and change the SQL in the default DS to match the SQL in the sample.

          • 2. Re: Problems changing DefaultDS
            peterj

            Check the server/xxx/deploy/uuid-key-generator.sar/META-INF/jboss-service.xml file. It contains a property used to create the table, make sure that property contains a correct DDL statement for Oracle.

            • 3. Re: Problems changing DefaultDS
              drathnow

              I looked at the SQL in server/xxx/deploy/uuid-key-generator.sar/META-INF/jboss-service.xml and it is correct. i.e. that syntax is correct for Oracle. I even went so far as to execute it to create the table. When I start JBoss, I get the following exception whenever JBoss tries to deploy one of my beans.


              10:50:48,895 INFO [EJBContainer] STARTED EJB: zedi.core.beans.OtadProgressServiceBean ejbName: OtadProgressServiceBean
              10:50:48,926 WARN [DatabasePersistencePolicy] Unable to get timer handles for containerId: jboss.j2ee:service=EJB3,ear=pacbridge.ear,jar=core.jar,name=OtadProgressServiceBean
              java.sql.SQLException: ORA-00942: table or view does not exist

              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
              at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
              at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
              at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:790)
              at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1037)
              at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:830)
              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
              at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1272)
              at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:171)
              at org.jboss.ejb.txtimer.GeneralPurposeDatabasePersistencePlugin.selectTimers(GeneralPurpose
              DatabasePersistencePlugin.java:257)
              at org.jboss.ejb.txtimer.DatabasePersistencePolicy.listTimerHandles(DatabasePersistencePolic
              y.java:165)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
              at java.lang.reflect.Method.invoke(Method.java:585)
              at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
              at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
              at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
              at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
              at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
              at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
              at $Proxy30.listTimerHandles(Unknown Source)
              at org.jboss.ejb.txtimer.EJBTimerServiceImpl.restoreTimers(EJBTimerServiceImpl.java:408)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
              at java.lang.reflect.Method.invoke(Method.java:585)
              at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
              at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
              at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
              at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
              at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
              at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
              at $Proxy110.restoreTimers(Unknown Source)
              at org.jboss.ejb3.timerservice.jboss.JBossTimerServiceFactory.restoreTimerService(JBossTimer
              ServiceFactory.java:120)
              at org.jboss.ejb3.stateless.StatelessContainer.start(StatelessContainer.java:98)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
              at java.lang.reflect.Method.invoke(Method.java:585)
              at org.jboss.ejb3.ServiceDelegateWrapper.startService(ServiceDelegateWrapper.java:103)

              Exception stack trace cut

              • 4. Re: Problems changing DefaultDS
                vickyk
                • 5. Re: Problems changing DefaultDS
                  peterj

                   

                  java.sql.SQLException: ORA-00942: table or view does not exist


                  Don't you just love how error messages assume you know what parameters were passed in the code and therefore they don't need name them (in this case, the name of the table or view).

                  But based on what is going on I would assume that the timer table was not built. I could not locate a DDL statement for this table, but apparently in Hypersonic is is created like this:

                  CREATE TABLE TIMERS(TIMERID VARCHAR(80) NOT NULL,TARGETID VARCHAR(250) NOT NULL,INITIALDATE TIMESTAMP NOT NULL,TIMERINTERVAL BIGINT,INSTANCEPK VARBINARY,INFO VARBINARY,CONSTRAINT TIMERS_PK PRIMARY KEY(TIMERID,TARGETID))

                  • 6. Re: Problems changing DefaultDS
                    drathnow

                    Vicky: apparently people in the JBossMQ forum didn't take kindly to your suggestion. They insist this is not there probelm.

                    Peter: yes, I could go and fix that, but then what will break? I guess what I'm after is an answer to why this is happening in the first place. JBoss is not tied to HSQL so changing DefaultDS as is documented should result in JBoss magically switching to Oracle, but this doesn't seem to be the case.

                    So perhaps I can ask a more general question: Is the information in the WIKI correct? Is changing DefaultDS as simple as changing a file in the JMS directory (under deploy) and then creating a new DefautlDS that points to Oracle?

                    Dave.

                    • 7. Re: Problems changing DefaultDS
                      peterj

                      Someone set up this wiki page to describe the steps to convert DefaultDS to use MySQL:

                      http://wiki.jboss.org/wiki/SetUpMysqlAsDefaultDS

                      The steps for Oracle should be the same, though the details will change. (I haven't reviewed those steps in detail, and lately all of my work has been done with 5.0 where the steps to replace hsqldb with another database are somewhat different. In addition, I work with MySQL and PostgreSQL.)

                      Essentially, the steps you outlined are correct. The catch is the DDL statements used by the various services. The wiki page you referenced details getting the messaging service DDL statements converted. But there are also tables for timers, hi-lo sequence tables, and transactions - you have run into issues with the first two, and looking at the last one I don't think that will be a problem. The issue is, of course, that different databases do not accept the same data types. But once you have corrected all of the tables, then you are done.

                      By the way, one way to view all of the tables is to use the hsqldb-ds.xml file, and start and then stop the app server. The tables can be view in the file server/xxx/data/hypersonic/localDB.script.

                      • 8. Re: Problems changing DefaultDS
                        drathnow

                        Thanks Peter,

                        That last bit of information about localDB.script really helped. The missing tables were HILOSEQUENCES and TIMERS. Once I created these, things started working.

                        Dave.

                        • 9. Problems changing DefaultDS
                          rmontag