Search This Blog

Monday, June 10, 2013

Using embedded mysql database for unit test with maven and spring

I used H2 in memory database to unit test my DAO.  However, my production database is MySQL.
Generally, H2 is compatible with MySQL.  However, H2 doesn't support ENUM type yet, and my schema uses ENUM for a column definition.  Inspired by this post and another, I successfully used embedded MySQL for unit test.  Here is what I did:

1.  Add mysql-connector-mxj dependency:

2. Create and

3. Create spring bean datasource:
4. Run unit test with Spring:
A couple things are worth mentioning:

1. The test uses Spring annotation @DirtiesContext(classMode = ClassMode.AFTER_EACH_TEST_METHOD) which reload the context after every test method.  It means every test method starts a new mysql instance.  You may group test methods to 2 different categories, one changes data, and another not.  For those methods don't change data, only need to create one instance of mysql to speed up testing.

2. If you see the following error message on Linux server:
/lib64/ version `GLIBC_2.7' not found (required by /tmp/test_db_2420035739165052/bin/mysqld)

You need to either downgrade mysql-connector-mxj to 5.0.11 or upgrade your linux OS.  I got the above error message on CentOS 5.4 with 5.0.12, but no problem with 5.0.11. However, 5.0.11 isn't in any public maven repo, but you can download it from here and install it to your local repo, or upload to your company's repo. Using the following command to check CentOS version:

cat /etc/redhat-release

3. The code is tested on Mac and CentOS only, and can be downloaded from github


  1. You can also try Maven plugin created exactly for this purpose: It starts a local MySQL server on pre-integration-test phase and shuts it down on post-integration-test.

    1. I looked into this approach but it seems that plugin isn't being supported all that well and that made me nervous. Also, I need an embedded-DB not only for integration-testing but also for unit-testing as well as specialized deployment situations. It wasn't clear to me how to use this plugin for those other situations. So I went with zhentao's solution above and it seems to be working quite well for me.

    2. jcabi-mysql-maven-plugin requires perl to be installed, it's a blocker for our team.

  2. This was a big help for me; thanks so much!

  3. Just work, jcabi-mysql-maven-plugin from start not work as expected. Great thanks.

  4. This helped us a lot on our projects.

    Question: How can I set the character set and collation of the database instance?


    1. You can set it in your schema.sql. Here is the example:

  5. Great article. It was very helpful. Thank you. For me, local mysql is created and ddl is executed but I am seeing error while shut down of mysql after the unit test(java.util.MissingResourceException: Resource 'kill.exe' not found). Any suggestions is greatly appreciated!