Strange DATETIME behaviour with Hibernate and MySQL 5.6

Question

I updated MySQL 5.1 Community server to 5.6. After this I am experiencing strange DATETIME (hibernate type timestamp) behaviour. For some reason my dates change from (for example) '2012-09-30 23:59:59' to '2012-10-1 00:00:00' after saving the hibernate mapped object. My log says that I am indeed saving the one second before midnight Date but when I look at the database it has changed to the beginning of the next day. If I make an INSERT query with the same date it works without problems.

According to MySQL documentation there shouldn't be any time zone conversions with DATETIME. I also tested with MySQL 5.5 and I couldn't reproduce the same problem.

My hbm mapping looks like this:

<composite-id>
    ...
    <key-property name="timestamp" type="timestamp" column="timestamp"/>
</composite-id>

EDIT: I also have the latest MySQL jdbc driver.

EDIT 2: As you can see the date changes.

22.04.13 12:04:54.149 DEBUG SQL:104 - insert into data_table (col_1, col_2, timestamp) values (?, ?, ?)
22.04.13 12:04:54.149 TRACE BasicBinder:83 - binding parameter [1] as [DOUBLE] - 1.0
22.04.13 12:04:54.149 TRACE BasicBinder:83 - binding parameter [2] as [INTEGER] - 1
22.04.13 12:04:54.150 TRACE BasicBinder:83 - binding parameter [3] as [TIMESTAMP] - Mon Apr 22 23:59:59 EEST 2013
22.04.13 12:04:54.151 ERROR SqlExceptionHelper:144 - Duplicate entry '1-2013-04-23 00:00:00' for key 'PRIMARY'

EDIT 3: Problem reproduced with Hibernate versions 3.3.1 and 4.1.9.

Answer 1

I think I found the answer. The date I used included milliseconds and it seems that in MySQL 5.6 you have to define the accuracy for a DATETIME. Without defining the number of fractional desimals it defaults to 0. In this case '2013-04-01 23:59:59.500' rounds to '2013-04-02 00:00:00'. I managed to reproduce this with a sql query so it has nothing to do with Java or Hibernate.

http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

I changed a row in my code from

calendar.set(GregorianCalendar.MILLISECOND, 999);

to

calendar.set(GregorianCalendar.MILLISECOND, 0);

and the problem went away.