Random Thoughts

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.
       transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

@ContextConfiguration(locations=
   {"classpath:/META-INF/spring/applicationContext.xml"})
@RunWith(SpringJUnit4ClassRunner.class)
@TransactionConfiguration(defaultRollback=true)
public class CourseIntegrationTest {

  @Autowired
  private SessionFactory sessionFactory;
  
  
  @Test
  @Transactional
  public void testCreateCourse() {
    Session session = sessionFactory.getCurrentSession();
    Course course = new Course();
    course.setCost(new BigDecimal("1000.00"));
    course.setDescription("Basketweaving");
    course.setStartDate(new Date());
    
    session.save(course);
    
    // now, we get and check
    session.flush();
    
    Assert.assertNotNull(course.getId());   
    
  }
  
}

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)

+------------+---------+--------------+------+------------+
| 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]: 
  PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
Opened new Session ... for Hibernate transaction
Preparing JDBC Connection of Hibernate Session ...
Exposing Hibernate transaction as JDBC transaction 
   [jdbc:mysql://localhost:3306/hibernate_sandbox, 
   UserName=root@localhost, MySQL-AB JDBC Driver]
Hibernate: 
    insert 
    into
        Course
        (cost, description, startDate) 
    values
        (?, ?, ?)
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 log4j.properties file for getting all of that nice log output:

# suppress everything else
log4j.logger.org.springframework=error
log4j.logger.org.hibernate=error

# log field bindings
log4j.logger.org.hibernate.type=trace

# log transactions
log4j.logger.org.springframework.jdbc.datasource=trace
log4j.logger.org.springframework.orm.hibernate3=trace 

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

<property name="hibernateProperties">
  <value>
    hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
    hibernate.hbm2ddl.auto=update
    hibernate.show_sql=true
    hibernate.format_sql=true
  </value>
</property>

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:

[mysqld]
default-storage-engine=InnoDB
innodb_data_home_dir=/usr/local/mysql/data
innodb_data_file_path=ibdata-new:10M:autoextend
innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=64M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1

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

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

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.