14 Replies Latest reply on Oct 25, 2004 9:56 AM by sys user

    Changing default Database on JBoss 4.0

    sys user Master

      Hello everybody,

      I'm rather new to JBoss and wonder how difficult it is to change the default database. I try to use Informix as database and did everything mentioned in the JBoss startguide in the section "Using other databases".

      1.) I exchanged the file hsqldb-ds.xml by informix-ds.xml (where I put the right database-url, user and password)
      jndi-name of the database is "informix"
      2.) I added the informix driver to the lib directory of the server
      3.) I modified the standardjbosscmp-jdbc.xml
      java:/informix
      <datasource-mapping>InformixDB</datasource-mapping>

      4.) I modified the standardjaws.xml
      java:/informix
      <type-mapping>InformixDB</type-mapping>

      When I start JBoss, the Informix database is recognized and seems to be ok (looking at the jmx-console) but anyway, I get the following error during startup:

      MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM:
      ObjectName: jboss.jca:name=DefaultDS,service=DataSourceBinding
      state: NOTYETINSTALLED
      I Depend On:
      Depends On Me: jboss.ejb:persistencePolicy=database,service=EJBTimerService
      jboss:service=Ebxmlrr
      jboss:service=KeyGeneratorFactory,type=HiLo
      jboss.mq:service=StateManager
      jboss.mq:service=PersistenceManager

      Does anybody know what's the cause of this problem and how to solve it (so that jBoss starts without errors).

      Thanks in advance.


        • 1. Re: Changing default Database on JBoss 4.0
          Robert Szalai Newbie

          If you want to completely remove hsqldb from JBoss 4.0, you also
          need to modify additional xmls...

          Check these pages first:

          http://www.jboss.org/wiki/Wiki.jsp?page=ConfigJBossMQDB
          http://www.jboss.org/wiki/Wiki.jsp?page=ChangeTheDatabase

          • 2. Re: Changing default Database on JBoss 4.0
            sys user Master

            First of all thanks for your answer.

            I now made the modifications in the additional xmls, but how should the informix-jdbc2-service.xml(?) look like. There is no example file in the examples/jms directory for informix.

            • 3. Re: Changing default Database on JBoss 4.0
              Robert Szalai Newbie

              It's true but you can create your own easily.

              You only need to change DefaultDS to your datasource name,
              and modify the SQL statements if needed.

              You also need to create an informix-jdbc-state-service.xml
              in the same way.

              • 4. Re: Changing default Database on JBoss 4.0
                sys user Master

                I also added this two files and deleted the one's of hsql. Now i get the message:

                ObjectName: jboss.ejb:persistencePolicy=database,service=EJBTimerService
                state: FAILED
                I Depend On: jboss.jca:name=informix,service=DataSourceBinding

                Anyway...should I put the xxx-service files etc. in the deploy directory or in the deploy-hasingleton directory? Which is the difference. The default configuration of jboss e.g. has no deploy-hasingleton directory.

                I'm rather confused...

                • 5. Re: Changing default Database on JBoss 4.0
                  sys user Master

                  Sorry, I think the real error message is this one:

                  MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM:
                  ObjectName: jboss.mq:service=PersistenceManager
                  state: FAILED
                  I Depend On: jboss.jca:name=informix,service=DataSourceBinding

                  Depends On Me: jboss.mq:service=DestinationManager
                  org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Messag
                  e recovery may not be accurate; - nested throwable: (java.sql.SQLException: The
                  specified table (jms_messages) is not in the database.)
                  ObjectName: jboss.jca:name=DefaultDS,service=DataSourceBinding
                  state: NOTYETINSTALLED
                  I Depend On:
                  Depends On Me: jboss:service=Ebxmlrr
                  jboss:service=KeyGeneratorFactory,type=HiLo

                  • 6. Re: Changing default Database on JBoss 4.0
                    Robert Szalai Newbie

                    I think you haven't change the configuration everywhere...
                    You need to modify the following files also:

                    deploy/ejb-deployer.xml
                    Change DefaultDS to your datasource. Modify CREATE TABLE statement.
                    (Use BLOB type instead of "other").

                    uuid-key-generator.sar/META-INF/jboss-service.xml
                    Change DefaultDS to your datasource. Modify CREATE TABLE statement.

                    ebxmlrr-service.sar/META-INF/jboss-service.xml
                    Change DefaultDS to your datasource (in depend element too).

                    conf/login-config.xml
                    Change DefaultDS to your datasource.

                    This should be enough... I've tried it with Oracle.

                    The "all" configuration contains services for clustering.
                    Use "default" if you don't need it.

                    • 7. Re: Changing default Database on JBoss 4.0
                      sys user Master

                      After making the modifications I still get the error:

                      15:02:10,203 ERROR [DatabasePersistencePolicy] Starting failed jboss.ejb:persist
                      encePolicy=database,service=EJBTimerService
                      java.sql.SQLException: A syntax error has occurred.
                      at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:348)
                      at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3000)
                      at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3310)
                      at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2263)
                      at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2183)
                      at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:692)
                      at com.informix.jdbc.IfxResultSet.executeUpdate(IfxResultSet.java:308)
                      at com.informix.jdbc.IfxStatement.executeUpdateImpl(IfxStatement.java:85

                      Maybe this occurs because I did not modify the create table statement? But I don't know how.
                      The content of this statement in jboss-service.xml is


                      create table HILOSEQUENCES (
                      SEQUENCENAME varchar(50) not null,
                      HIGHVALUES integer not null,
                      constraint hilo_pk primary key (SEQUENCENAME)
                      )


                      What da I have to change here? Sorry, but I'm not very familiar with databases.

                      • 8. Re: Changing default Database on JBoss 4.0
                        Robert Szalai Newbie

                        I think the problem is with the CREATE TABLE statement in
                        ejb-deployer.xml.

                        You need to change the SQL type "other" to your database's BLOB
                        type. (I haven't known Informix, but you probably should change it
                        to "BLOB").

                        • 9. Re: Changing default Database on JBoss 4.0
                          sys user Master

                          Hmm, the SQL-type is "BLOB" and I changed it in the ejb-deployer.xml.

                          This cannot be the cause of the exception.

                          • 10. Re: Changing default Database on JBoss 4.0
                            Robert Szalai Newbie

                            Maybe there are other issues with your SQL statements...
                            Try to run them in your database manually and check errors...

                            • 11. Re: Changing default Database on JBoss 4.0
                              sys user Master

                              Maybe I have to change the sql-properties in informix-jdbc-state-service.xml.

                              POPULATE.TABLES.01 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('guest', 'guest')
                              POPULATE.TABLES.02 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('j2ee', 'j2ee')
                              POPULATE.TABLES.03 = INSERT INTO JMS_USERS (USERID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
                              POPULATE.TABLES.04 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('nobody', 'nobody')
                              POPULATE.TABLES.05 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('dynsub', 'dynsub')
                              POPULATE.TABLES.06 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('guest','guest')
                              POPULATE.TABLES.07 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('j2ee','guest')
                              POPULATE.TABLES.08 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('john','guest')
                              POPULATE.TABLES.09 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('subscriber','john')
                              POPULATE.TABLES.10 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','john')
                              POPULATE.TABLES.11 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','dynsub')
                              POPULATE.TABLES.12 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','john')
                              POPULATE.TABLES.13 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','dynsub')
                              POPULATE.TABLES.14 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('noacc','nobody')

                              could cause problems, because the tables cannot/shoulc not be created in the database.
                              Is it a problem if i delete these lines?

                              • 12. Re: Changing default Database on JBoss 4.0
                                Robert Szalai Newbie

                                If you want to use JMS and need persistence, you must keep these files.
                                If you don't need JMS you can remove the jms directory.

                                If you can't modify these SQL statements succesfully, I recommend you
                                return to original default configuration and let JBoss use hsqldb for
                                its own services (don't delete hsqldb-ds.xml).

                                • 13. Re: Changing default Database on JBoss 4.0
                                  sys user Master

                                  Does JBoss really (try to) create those tables, e.g.

                                  create table TIMERS (
                                  TIMERID varchar(50) not null,
                                  TARGETID varchar(50) not null,
                                  INITIALDATE timestamp not null,
                                  INTERVAL bigint,
                                  INSTANCEPK blob,
                                  INFO blob,
                                  constraint timers_pk primary key (TIMERID)

                                  in the database? Is it possible to prevent this behaviour? It wouldn't be good if JBoss creates such tables in my database because I'm not the only one using it.

                                  • 14. Re: Changing default Database on JBoss 4.0
                                    sys user Master

                                    Ok, I think it's better to keep hsql for the jms service. Which files do I have to "remodify"? I deleted my informix-jdbc2-service.xml, etc. and put the hsql files instead.
                                    What's with the ejb-deployer and those modifications?
                                    I want to keep Informix as current "working" database and JBoss shall use hsql for the jms service.