Migrating from MySQL 5.5 to 5.6.4 to have milliseconds/fractional seconds support

Before MySQL version 5.6.4, MySQL does not support milliseconds (more precise: fractional seconds) in TIME, TIMESTAMP and DATETIME!

In a project I was on, timestamps are used for versioning of entities via the JPA @Version annotation. So it's essential for correct optimistic locking, otherwise changes with a millisecond or bigger difference won't get a stale data exception. Since MySQL 5.6.4 milliseconds (fractional seconds) are supported by the newly introduced type TIMESTAMP(fsp), where fsp stands for fractional seconds precision. fsp ranges from 0 to 6 with 0 indicating there's no fractional part.

This blogpost describes how to migrate your current MySQL 5.5 version to 5.6.4, get it working correctly with fractional seconds support and problems that occurred.

Setup

- MySQL 5.5
- JPA 2.0
- Spring 3.1.2
- Hibernate 4.2
- Ubuntu 12.04
- Tomcat 7

JPA @Version annotation used for all entities:
@Version
@Column(nullable = false)
@Temporal(TemporalType.TIMESTAMP)
private Date version;

Possible solutions

1. Put in an interceptor for Hibernate such that milliseconds are removed before inserting. But then you only have optimistic locking correctly working for updates that don't occur within a second. More details here. 
2. Change the version field into a long, so don't store it as a TIMESTAMP. 
3. Try to put something else smart in the application logic itself. 
4. Put in a columnDefinition on the column: http://stackoverflow.com/questions/811845/setting-a-jpa-timestamp-column-to-be-generated-by-the-database
5. Upgrade to 5.6.4.

Selected Solution

Together with the Product Owner it was decided to go for option 5. That means the minimal MySQL requirement for the application had to be upped to 5.6.4. That was acceptable; if a company is a MySQL shop, then usually they don't mind using a new(er) version of MySQL.
Option 4 was dismissed because that introduced a MySQL database dependency, which that project tries to avoid as much as possible.

Implementation

1. Upgrade MySQL from 5.5 to 5.6.4. Basically following the steps from here: http://www.peterchen.net/2013/02/20/en-how-to-install-mysql-5-6-on-ubuntu-12-04-precise/
   The exact MySQL version I used was: http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.13-debian6.0-x86_64.deb/from/http://cdn.mysql.com/


2. But a few more things are needed. For one thing, you need an up-to-date Connector/J, at least 5.1.24. Follow these steps to upgrade it:
1. Download it from: http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.26.zip/from/http://cdn.mysql.com/
2. Stop Tomcat
3. Remove the old version:
   
   sudo rm /usr/share/tomcat7/lib/mysql-connector-java-5.0.8-bin.jar
4. Put in the new version:
   
   sudo cp mysql-connector-java-5.1.26/mysql-connector-java-5.1.26-bin.jar /usr/share/tomcat7/lib/
5. Restart Tomcat
6. Done.


3. Now you need to make sure columns are created as TIMESTAMP(fsp) to get the fractional precision, instead of TIMESTAMP which Hibernate uses by default. Decided was to go for millisecond accuracy, so TIMESTAMP(3). For that, a Hibernate dialect resolver was put in place that registers another column type for java.sql.Types.TIMESTAMP:
   
   public class FixMysql5Dialect extends MySQL5Dialect {
   
       public FixedMysql5Dialect() {
           registerColumnType(java.sql.Types.BOOLEAN, "bit");
           registerColumnType(java.sql.Types.TIMESTAMP, "timestamp(3)");
       }
   }
   Note also the boolean fix :)
   
   The resolver:
   ...
   private final DialectResolver standardDialectResolver = new StandardDialectResolver();
   
   @Override
   
   public Dialect resolveDialect(DatabaseMetaData metaData) throws JDBCConnectionException {
   
   Dialect dialect = standardDialectResolver.resolveDialect(metaData);
   
   if (dialect instanceof MySQL5Dialect) {
   
   return new FixedMysql5Dialect();
   
   }
   return dialect;
   .....


4. Configure it in the Spring JavaConfig:
   
   map.put("hibernate.dialect_resolvers", MyHibernateDialectResolver.class.getName());


5. Done

Troubleshooting

• Problem: After restarting the application and loading the pre-existing testdata, an exception started occuring:
  
  java.sql.SQLException: Value '0000-00-00 00:00:00.000' can not be represented as java.sql.Timestamp
• Cause: the SQL import scripts were using:
  
  INSERT INTO ct (ID, VERSION, NAME) VALUES (1, '1970-01-01 00:00:00.001', 'a');
  
  Looking in the database, it turns out the import wasn't able to be converted correctly anymore! They all appeared as:
  
  | 1 | 0000-00-00 00:00:00.000 | a
  
  After trying out a couple of combinations, it turns out that "it" definitely doesn't like 1 january 1970!! All below inserts do work:
  
  '2013-08-05 12:32:34.233'
  '2000-01-01 00:00:00.233'
  '1999-01-01 00:00:00.233'
  '1999-01-01 00:00:00.001'
  '1990-01-01 00:00:00.001'
  '1971-01-01 00:00:00.001'
  
  Also failing are '1970-01-01 00:00:00.000' and '1500-01-01 00:00:00.001'.
  
  If you watch carefully on the (incorrect) inserts, you see there's a warning (put in red for clarity):
  
  INSERT INTO ct (ID, VERSION, NAME) VALUES (19, '1970-01-01 00:00:00.001', 'Test19');
  Query OK, 1 row affected, 1 warning (0.01 sec)
  To see the exact warning execute:
  
  show warnings\G
  Then you see:
  
  *************************** 1. row ***************************
  Level: Warning
  Code: 1264
  Message: Out of range value for column 'VERSION' at row 1
  So the entered values are just not valid. Couldn't find out why they are not accepted, if you find it, leave it in the comments!!
  
  In the scripts we decided to use '1971-01-01 00:00:00.001' for the inserts.

JPA @Version annotation: when is the version updated?

In JPA you can annotate a field of an entity with @Version to specify that property as its optimistic lock value.
But the above documentation does not say when the version number is validated and updated (increased).
The short answer is: at commit time!

I came across this because I was having trouble passing on the updated version number back to the caller, after a merge() operation.

The setup

• Spring 3.1.2
• JPA 2.0
• Hibernate 4.2

Example and the solution

A client class (not in a transaction) invokes the method 

   /** 

    * Updates the order and returns the new version number for the order

    *

    * @param order the entity to update. Must be provided with the current version of the order.

    *             If not provided, StaleObjectStateException will be thrown

    */

   Date updateMe(Order orderWithNewValues);

on a service class, which is annotated with Spring @Service and Spring @Transactional. 

That method invokes 

   /** 

    * Similar java doc

    */

   Date updateMe(SomeEntity orderWithNewValues)

in a DAO which is annotated with Spring @Transactional(propagation = Propagation.MANDATORY) and @Repository.  The DAO searches for the entity to update using its name, sets the version number to the currently known version number, updates it and should return the new version number.

The class Order is annotated (amongst other things) with @Version as follows:

   @Version

   @Column(nullable = false)

   @Temporal(TemporalType.TIMESTAMP)

   private Date version;

   ...

   DateTime getVersion() {

       return version;

   }

The implementation of the DAO method implementing the update originally was:

   Order order = orderDao.findByName(name);

   if (order == null) {

       throw new IllegalArgumentException("No order with name '" + name + "' exists");

   }

   Order updatedOrderNotManaged = new Order(order);

   updatedOrderNotManaged.setVersion(orderWithNewValues.getVersion());

   updatedOrderNotManaged.setTotal(orderWithNewValues.getTotal());

   Order updatedOrderManaged = orderDao.merge(updatedOrderNotManaged);

   return updatedOrderManaged.getVersion();

Expecting that the last line (return updatedOrderManaged.getVersion()) would return the new version number, since the non-managed entity was now managed.

But it isn't! What seems to be the reason is that:

• The JPA implementation (in this case Hibernate) can do some caching, deferring the actual database access to a later moment, causing the changes to not hit the database yet.
• Not hitting the database also doesn't cause the @Version to be "triggered" (it seems).
• So the version is still at its old value!

But after the service method had returned the (old) version to the client calling it (so the transaction was committed), in the database you can see the new increased timestamp in the version column.

The solution is to force the JPA implementation to flush() to the database, such that the @Version annotation gets triggered and thus the version gets updated.

Therefor the following was added, just before the "return"  statement: 

   orderDao.flush();

That did it, now return updatedOrderManaged.getVersion()) is returning the updated version number. Should the transaction commit fail later, then no harm is done since the whole transaction is rolled back and the client should get an exception or similar so it knows it should ignore the returned version number.