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 EmbeddedMysqlDatabase.java and EmbeddedMysqlDatabaseBuilder.java

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/libc.so.6: 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

fail to install myql gem for ruby

You tried to install mysql gem on linux:

$ gem install mysql

Building native extensions.  This could take a while...
ERROR:  Error installing mysql:
	ERROR: Failed to build gem native extension.

/home/t/ruby-1.9.2-p136-1/bin/ruby extconf.rb
checking for mysql_ssl_set()... *** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers.  Check the mkmf.log file for more
details.  You may need configuration options.

Provided configuration options:
	--with-opt-dir
	--without-opt-dir
	--with-opt-include
	--without-opt-include=${opt-dir}/include
	--with-opt-lib
	--without-opt-lib=${opt-dir}/lib
	--with-make-prog
	--without-make-prog
	--srcdir=.
	--curdir
	--ruby=/home/t/ruby-1.9.2-p136-1/bin/ruby
	--with-mysql-config
	--without-mysql-config
/home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:368:in `try_do': The complier failed to generate an executable file. (RuntimeError)
You have to install development tools first.
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:435:in `try_link0'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:440:in `try_link'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:552:in `try_func'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:797:in `block in have_func'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:693:in `block in checking_for'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:280:in `block (2 levels) in postpone'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:254:in `open'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:280:in `block in postpone'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:254:in `open'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:276:in `postpone'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:692:in `checking_for'
	from /home/t/ruby-1.9.2-p136-1/lib/ruby/1.9.1/mkmf.rb:796:in `have_func'
	from extconf.rb:50:in `<main>'


Gem files will remain installed in /home/t/ruby-1.9.2-p136-1/lib/ruby/gems/1.9.1/gems/mysql-2.8.1 for inspection.
Results logged to /home/t/ruby-1.9.2-p136-1/lib/ruby/gems/1.9.1/gems/mysql-2.8.1/ext/mysql_api/gem_make.out
 

You need to check mkmf.log which is in the same folder as gem_make.out is in.  In my case, mkmf.log is in /home/t/ruby-1.9.2-p136-1/lib/ruby/gems/1.9.1/gems/mysql-2.8.1/ext/mysql_api/ 

Read mkmf.log, if you see something similar to below: 

/usr/bin/ld: skipping incompatible /usr/lib/libz.so when searching for -lz
/usr/bin/ld: skipping incompatible /usr/lib/libz.a when searching for -lz
/usr/bin/ld: cannot find -lz
collect2: ld returned 1 exit status

It means you have an incompatible zlib-devel binary.  In case, the 32-bit zlib-devel is installed, but not the 64 bits.  Run following command to fix it:

yum erase zlib-devel
yum install zlib-devel 

Select a range from oracle, mysql

I talked about how to select first/top n rows from oracle, mysql, and ms sql server. How do we get the range, say from m to n, where m < n?

Oracle

select id, age from (select id, age, rownum as rn from customer order by age) where rn between :m and :n

MySql

select * from customer order by age limit :m, :n - :m

MS SQL

I don't know how to do it with MS sql server yet. I don't have ms sql server installed. If you happen to know it, please post your solution in the comment.

Select first n rows from oracle, MySql and MS Sql

Assume we have table customer with columns id and age. Find first n customers with youngest ages

Oracle

select * from (select * from customer order by age) where rownum < :n

Mysql

select * from customer order by age limit :n

MS Sql

select top :n * from customer order by age