Configuring JBoss to use MySQL

Configuring JBoss to use MySQL

For JBoss 3.2.6 see JbossMySql32

I'm not sure which items are case-significant. Play safe and assume they all are!

Download the MySql jdbc drivers from http://www.mysql.com/downloads/api-jdbc.html
Extract mysql-connector-java-3.0.11-stable-bin.jar from the download and copy it to jboss-3.0.8/server/xxx/lib
If you're using the older drivers then copy mm.mysql-2.0.4-bin.jar to jboss-3.0.6/server/xxx/lib instead. (I don't know where you can download these from now).
copy jboss/docs/examples/jca/mysql-service.xml to jboss-3.0.8/server/xxx/deploy directory
edit jboss-3.0.8/server/xxx/conf/login-config.xml as follows:

There is an example <application-policy> entry in mysql-service.xml. It is commented out and irrelevant to that file. Copy this example to login-config.xml. You'll see similar sections in login-config.xml. Update the <module-option> entries for userName and password setting them to those used by your MySQL database.

E.g.

     <application-policy name = "MySqlDbRealm">
         <authentication>
             <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
                 flag = "required">
                 <module-option name = "principal">sa</module-option>
                 <module-option name = "userName">myUserName</module-option>
                 <module-option name = "password">myPassword</module-option>
                 <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=MySqlDS</module-option>
             </login-module>
         </authentication>
     </application-policy>

edit jboss-3.0.8/server/xxx/deploy/mysql-service.xml

If you're using the newer MySQL jdbc drivers (e.g. 3.0.11) then you'll need to change the driver class name in mysql-service.xml. Amend the entry from 'org.gjt.mm.mysql.Driver' to 'com.mysql.jdbc.Driver'. If you're still using the older driver (e.g. 2.0.4) then leave it as is.

You'll also need to change the ConnectionURL property from 'jdbc:mysql://dell:3306/jbossb' to something like 'jdbc:mysql://localhost:3306/mydbname'.

e.g.

     <depends optional-attribute-name="ManagedConnectionFactoryName">
        <!--embedded mbean-->
        <mbean code="org.jboss.resource.connectionmanager.RARDeployment" name="jboss.jca:service=LocalTxDS,name=MySqlDS">
          <attribute name="JndiName">MySqlDS</attribute>
          <attribute name="ManagedConnectionFactoryProperties">
             <properties>
                <config-property name="ConnectionURL" type="java.lang.String">jdbc:mysql://localhost:3306/myDbName</config-property>
         <!--
                <config-property name="DriverClass" type="java.lang.String">org.gjt.mm.mysql.Driver</config-property>
         -->
                <config-property name="DriverClass" type="java.lang.String">com.mysql.jdbc.Driver</config-property>
                <!--set these only if you want only default logins, not through JAAS -->
                <config-property name="UserName" type="java.lang.String"></config-property>
                <config-property name="Password" type="java.lang.String"></config-property>
             </properties>
          </attribute>

There's also a section which must be uncommented, below the example <application-policy> section.

    <attribute name="SecurityDomainJndiName">MySqlDbRealm</attribute>

edit server/xxx/conf/standardjbosscmp-jdbc.xml replacing the existing <datasource/> and <datasource-mapping> entries as follows:

[--FrankDean - not sure this is necessary, or there's a better way...]

  <jbosscmp-jdbc>
      <defaults>
         <datasource>java:/MySqlDS</datasource>
         <datasource-mapping>mySQL</datasource-mapping>
         ...

    * edit server/xxx/conf/standardjaws.xml as follows:

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

Update your META-INF/jaws.xml that gets deployed with your application to include the following entry:

[--FrankDean - not sure this is necessary...]

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

I use XDoclet to create my deployment descriptors (and more!) and updated the <jboss> subtask of my ant <ejbdoclet> task in build.xml to include them, e.g.

            <jboss version="${jboss.version}"
                xmlencoding="UTF-8"
                typemapping="mySQL"
                datasource="java:/MySqlDS"
                destdir="${build.dir}/META-INF"
                validateXml="false"
            />

That's it!

Note: MySql uses lowercase for table names. This causes problems with JBoss if you used any upper-case characters when defining the table name. Most things work fine, but creating and deleting tables fails. Best use all lower-case characters for table names.

Note: These instructions will leave JBoss using Hypersonic for the jbossmq persistence manager. Options to consider persuing are:

Change the "jdbc2" mq persistence manager to use the mysql datasource
Change jbossmq to use a non-jdbc persistence manager
Rename or copy the MySQL datasource to DefaultDS

-- Frank Dean - 06 June 2003
-- Frank Dean - 03 March 2004 - updated