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:

persistence.xml
...
<properties>
    <property name="toplink.target-database" 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"/>
</properties>
...

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:

/etc/mysql/my.cnf
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

default-storage-engine=innodb
$ 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.

Thursday, 9 April 2009

Jaunty Jackalope!

I've had GNU/Linux boxes for years. I think the first distribution I had was Unifix running on an old 486DX266 with about 500MB disk and 4MB of RAM. Then, as various pieces of upgraded hardware found their way into the box, it became Slackware followed by RedHat 6.2 (before RedHat got too commercial for my liking).

These days my server is a 500MHz Celeron dual-processor box with 768MB RAM running Ubuntu Intrepid Ibex 8.10. Samba file server, software RAID, caching DNS, Oracle XE, GlassFish v2 and Subversion repository.

There have been occasional flirtations with GNU/Linux as a desktop but, much as I wanted it to, it never quite cut it. Toward the end of last year, I decided to try again with Ubuntu Hardy Heron 8.04 on my new AMD64 laptop. I was pretty excited about it. I'd never tried Evolution as an email client before and loved it. Most of the software looked good and worked well. But after struggling with disappearing fonts and badly sized windows in a manually installed Netbeans 6.5, inability to resume after suspend and no 64-bit Flash plugin for Firefox, once again the idea of a GNU/Linux desktop fell by the wayside.

Anyway, this week I've been trying the pre-release version of Ubuntu: Jaunty Jackalope 9.04. I'm finding it better than ever. The distribution upgrade from 8.04 via 8.10 recovered flawlessly despite my laptop running out of battery half way through. In hindsight it wasn't the best time to dislodge the power cable!

I used the package manager to install Netbeans 6.5 (no need for a manual install now) and added all my usual Java EE plugins and GlassFish v3 Prelude as an application server. It all works a treat. I went for MySQL instead of Oracle this time, partly because there's no package installation for Oracle XE and partly because I wanted to test applications against both. The MySQL package install was a breeze. Subversion client and Netbeans plugin are up and running against my repository and Maven is just Maven.

Adobe recently released an alpha version of a Flash plugin for 64-bit Linux but I couldn't get their installation instructions to work with Firefox 3.0 (copying the libflashplayer.so to the ~/.mozilla/plugins directory). However, I used the script on this page and all is well. I suspect I may need to undo this at some point as the Adobe plugin becomes available as an install in Firefox but I'll cross that bridge when I come to it.

There are still a few issues (not all Ubuntu issues per se) but I think it's reached a point now where none of them stop me working:

  1. Suspend and resume doesn't work on my Toshiba Equium
  2. GlassFish v3 Prelude Update Tool isn't working but I've not investigated further
  3. Netbeans doesn't understand the way Tomcat is installed on a Unix filesystem

So I'm happy to say that I'm finally using GNU/Linux/Ubuntu - whatever you prefer to call it - exclusively on my laptop now.

The official release date of Ubuntu Jaunty Jackalope 9.04 is 23rd April.

Followers