How to configure MySQL for JBoss Mail Server

versions: 1.0M4, 1.0M5-pre1

Easy way

The easy way to configure MySQL for JBMS is to use the graphical installer and select "generate datasource", put in your server/user information and allow the installer to automatically install the MySQL Connector/J 3.1 driver into your $JBOSS_HOME/server/$CONFIG/lib directory.  You may want to read the bottom section about how to configure MySQL for larger blob sizes.

Hard way

The harder way to configure MySQL for JBMS is to copy the datasource descriptor example from $JBOSS_HOME/docs/examples/jca/mysql-ds.xml to the $JBOSS_HOME/server/$CONFIG/deploy directory.  Edit this replacing the user and server information with the information related to your database.  Following this you need to ensure that some additional information is in the JBMS jboss-service.xml descriptor.

Adding MySQL specific parts to the descriptor

In the jboss-service.xml you need to ensure that the following is defined.  (You may also want to remove

any alternatively defined "Store" implementations.)

        <!--
                Hibernate based store that uses a Paging mechanism
                to achieve partial I/O across a range of databases
        -->
        <mbean code="org.jboss.mail.store.paged.PagedStore"
                name="jboss.mail:type=MailServices,name=PagedStore">
                <attribute name="PageSize">65536</attribute>
                <attribute name="Compress">true</attribute>
                <attribute name="CompressBufferSize">65536</attribute>
                <attribute name="StartIndex">0</attribute>
                <attribute name="Hashed">false</attribute>
                <depends>jboss.mail:service=Hibernate</depends>
        </mbean>

        <!-- Mail Body Manager -->
        <mbean code="org.jboss.mail.message.MailBodyManager"
                name="jboss.mail:type=MailServices,name=MailBodyManager">
                <attribute name="UseStore">true</attribute>

                <depends optional-attribute-name="Store"
                        proxy-type="attribute">jboss.mail:type=MailServices,name=PagedStore</depends>

        </mbean>

There is also an addition descriptor that you must change called $JBOSS_HOME/server/$CONFIG/deploy/mail.ear/mail.par/META-INF/persistence.xml.  Ensure it reads as follows:

<persistence>
  <persistence-unit name="mail">
  <jta-data-source>java:/YOUR_DATASOURCE_NAME</jta-data-source>
  <properties>
    <property name="jboss.entity.manager.jndi.name"
              value="java:/EntityManagers/mail"></property>
   <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"></property>
    <property name="hibernate.hbm2ddl.auto"
              value="update"></property>
  </properties>
  </persistence-unit>
</persistence>

That's basically it.  You will need to restart the underlying JBAS instance after making this change.

MySQL blob page size

JBMS messages are stored in the database as BLOBs. MySQL requires that the entire blob be sent at once or that "emulated SQL locators" be used.  The above chooses the latter option which move the inefficiency to the database side than over the whole stack.  The entire page size listed above must fit comfortably in mysql's transmit page.  You can define this in your my.cnf:

set-variable    = max_allowed_packet=32M

You'll then need to restart MySQL.

It is strongly recommended that you use the innodb table option.  Consult the MySQL documentation for more information on that issue.

Efficiency

MySQL does not handle blobs efficiently so we recommend you consider an alternative database for JBMS.  However when using the paged store MySQL has adequate performance though underperforms PostgreSQL in benchmarks.