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.