The following configuration worked well for me in a production system using JBoss AS 4.x, MySQL, and Hibernate with a Seam application.

Naturally I want to have a transactional DBMS, so I always pick InnoDB as the MySQL storage engine. I also want UTF-8 support because the content I store is entered by users from all over the world. Hence not all of the tips and tricks shown here might be relevant for you, in fact, if you are happy with your system in production, do not change anything. Also note that many of these customizations are only relevant if you let Hibernate generate your MySQL DDL and schema for you.

The Hibernate Dialect

The built-in Hibernate dialect for MySQL and InnoDB storage engine is MySQL5InnoDBDialect. There are several issues that I had with the settings as configured by this dialect, and I wrote my own subclass to fix those problems. These are only relevant if you let Hibernate generate DDL, they do not affect runtime behavior:

public class EnhancedMySQL5HibernateDialect extends MySQL5InnoDBDialect {

    public EnhancedMySQL5HibernateDialect() {
        super();
        registerColumnType(Types.BIT, "tinyint(1)");
    }

    @Override
    protected void registerVarcharTypes() {
        registerColumnType(Types.VARCHAR, "longtext");
        registerColumnType(Types.VARCHAR, 16777215, "mediumtext");
        registerColumnType(Types.VARCHAR, 1023, "varchar($l)");
    }

    @Override
    public String getTableTypeString() {
        return " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";
    }

}

Let's walk through this code and see why it does what it does.

Storage and representation of boolean values

MySQL, just like Oracle DBMS, does not support a real boolean datatype (the only type required by the relational data model)!

Hibernate maps a Java boolean to an ANSI SQL java.sql.Types.BIT.

This is then mapped in the dialect as bit(1) in MySQL which is hard to re-use when you do a 'mysqldump' export of your database. A tinyint(1) on the other hand needs more storage space (1 byte), however it is nicer because most import tools can handle boolean representations of '0' and '1' whereas you will have trouble importing the exported bit/binary data into a boolean field on anything that isn't MySQL.

If you are sure you will never migrate from MySQL then you probably can use continue using bit(1). If all of this doesn't make sense to you, just use the default bit(1) mapping and look at your data with 'mysqldump'. Try to execute those SQL statements on anything that is not MySQL. Also note that bit(1) is only available in MySQL >= 5.0.5. Finally, this is from the MySQL JDBC driver release notes (http://dev.mysql.com/doc/refman/5.0/en/cj-news-3-1-9.html):

The datatype returned for TINYINT(1) columns when tinyInt1isBit=true (the default) can be switched between Types.BOOLEAN and Types.BIT using the new configuration property transformedBitIsBoolean, which defaults to false. If set to false (the default), DatabaseMetaData.getColumns() and ResultSetMetaData.getColumnType() will return Types.BOOLEAN for TINYINT(1) columns. If true, Types.BOOLEAN will be returned instead. Regardless of this configuration property, if tinyInt1isBit is enabled, columns with the type TINYINT(1) will be returned as java.lang.Boolean instances from ResultSet.getObject(...), and ResultSetMetaData.getColumnClassName() will return java.lang.Boolean. (Bug#10485)

Picking the right TEXT datatypes

The next issue is large text data. MySQL does not have a single TEXT datatype; like many other broken DBMSs it has char, varchar, text, longtext, mediumtext, footext, whatever. All of these are of course the same to an application. But the DBMS uses them to constrain the text length, letting its internal implementation details leak into the application.

So depending on the expected length of the text you want to store, you have to pick the right MySQL datatype so that MySQL can prepare how much space it is going to waste internally.

There are some limitations to that. For example, if you just create a VARCHAR(length) column with a large number, you probably didn't expect that there is also a row length limit in MySQL of 65 kilobytes. Also note that 'length' is in characters but the row size limit is in bytes. If you use UTF8 encoding to store the text, each character has two bytes. So five VARCHAR(10000) columns on one table would be OK if you do not use UTF8. It's not OK if you use UTF8.

The other TEXT datatypes such as 'mediumtext' and 'longtext', etc. do not have the row length limit of 65 kb.

Unfortunately, the Hibernate dialect for MySQL assumes that any Java string type with a length of up to 65535 characters (not bytes!) should be stored in a VARCHAR(length) in MySQL. With UTF8 encoding that would be a maximum of 131070 bytes in a single VARCHAR field and you could not even store one field value without running into the row size limit.

So we change the default mapping here and assume that anything with more than 1024 characters (minus length byte) should be TEXT, not a VARCHAR(length). And to help MySQL a little, we switch to 'longtext' when the text is larger than 2 MB (non-UTF8) or 4 MB (UTF8) - wich means 16777216 characters (minus length byte).

Finally, all tables are created with the InnoDB storage engine and data is stored encoded using UTF-8 by default.

Datasource and connection pool configuration

In JBoss AS 4.x I deployed the following datasource configuration:

<datasources>

    <local-tx-datasource>
        <jndi-name>myDatasource</jndi-name>

        <connection-url>
            jdbc:mysql://localhost/mydatabase?characterEncoding=UTF-8
        </connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>someuser</user-name>
        <password>somepassword</password>
        <min-pool-size>20</min-pool-size>

        <!--
         Make sure your MySQL /etc/my.cnf has that many max_connections! 
         This is also the the max_threads in default Tomcat server.xml on JBoss AS. 
        -->
        <max-pool-size>250</max-pool-size>
        <blocking-timeout-millis>5000</blocking-timeout-millis>

        <!--
         Needs to be smaller than wait_timeout (which is in seconds) in /etc/my.cnf! 
        -->
        <idle-timeout-minutes>2</idle-timeout-minutes>

        <!--
         These are the secret ingredients that make it stable! Using the 
         simple check-valid-connection-sql option won't be enough! 
        -->
        <exception-sorter-class-name>
            com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
        </exception-sorter-class-name>
        <valid-connection-checker-class-name>
            com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
        </valid-connection-checker-class-name>
    </local-tx-datasource>

</datasources>

Enabling UTF-8 support in Tomcat

Tomcat in JBoss AS 4.x does not correctly decode UTF-8 URLs. So if you are passing text strings encoded as URL parameters (e.g. /search.seam?query=%E2%82%AC for the EUR currency symbol) you will not get the query string properly decoded in your Seam application (or any servlet, for that matter).

To enable correct decoding, edit

${JBOSS_HOME}/server/(default)/deploy/jboss-web.deployer/server.xml

and add

URIEncoding="UTF-8"

to the <connector> declaration.