The definitive (I hope!) guide to switching JBoss from Hypersonic to MySQL

Hi folks,

A real techie post today. I’ve just spent most of an afternoon trying to track down a complete list of steps for changing your default data source in JBoss 4.0.4.GA from Hypersonic to MySQL. I’ve had to visit at least a couple dozen forum posts, but have only found the 5 linked in the steps below to be useful in answering my question.

However, none of those 5 had the entire answer, which leads me to the reason for this post. I hope it can help other people out!

  1. (From OnJava.com): With your MySQL driver jarfile downloaded and added to your classpath, copy it to the [jboss-location]/server/default/lib directory.
  2. Changes to the /server/default/deploy directory:
    1. Remove [jboss-location]/server/default/deploy/hsqldb-ds.xml (or move to a location outside of your jboss directory)
    2. (From OnJava.com and EPFL): copy [jboss-location]/docs/examples/jca/mysql-ds.xml to this location. Modify the <local-tx-datasource> element inside the mysql-ds.xml configuration file to match the following (Note there are some parts below which you will have to change!):
      <local-tx-datasource>
      <jndi-name>DefaultDS</jndi-name>
      <connection-url>jdbc:mysql://your-hostname:your-port/your-database-name</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>your-user</user-name>
      <password>your-pass</password>
      <security-domain>MySqlDbRealm</security-domain>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
      <!-- should only be used on drivers after 3.22.1 with "ping" support
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-che\cker-class-name>
      -->
      <!-- sql to call when connection is created
      <new-connection-sql>some arbitrary sql</new-connection-sql>
      -->
      <!-- sql to call on an existing pooled connection when it is obtained from pool - MySQLValidConnectionChecker is preferred for n\ewer drivers
      <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
      --> <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
      <metadata>
      <type-mapping>mySQL</type-mapping>
      </metadata>
      </local-tx-datasource>
  3. Next we have to change and replace some files in [jboss-location]/server/default/conf:
    1. (From OnJava.com, Experts’ Exchange and EPFL): In standardjaws.xml change the following two elements, and leave the rest in place:
      <datasource>java:/DefaultDS</datasource>
      <type-mapping>mySQL</type-mapping>
    2. (From OnJava.com, Experts’ Exchange and EPFL): In standardjbosscmp-jdbc.xml change the following, not necessarily contiguous, elements only, leaving the rest in place:
      <datasource>java:/DefaultDS</datasource>
      <datasource-mapping>mySQL</datasource-mapping>
      <fk-constraint>true</fk-constraint>
    3. (From OnJava.com and EPFL): Introduce the following set of tags (ADDING, not replacing anything!) into login-config.xml:
      <application-policy name = "MySqlDbRealm">
      <authentication>
      <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">
      <module-option name = "principal">your-user</module-option>
      <module-option name = "userName">your-user</module-option>
      <module-option name ="password">your-pass</module-option>
      <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS</module-option>
      </login-module>
      </authentication>
      </application-policy>
  4. Next we have to change and replace some files in [jboss-location]/server/default/deploy/jms:
    1. (From OnJava.com and EPFL): Replace file hsql-jdbc2-service.xml by file [jboss-location]/docs/examples/jms/mysql-jdbc2-service.xml, and change the one mention of MySqlDS to DefaultDS
    2. (From Experts’ Exchange): Rename hsqldb-jdbc-state-service.xml to mysql-jdbc-state-service.xml. NO modifications within this file are required!

That’s all, folks! Let me know if I’ve gotten something wrong.

A full list of all the threads that helped me:

Advertisements

2 thoughts on “The definitive (I hope!) guide to switching JBoss from Hypersonic to MySQL”

  1. Hello!!!

    Thank you very much for write this interesting post! It’s help me a lot. I’m new in the jBPM’s world and I’ve configured jbpm to works with MySQL, but I have a problem. I’ve changed the hibernate.cgf.xml file to works with mySQL but it doen´t works. When I want to do a persistence operation en jbpm a exception is thrown:

    2007-12-09 10:56:08,328 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select job0_.ID_ as ID1_26_, job0_.VERSION_ as VERSION3_26_, job0_.DUEDATE_ as DUEDATE4_26_, job0_.PROCESSINSTANCE_ as PROCESSI5_26_, job0_.TOKEN_ as TOKEN6_26_, job0_.TASKINSTANCE_ as TASKINST7_26_, job0_.ISSUSPENDED_ as ISSUSPEN8_26_, job0_.ISEXCLUSIVE_ as ISEXCLUS9_26_, job0_.LOCKOWNER_ as LOCKOWNER10_26_, job0_.LOCKTIME_ as LOCKTIME11_26_, job0_.EXCEPTION_ as EXCEPTION12_26_, job0_.RETRIES_ as RETRIES13_26_, job0_.NAME_ as NAME14_26_, job0_.REPEAT_ as REPEAT15_26_, job0_.TRANSITIONNAME_ as TRANSIT16_26_, job0_.ACTION_ as ACTION17_26_, job0_.GRAPHELEMENTTYPE_ as GRAPHEL18_26_, job0_.GRAPHELEMENT_ as GRAPHEL19_26_, job0_.NODE_ as NODE20_26_, job0_.CLASS_ as CLASS2_26_ from JBPM_JOB job0_ where (job0_.LOCKOWNER_ is null or job0_.LOCKOWNER_=?) and job0_.RETRIES_>0 and job0_.DUEDATE_<=? and job0_.ISSUSPENDED_1 order by job0_.DUEDATE_ asc]
    com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘1 job0_.ID_ as ID1_26_, job0_.VERSION_ as VERSION3_26_, job0_.DUEDATE_ as DUEDAT’ at line 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1027)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
    …..

    10:56:46,078 [main] ERROR JDBCExceptionReporter : Table not found in statement [insert into JBPM_MODULEINSTANCE (ID_, VERSION_, PROCESSINSTANCE_, CLASS_) values (null, ?, ?, ‘C’)]
    10:56:46,078 [main] ERROR DbPersistenceService : hibernate commit failed
    org.hibernate.exception.SQLGrammarException: could not insert: [org.jbpm.context.exe.ContextInstance]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:40)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2158)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2638)
    at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:48)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
    at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:298)
    at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
    ……..

    I’ve check the dialect in hibertane.cfg file and is MySQLDialect and the tables that it doesn’t find exist in the DB!!

    Do you have any idea of the reason of that? Thank you very much and congratulations of your post.

    Regards!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s