Thursday, April 21, 2011

Embedded MySql Server for jUnit Testing With Maven

Be sure to see the two UDPATES to this blog at the bottom.

Those of you who use an in-memory hsqldb instance for junit testing know the frustration when faced with some code that uses syntax of features specific to MySQL.  Your options for this situation are basically to skip the tests completely, or to have a dedicated junit server somewhere on your network, neither of which are particularly great ideas.

Then I came across this gem, which seems to be the most linked-to article on embedded MySQL servers in Java.  In fact it seems to be the only real article with any information!

He perhaps makes it sound a little easier than it is, but all in all he nails it.  There are a few tricks, though.
  1. The Connector/MXJ library (in Maven "com.mysql:management:5-0-2-beta") requires two JSR libraries from SUN: jmxri and jmxtools.  Unfortunately, these are not available in the standard Maven repository, due to binary distribution constraints.  This is the same constraint as on javax.servlet libraries, and has the same solution.
    1. Download the binary package from Sun.  I needed v1.2
    2. Rename the jars to "jmxri-1.2.jar" and "jmxtools-1.2.jar"
    3. Upload to your local Maven repository.  Use "com.sun.jmx" as the groupId and "jmxri" and "jmxtools" as the artifactId respectively.
  2.  If you already have a MySQL instance installed on the build machine (or your local box) then there will be a conflict on the port and socket connector file.  We'll need to change this at runtime.
    1. Create a new class "EmbeddedMysqlDataSource extends MysqlDataSource"
    2. Provide a new constructor to add additional configuration to the instance setup
    3. Override the "getConnection( Properties props )" method
    4. Set a new value for the MysqldResourceI.SOCKET and MysqldResourceI.PORT properties
    5. Call the super-class implementation
  3. To simplify, I provide a static Factory class to provide a DataSource object pointing to the embedded database instance.  It also allows you to track the necessary information to kill the instance when you're done with it.
Here's what my class looks like:

package com.literatitech.example;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

// We use the excellent Log5j variant
import com.spinn3r.log5j.Logger;

public class EmbeddedMysqlDataSource extends MysqlDataSource
  private int port;
  private String sock;
  private String url;
  private File basedir;
  private File datadir;
  private Connection connection;

  private static Logger logger = Logger.getLogger();

  public static EmbeddedMysqlDataSource getInstance()
    EmbeddedMysqlDataSource dataSource = null;
    try {
      dataSource = new EmbeddedMysqlDataSource( 4000 );
      dataSource.setUrl( dataSource.getEmbeddedUrl() );
      dataSource.setUser( "root" );
      dataSource.setPassword( "" );
    } catch( Exception e2 ) {
      dataSource = null; "Could not create embedded server.  Skipping tests. (%s)", e2.getMessage() );
    return dataSource;

  public static void shutdown( EmbeddedMysqlDataSource ds )
    try {
    } catch( IOException e ) { "Could not shutdown embedded server. (%s)", e.getMessage() );

  public EmbeddedMysqlDataSource( int port ) throws IOException
    this.port = port;
    sock = "sock" + System.currentTimeMillis();

    // We need to set our own base/data dirs as we must
    // pass those values to the shutdown() method later
    basedir = File.createTempFile( "mysqld-base", null );
    datadir = File.createTempFile( "mysqld-data", null );

    // Wish there was a better way to make temp folders!

    StringBuilder sb = new StringBuilder();
    sb.append( String.format( "jdbc:mysql:mxj://localhost:%d/test", port ));
    sb.append( "?createDatabaseIfNotExist=true" );
    sb.append( "&server.basedir=" ).append( basedir.getPath() );
    sb.append( "&server.datadir=" ).append( datadir.getPath() );
    url = sb.toString();

  public String getEmbeddedUrl()
    return url;

  protected java.sql.Connection getConnection( Properties props ) throws SQLException
    if( connection == null ) {
      props.put( MysqldResourceI.PORT, String.valueOf( port ));
      props.put( MysqldResourceI.SOCKET, sock );
      props.put( MysqldResourceI.BASEDIR, basedir.getPath() );
      props.put( MysqldResourceI.DATADIR, datadir.getPath() );
      connection = super.getConnection( props );
    return connection;

  public void shutdown() throws IOException
    ServerLauncherSocketFactory.shutdown( basedir, datadir );

Once done you can spool up a new MySQL instance within a unit test with:

dataSource = EmbeddedMysqlDataSource.getInstance();

  • The embedded server can run concurrently with a normal instance on the same machine
  • The server process doesn't shutdown instantly... might want to pause in the shutdown method
  • Consult the Connector/MXJ Documentation for more configuration options

UPDATE 1:  Turns out there is a know issue which prevents more than one connection from being obtained on a port other then 3306.  So, your choices are to get one connection on an alternate port and use it for all your testing, or to give up running an embedded server on a machine which has another MySQL install.  Perhaps we could make a Connection singleton which ignores normal close() calls...

UPDATE 2:  Another problem -- if you use the com.mysql:management:5-0-2-beta artifact from Maven, it has a dependency for aspectj:aspectjtools which causes conflicts within the java.xml.parsers package during Spring startup, specifically a "Provider org.apache.xerces.jaxp.DocumentBuilderFactoryImpl not found" exception.  You can exclude it in your POM but I'm not what effect, if any, that will have on the server operation.


1 comment: