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.

Refactor/rename package in Eclipse with Subclipse and SVN

Refactoring a package by giving it a new name can give quite annoying problems when trying to commit it from within Eclipse using the Subclipse plugin.

You can get (tree) conflicts, maybe even causing you to rollback all your changes and then step by step committing them.

Below is a set of steps you can follow that should not cause any problems if executed in this order :) In short: you do your refactor, then first commit only the deletions, and only then commit the additions (which are the moved files in their new package location).

Detailed steps

Set up and tested with: Eclipse Juno version, Subclipse plugin, SVN 1.6:

1. Rightclick on your project and select Team --> Synchronize with Repository
2. Select the incoming view
3. Now note in the incoming view, it shows conflicts on some/most directories (packages) that were renamed. Just do an update on those.
4. Go to the outgoing view
5. If you now select all items with a '-' (the deleted ones) and then try to commit, you get an error on the commit: Tree remains in conflict. Notice the red/green icons on several directories (actually the ones that had a conflict in step 3), see screenshot:
   
   
   
   So those need to be resolved first.
6. Right click on one of those entries (multiple select not supported for the Show Tree Conflicts operation) and select Team --> Show Tree Conflicts. That shows something like:
   
   
   
7. Rightclick and select Resolve...
8. On that next screen:
   
   
   select only Mark conflict resolved.
9. Click Finish
10. Now the entry is not marked red/green any more, and only shown with a '-' in the Synchronize screen! 
11. Do that for all red/green arrows (conflicts)
12. When done, right-click on all items marked with a '-' and select Commit. And that succeeds!
13. Finally, commit now the ones marked with '+' (so the added ones)
14. Really final step: note that sometimes some empty packages with the old name stay around. Deleting them in Eclipse and then trying to commit gives again conflicts(!). Those I just delete directly from the Repository perspective and do an update on those.

Bonus tip: sometimes when another team member has commited a new module, the Synchronize perspective will show the (compiled) /target dir and its items as needing to be committed. Even though the directory is already in the .svnignore file, since right-clicking on it won't allow you to add it. Solution: do an update on that target directory. After this it should be fine and it should not show up anymore on the next Synchronizes.