I don’t know how much time I could have saved, if someone told me I should not use the hypersonic database for a production JBoss system.

jboss.org/wiki:
hsqldb is not a production quality database.

I haven’t found a good tutorial, even the JBoss Developer’s Notebook wasn’t exhaustive, so here are my notes:

How to remove the hypersonice db from JBoss

1. Remove datasource:
   delete deploy/hsqldb-ds.xml
2. Add new datasource:
   for MySql add a file named deploy/mysql-ds.xml or whatever you like with something like:

   <datasources>
       <local-tx-datasource>
           <jndi-name>MySqlDS</jndi-name>
           <connection-url>jdbc:mysql://localhost:3306/datasource</connection-url>
           <driver-class>com.mysql.jdbc.Driver</driver-class>
           <user-name>myuser</user-name>
           <password>mypwd</password>
           <exception-sorter-class-name>
               org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
           </exception-sorter-class-name>
   
           <metadata>
               <type-mapping>mySQL</type-mapping>
           </metadata>
       </local-tx-datasource>
   </datasources>

   You should use MySqlDS as the jndi-name or you have to edit mysql-jdbc2-service.xml later

3. Use new datasource:
   find all references to DefaultDS and replace them with MySqlDS and all references to Hypersonic SQL with mySQL
4. Configure jms:
   delete deploy/jms/hsqldb-jdbc2-service.xml and replace it with doc/examples/jms/mysql-jdbc2-service.xml and don’t forget to edit mysql-jdbc2-service.xml

   Edit the persistence configuration and change the line :-

   SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES)

   to: -

   SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES