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
to
the example (my actual
file):


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

<!-- $Id: mysql-ds.xml,v 1.3.2.3 2006/02/07 14:23:00 acoliver Exp $ -->
<!-- Datasource config for MySQL using 3.0.9 available from:
http://www.mysql.com/downloads/api-jdbc-stable.html

-->

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

<datasources>
<local-tx-datasource>
<jndi-name>MySqlDS</jndi-name>

<connection-url>jdbc:mysql://localhost:3306/OPA</connection-url>

<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>A_USER_NAME</user-name>
<password>A_PASSWORD</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>5</idle-timeout-minutes>



<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-checker-class-name>
-->
<!-- 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
drivers
<check-valid-connection-sql>select 1</check-valid-connection-sql>
-->

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

<type-mapping>mySQL</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>

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
"-//JBoss//DTD JBOSSCMP-JDBC 3.0//EN"
"http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_0.dtd">

<!--
=====================================================================
-->
<!--
-->
<!-- Standard Jaws Configuration
-->

<!--
-->
<!--
=====================================================================
-->

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

<jaws>
<datasource>java:/MySqlDS</datasource>
<type-mapping>mySQL</type-mapping>

<debug>false</debug>

<default-entity>
......{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
"-//JBoss//DTD JBOSSCMP-JDBC 4.0//EN"
"http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_4_0.dtd">

<!--
=====================================================================
-->
<!--
-->
<!-- Standard JBossCMP-JDBC Configuration
-->
<!--
-->
<!--
=====================================================================
-->

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

<jbosscmp-jdbc>

<defaults>
<datasource>java:/MySqlDS</datasource>
<!-- optional since 4.0
<datasource-mapping>mySQL</datasource-mapping> -->

<create-table>true</create-table>
<remove-table>false</remove-table>
........
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 :


<policy>
.....
<application-policy name = "MySqlDbRealm">
<authentication>
<login-module code =
"org.jboss.resource.security.ConfiguredIdentityLoginModule"
flag = "required">
<module-option name =
"principal">the_mysql_username</module-option>
<module-option name =
"userName">the_mysql_username</module-option>

<module-option name
="password">the_mysql_pass</module-option>
<module-option name =
"managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=MySqlDS</module-option>
</login-module>
</authentication>
</application-policy>

....
</policy>

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 $ -->

<server>

<!--
====================================================================
-->

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

<!-- A Statemanager that stores state in the database -->
<mbean code="org.jboss.mq.sm.jdbc.JDBCStateManager"
name="jboss.mq:service=StateManager">
<depends
optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=MySqlDS</depends>

<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>
<persistence-unit name="the_name_of_your_persistence_unit">
<jta-data-source>java:/MySqlDS</jta-data-source>
<properties>

<property name="hibernate.hbm2ddl.auto"
value="update"/>
</properties>
</persistence-unit>
</persistence>

I must note that the "update" value in the line <property name="hibernate.hbm2ddl.auto" 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.