Hibernate, MySQL 5.6 and the millisecondsTiny but tedious

Split seconds are the reason for a compatibility problem between the MySQL JDBC driver Connector/J and MySQL 5.6. They kept me quite busy recently.

Following a database update from MySQL 5.5 to 5.6.4, suddenly, refreshing the datasets within one appNG-based application wasn't working any more.

Summing up the problem's anatomy: With our JPA entities, we use one column version of the type datetime for optimistic locking through @Version, meaning that prior to each UPDATE, we verify that the most recently known version in the EntityManager corresponds to the one in the database. Starting with MySQL 5.6.4, fractional seconds with up to six decimal places (microseconds) are supported for datetime. In that process, datetime(0) is the default value:

A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

The MySQL JDBC driver (Connector/J) is responsible for "translating" the JDBC statements into native SQL. With all versions prior to 5.1.23, milliseconds are always truncated - a bug that has already been discussed under MySQL Bug #60598.

The nativeSQL() method would always truncate fractional seconds rather than preserving the fractional part in the output string. Now Connector/J checks the server version: it preserves the fractional part for MySQL 5.6.4 and greater, and truncates the fractional part for older versions. (Bug #14748459, Bug #60598)

If a MySQL version greater than 5.6.4 is used together with a Connector/J version greater than 5.1.23, the following will happen within one transaction: The dataset is created in a version containing milliseconds.

INSERT INTO table (version,...) VALUES ('2015-11-14 17:05:47.618',...)

The entity's internal version number in the EntityManager, consequently, contains milliseconds, too. They do not land in the database, though. In case of an UPDATE to the data set, it tries to be identified based on its id and version.

UPDATE table set ... where id=10 and version='2015-11-14 17:05:47.618'

This update fails, since the column "version" is of the type datetime(0), hence doesn't support milliseconds and the condition version='2015-11-14 17:05:47.618' is not fulfilled.

This leads to the notorious OptimisticLockException:

javax.persistence.OptimisticLockException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)

Tip: Since with Hibernate (as a JPA implementation) it is hard/laborious to get to the SQL actually generated, I recommend the JDBC-Performance-Logger for that. Not only does it show the SQL statements, but is also a huge help in identifying slow queries.

The solution to the problem, therefore, is to either add milliseconds to the version column or use a driver prior to 5.1.23.

But since we don't feel comfortable using an outdated driver for any lenght of time, let alone one that might still be using the forbidden SET OPTION statement, the version columns were updated to datetime(3).

Conclusion: Even with seemingly minor updates of MySQL and the JDBC driver, one should always study the Release Notes carefully in order to identify potential problems in good time.

Postscript

Interestingly enough, this problem had been brought to Hibernate's attention a long time ago, as substantiated by Ticket HHH-3822. This ticket, though, has never been dignified with a response by the Hibernate team. It was five years later, with the solution of  HHH-8401, that it was closed without comment.

Are you interested? Please get in touch!