Wednesday, August 21, 2013

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.


- 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:
@Column(nullable = false)
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:
  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.


  1. Upgrade MySQL from 5.5 to 5.6.4. Basically following the steps from here:
    The exact MySQL version I used was:

  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:
    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();


    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


  • 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.


Mike Miller said...

Just found your blog entry while fighting this same problems. We support MySQL 5.5 but I got a new laptop and decided to try 5.6 and started getting errors for all our datetime fields. We define as 'datetime' not timestamp.

Can this be done without changing the dialect resolver? I haven't touched those in the past.

I created a straight-forward jdbc prepared statement example and get the same error without Hibernate being involved, which makes me thing it isn't really a Hibernate issue.

Techie said...

@Mike Miller: which problem do you exactly mean? The milliseconds or the 1970-01-01 problem? Or both? The insert of 1970-01-01 does happen when you do it on the MySql command line, so that problem can't be Hibernate indeed. The dialect resolver is indeed a Hibernate thing, it is used to determine how it maps Java types on database/DDL types; haven't looked for other possible solutions...

omw said...

A nice example of how to implement my own Hibernate Dialect.

The precision can be parameterized (MySQL supports microseconds precision) by using $p:

registerColumnType(java.sql.Types.TIMESTAMP, "timestamp($p)");

Example mapping:

The inserts above fails due to the limitation of the timestamp data type. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' according to

Use datetime to support a larger date range ('1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999')

registerColumnType(java.sql.Types.TIMESTAMP, "datetime($p)");