Chariot Training Classes

Training Courses

I run Chariot's training and mentoring services. We provide training in AngularJS, HTML5, Spring, Hibernate, Maven, Scala, and more.

Chariot Education Services


Chariot Emerging Tech

Learn about upcoming technologies and trends from my colleagues at Chariot Solutions.


Chariot Conferences


« Living on the Roo "edge" w/git | Main | Progress on Roo in Action »

Spring JUnit Tests not Rolling Back? It may not be you...

Plug/Disclaimer! I'm teaching a Hibernate with Spring course in September, and while preparing for the course I came up with this tidbit. I hope you enjoy it.

Here's a little tip for you Spring users who are using MySQL.  If you just installed MySQL with the defaults, you may find that Spring's @ContextConfiguration and @RunWith(SpringJUnit4Runner.class) annotations might not work for you.

import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.
import org.springframework.transaction.annotation.Transactional;

public class CourseIntegrationTest {

  private SessionFactory sessionFactory;
  public void testCreateCourse() {
    Session session = sessionFactory.getCurrentSession();
    Course course = new Course();
    course.setCost(new BigDecimal("1000.00"));
    course.setStartDate(new Date());;
    // now, we get and check

The Problem...

The default behavior of Spring when running integration tests like this is to roll back the transaction. So, you go ahead and execute the test, and hope that it rolls back the row. But, in fact, it might not - you might see the row in the database. Why?

The answer lies in whether you've installed and configured the InnoDB engine in MySQL. What is InnoDB? It's a transactional storage engine that ships with MySQL binaries as of 5.1 and higher. Here is a good wikipedia article on InnoDB for further reading. You can tell whether it is installed by executing the following SQL as the 'root' MySQL user: (I've removed the "comment" field so it fits on my blog page)

mysql> show engines;
| Engine     | Support | Transactions | XA   | Savepoints |
| CSV        | YES     | NO           | NO   | NO         |
| MRG_MYISAM | YES     | NO           | NO   | NO         |
| MEMORY     | YES     | NO           | NO   | NO         |
| MyISAM     | DEFAULT | NO           | NO   | NO         |

In the case above, I haven't yet configured InnoDB - MyISAM is the default engine, which is also non-transactional. Since MySQL can have several installed engines, and one is the default, setting the wrong default (as well as not installing a transactional engine) can be a problem!

When you create tables, you can specify the engine they use, otherwise they get the default. I found a GREAT article about verifying your Spring JPA MySQL tables to make sure they use a transactional (InnoDB) data store. Since we're geeking out, you can also run this command in MySQL against your table to see what settings it has (many more columns come back than the ones I'm showing):

mysql> show table status;
| Name   | Engine | Version | Row_format | Rows | Avg_row_length |
| Course | MyISAM |      10 | Compact    |    1 |          16384 |

Run your JUnit Spring integration tests against a table with this engine, and you'll see that rollbacks are ignored, even though Spring shows that they are sent. Here is what Spring shows us when we run the test, which would lead you to believe that everything is ok, until you look at the data in the table:

Fetching JDBC Connection from DataSource
Returning JDBC Connection to DataSource
Creating new transaction with name [testCreateCourse]: 
Opened new Session ... for Hibernate transaction
Preparing JDBC Connection of Hibernate Session ...
Exposing Hibernate transaction as JDBC transaction 
   UserName=root@localhost, MySQL-AB JDBC Driver]
        (cost, description, startDate) 
        (?, ?, ?)
binding '1000.00' to parameter: 1
binding 'Basketweaving' to parameter: 2
binding '29 August 2010' to parameter: 3
Triggering beforeCompletion synchronization
Initiating transaction rollback
Rolling back Hibernate transaction on Session ...
Triggering afterCompletion synchronization
Closing Hibernate Session ... after transaction
Closing Hibernate Session
Closing Hibernate SessionFactory

Incidentally, here is my file for getting all of that nice log output:

# suppress everything else

# log field bindings

# log transactions 

... and my Hibernate settings from within my AnnotationSessionFactoryBean...

<property name="hibernateProperties">

Ok, so obviously for a serious application involving more than one SQL statement at a time, this is seriously inadequate. So, let's fix it!

Installing InnoDB

I'm using a Mac, so your mileage for these instructions will vary, and you'll have to have a passing familiarity with the command line. First, create a my.cnf file (or edit the existing one). Mine is located in /etc/my.cnf, but yours may live in /usr/local/mysql/data or in another place. I have added the following settings to my file, taken from a few blog entries:


Now, to install this file, you need to shutdown and start up MySQL. I use the following commands from OS X:

sudo mysqladmin shutdown
sudo mysqld_safe --console & 
sudo cat /usr/local/mysql/data/yourservername.err

Verifying the Installation

Now, to verify that everything is configured correctly, check the same

show engines
command again as the MySQL root user:

mysql> show engines;
| Engine     | Support | Transactions | XA   | Savepoints |
| CSV        | YES     | NO           | NO   | NO         |
| MRG_MYISAM | YES     | NO           | NO   | NO         |
| MEMORY     | YES     | NO           | NO   | NO         |
| InnoDB     | DEFAULT | YES          | YES  | YES        |
| MyISAM     | YES     | NO           | NO   | NO         |
5 rows in set (0.01 sec)

If all is well, you now have InnoDB, and it's the default engine. Try the test, and see if the rows are rolled back. Important: you may have to drop or modify the table to make it use InnoDB. There is a simple SQL command to modify it:

mysql> alter table Course engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

If you get any warnings, just type

show warnings
and you'll get a message.

That's it. I hope this helps someone who is wrestling with MySQL databases and Hibernate transactions. I know I have had trouble with this when preparing the Hibernate section of my book, Roo in Action.

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (1)

Thanks very much. Had this exact problem running Spring JUnit tests and this solution worked, saving me a lot of frustration.
Much appreciated.

November 2, 2010 | Unregistered CommenterJimmie

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>