Insane Programming Java ramblings and finds on the net

Ensuring transaction safety when using the MySQL/JPA+Hibernate/Spring mix

When using JPA, using transactions is something one might take for granted. However, when you're using MySQL and you're not in control of the database, you may find yourself debugging a really hard bug: transactions that aren't properly rolled back.

You see, MySQL has 2 main storage engines: MyISAM and InnoDB. There are a lot of differences between the two, one of which is the fact that MyISAM is non-transactional. As in: doesn't support rollback at all. Now imagine this case: you have 2 JPA classes where one of the tables (A) has a one-to-many relationship to the other (B). Deletes on A are cascaded on B. You're writing test cases on the JPA entitymanager to test the simple CRUD functionality and off course you're rolling back the transaction after each test (after all, you wouldn't want to have one test that is affecting the outcome of other tests). Guess what happens if A is an InnoDB table and B is a MyISAM table? The A objects will still be in the database after the rollback, the B objects linked to that A object won't be. It's hell when testing, a disaster when this goes to production. Throw in the fact that 1. you don't administer the database and 2. Hibernate won't check storage engines and you have yourself a real PITA.

So I wrote this little class to be included into each and every MySQL/JPA+Hibernate/Spring project I do from now on. It checks on startup of the Spring container whether all tables are in the InnoDB format.

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.Assert;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Checks whether all tables in a database are using a certain engine to store
 * data
 */
public class MySQLTableEngineCheck {
    private DataSource dataSource;

    private String dbName;

    private String engine = "InnoDB";

    /**
     * Perform the check.
     * @throws IllegalStateException if the database contains tables that do not use the specified engine
     */
    @PostConstruct
    public void check()
    {
        Assert.notNull(dataSource);
        Assert.notNull(dbName);
        JdbcTemplate template = new JdbcTemplate(dataSource);
        List nonInnoDbTables = template.queryForList("select TABLE_NAME from information_schema.tables i where TABLE_SCHEMA = ? and ENGINE <> ?;", new Object[] {dbName, engine});
        if(nonInnoDbTables.size() > 0)
        {
            List<String> tables = new ArrayList<String>();
            for (Object nonInnoDbTable : nonInnoDbTables) {
                Map map = (Map) nonInnoDbTable;
                tables.add(map.get("TABLE_NAME").toString());
            }
            throw new IllegalStateException("Some of the tables are not using the " + engine + " engine : " + StringUtils.join(tables, ", "));
        }
    }

    /**
     * The database engine that all tables in the specified database should use
     */
    public void setEngine(final String engine) {
        this.engine = engine;
    }

    /**
     * The database to be checked
     */
    public void setDbName(final String dbName) {
        this.dbName = dbName;
    }

    /**
     * The datasource to be used for performing the check
     */
    public void setDataSource(final DataSource dataSource) {
        this.dataSource = dataSource;
    }
}

Put this bean in your Spring context and it'll warn you immediately when things aren't as they should be.

<bean id="innoDbChecker" class="MySQLTableEngineCheck">
    <property name="dataSource" ref="myDS"/>
    <property name="dbName" value="myDB"/>
</bean>

If you're using for example the classes from my Liquibase article, don't forget to put a depends-on="liquibase" in the innoDbChecker bean definition.