Friday, February 02, 2007

JBoss: Hypersonic to mysql migration

So, now I had my server, deploying the trailbalzer

and even my application without any problems, but as there's already stated that Hypersonic isn't suited for production purposes and as I discovered that my tables were gone when restarting the server (only after some more hours I understoof it wasn't Hypersonic's fault, but a xml config file's), I decided to change to mySQL as a datasource for my applications using JBoss's services. Easy said...looking in a few
pages/articles/threads on this subject, the only thing I got to be sure about was
that there was no two opinions alike. And only for the 4.0.5 version, as I was careful not to read outdated info...Did those people offering advice really set their data source to mySQL? I guess I'll never know. Bottom line, none of the guides I'd found provided the complete solution to my problem, mainly all of them said the follwong things:

1.Download and set to the classpath the mysql driver (mysql-connector-java-5.0.4-bin.jar),
then copy this to [jboss-location]/server/default/lib directory.

2.Create a file named mysql-ds.xml in the
[jboss-location]/server/default/deploy/ directory and edit it similar
the example (my actual

<?xml version="1.0" encoding="UTF-8"?>

<!-- $Id: mysql-ds.xml,v 2006/02/07 14:23:00 acoliver Exp $ -->
<!-- Datasource config for MySQL using 3.0.9 available from:


<!-- This connection pool will be bound into JNDI with the name
"java:/MySqlDS" -->




<!-- should only be used on drivers after 3.22.1 with "ping"

<!-- sql to call when connection is created
<new-connection-sql>select 1</new-connection-sql>

<!-- sql to call on an existing pooled connection when it is
obtained from pool - MySQLValidConnectionChecker is preferred for newer
<check-valid-connection-sql>select 1</check-valid-connection-sql>

<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml
(optional) -->


The important lines are the bolded ones, one is for the url used for connecting to the mysql server; if you didn't change the port it listens to, leave 3306. 'localhost' is the host where the mysql server is running, if is something other than this, change it to the ip to reflect it corectly. After the slash there's the name of the datatbase the driver would connect to by default (in my case, "OPA").
The <user-name> element is obviously for specifying the user name that should be used when trying to connect to the mysql server, and the <password> element is for its corresponding password. Make sure u've created the user before testing the connection and have assigned that user with the rights for your above mentioned database. (see mysql manual for these operations :) )

3.Modify the file [jboss-location]\server\default\conf\standardjaws.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE jbosscmp-jdbc PUBLIC

<!-- Standard Jaws Configuration


<!-- $Id: standardjaws.xml 8624 2002-06-27 19:26:28Z dsundstrom $ -->



......{this part is unchanged}

4.Modify the file
[jboss-location]\server\default\conf\standardjbosscmp-jdbc.xml :

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jbosscmp-jdbc PUBLIC

<!-- Standard JBossCMP-JDBC Configuration

<!-- $Id: standardjbosscmp-jdbc.xml 41762 2006-03-06 14:39:33Z
aloubyansky $ -->


<!-- optional since 4.0
<datasource-mapping>mySQL</datasource-mapping> -->

Leave the long rest of this file unchanged. The <remove-table> is set to false so i would keep my application tables after a redeployment.
You don't have to change it from the default value of 'true' if you don't want this behaviour.

5.Add a <application-policy> element - inside the main <policy> one,
but not intercalated with other <application-policy> tags - in the
[jboss-location]\server\default\conf\login-config.xml file :

<application-policy name = "MySqlDbRealm">
<login-module code =
flag = "required">
<module-option name =
<module-option name =

<module-option name
<module-option name =


6.1. I don' really know if one could leave the jms need for a persistence environment to the old data source, HypersonicDS, and as I havent' used jms yet I can't be sure if I did the switching correctly (but by the lack of errors I'd say I did). But i proceeded to this step just to be sure:
Replace file [jboss-location]/server/default/deploy/jms/hsql-jdbc2-service.xml by
file [jboss-location]/docs/examples/jms/mysql-jdbc2-service.xml.

6.2.Rename hsqldb-jdbc-state-service.xml to mysql-jdbc-state-service.xml and change the line where the DeafultDS is mentioned:

<?xml version="1.0" encoding="UTF-8"?>

<!-- $Id: hsqldb-jdbc-state-service.xml 23386 2004-09-03 21:38:12Z
ejort $ -->



<!-- JBossMQ State Management using HSQLDB
<!-- See docs/examples/jms for other configurations

<!-- A Statemanager that stores state in the database -->
<mbean code=""

<attribute name="SqlProperties">

7. I can't stress out how important this was for me, it was the point missing in all other guides I read (many, so many of them....) :find all files containing a reference to DefaultDS and replace that reference with MySqlDS and do the same in replacing Hypersonic SQL with mySQL.

8. Don't forget to edit the persistence.xml in yout application to reflect the new data source used:

<persistence-unit name="the_name_of_your_persistence_unit">

<property name=""

I must note that the "update" value in the line <property name="" value="update"/> is also a part of the solution
for keeping the data in the tables after a redeploy of the application, instead of losing all the previous populated tables.


Vincent Tran said...

I am doing the same thing right now. On the ConfigJBossMQDB JBoss wiki page it states:

NOTE-READ THIS!!!: Other services such as the TimerService or the HiLo generator are relying on an existing datasource deployed with jndi name "DefaultDS".

So I believe the best solution would be to use DefaultDS as your jndi name instead of MySqlDS

powerpuffvic said...

how do you migrate de script??
thanks Victoria

Anonymous said...

Many thanks! it worked and we are very happy.


Unknown said...


Anonymous said...

thanks a lot it works.

A detail ... into the tag application-policy we have to set the correct login pwd etc. + correct DS (I believe that it is not mentioned in your article)

to be sure to not use the hypersonic_ds i've moved it away from the deploy directory.