Saturday, 11 April 2009

Toplink JPA and InnoDB

Further to my post on my new-found love of Ubuntu, I've been porting a prototype application I'm working on to MySQL. This is an EJB3/JPA web application that was running on Oracle XE with Toplink as the JPA provider.

When I got it up and running I noticed that the tables were being created in MySQL using the non-transactional MyISAM engine, which doesn't really fit with the whole distributed transaction ethos of the application. I could easily change the storage engine for the tables in MySQL Administrator, but they would revert to MyISAM when I redeployed the application because I am still prototyping and using drop-and-create through Toplink.

My first step was to verify that Toplink wasn't creating these tables with MyISAM through the MySQL dialect that I am using. After some digging into the Toplink documentation I found these handy properties that can be set in persistence.xml:

    <property name="" value="MySQL4"/>
    <property name="toplink.ddl-generation" value="drop-and-create-tables"/>
    <property name="toplink.ddl-generation.output-mode" value="both"/>
    <property name="toplink.application-location" value="/tmp"/>

The toplink.ddl-generation.output-mode of both asks Toplink to externalize its drop and create scripts to files in the directory specified by toplink.application-location as well as issuing the DDL against the database. Apart from being useful in its own right, this confirmed that Toplink wasn't specifying the storage engine for the tables it was creating.

So the next step was to try setting the default storage engine on MySQL. On Linux, this is the easiest solution:

$ sudo /etc/init.d/mysql stop

Then edit /etc/mysql/my.cnf and add the default-storage-engine entry in the [mysqld_safe] section of the config file:

socket          = /var/run/mysqld/mysqld.sock
nice            = 0

$ sudo /etc/init.d/mysql start

Which works a treat, with JPA now creating InnoDB tables instead of MyISAM. But it seems an invasive approach, setting the whole database default to InnoDB.

Far better would be to set the default storage engine on a per-session basis, using SET SESSION storage_engine=InnoDB;. This can also be achieved through the MySQL JDBC driver using the sessionVariables property:

Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

With GlassFish I was able to achieve this quite easily by adding the property to the connection pool I'd created for the MySQL database:

Once the change to the default storage engine on MySQL was reversed and GlassFish restarted to re-establish the connections in the pool, I was able to redeploy my application and see my tables created with the InnoDB engine.

No comments: