Configuration :

AS : JBOSS 5.1

OS : Ubuntu 12.04.1 LTS

JDK : 1.6.0_27

Changing JBoss Database to Mysql :

1) In MySQL :

CREATE DATABASE jbossDB;

CREATE USER 'jboss'@'localhost' IDENTIFIED BY 'jboss';

GRANT ALL ON jbossDB.* TO jboss@localhost;

2) Download MySQL driver from http://dev.mysql.com/downloads/connector/j/, for instance mysql-connector-java-5.1.24.zip

3) Extract only mysql-connector-java-5.1.24-bin.jar

4) Copy mysql-connector-java-5.1.24-bin.jar to server/default/lib

5) Remove other drivers (ex. Hypersonic SQL h2-1.2.139.jar) from server/default/lib

6) Copy and adapt as follows JBOSS_HOME/docs/examples/jca/mysql-ds.xml to JBOSS_HOME/server/default/deploy/

It is preferred to rename MySqlDS ==> DefaultDS (it is simpler way to migrate form Hypersonic to Mysql)

<local-tx-datasource>

        <jndi-name>DefaultDS</jndi-name>

        <connection-url>jdbc:mysql://yourIP:yourPort/jbossDB</connection-url> <!-- ex yourIP = localhost and yourport = 3306 -->

        <driver-class>com.mysql.jdbc.Driver</driver-class>

        <min-pool-size>5</min-pool-size>               

        <max-pool-size>100</max-pool-size>             

        <idle-timeout-minutes>60</idle-timeout-minutes>

    <security-domain>MySqlDbRealm</security-domain>

        <!-- This is to -try- to avoid MySQL INNODB known deadlocks -->

        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation> <!-- Without thi, you will also have a Warning -->

    <!-- MySqlDbRealm is defined server/default/conf/login-config.xml -->

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

7) Remove other *-ds.xml files from JBOSS_HOME/server/default/deploy/*-ds.xml (ex. h2-ds.xml, hsqldb-ds.xml)

8) define MySqlDbRealm needed by DefaultDS in /home/baina/angel/BOS-5.10-JBoss-5.1.0.GA/server/default/conf/login-config.xml

<application-policy name = "MySqlDbRealm">

  <authentication>

  <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">

   <module-option name = "principal">PRINCIPAL</module-option> <!-- ex. jboss -->

   <module-option name = "userName">jboss</module-option> <!-- same db user defined before -->

   <module-option name ="password">jboss</module-option> <!-- same db user passwd defined before -->

   <!-- passwd can be crypted using login-module code="org.jboss.resource.security.SecureIdentityLoginModule"

        and running

   java -cp client/jboss-logging-spi.jar:lib/jbosssx.jar org.jboss.resource.security.SecureIdentityLoginModule LEPASSWORD

   for more details : http://docs.jboss.org/jbosssecurity/docs/6.0/security_guide/html/Encrypting_Data_Source_Passwords.html

   -->

   <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS</module-option>

  </login-module>

  </authentication>

</application-policy>

9) Copy and adapt as follows JBOSS_HOME/docs/examples/jms/mysql-persistence-service.xml TO JBOSS_HOME/server/default/deploy/messaging/mysqldb-persistence-service.xml 

- Set CreateTablesOnStartup property to false Directely AFTER first succesful run of JBoss (on MySQL)

<attribute name="CreateTablesOnStartup">true</attribute> <!-- TWICE in mysqldb-persistence-service.xml -->

- Comment ChannelFactoryName property which deals with Clutering <!-- depends optional-attribute-name="ChannelFactoryName">jboss.jgroups:service=ChannelFactory</depends> -->

10) Comment InterceptorsFactory stuff in JBOSS_HOME/server/default/deploy/ejb3-interceptors-aop.xml

in order to avoid WARN  [InterceptorsFactory] EJBTHREE-1246: Do not use InterceptorsFactory with a ManagedObjectAdvisor, InterceptorRegistry should be used via the bean container

<!--aspect name="InterceptorsFactory" factory="org.jboss.ejb3.interceptors.aop.InterceptorsFactory" scope="PER_INSTANCE"/-->

<!--advice name="invoke" aspect="InterceptorsFactory"/-->

11) JBOSS_HOME/bin/run.sh 1>output.txt &

12) you will remark that jbossDB is created through MySQL

Congratulations JBoss is persistent over MySQL.

Or use AS7 (or later) which allows the default datasource to be configured for JPA deployments.