Search code examples
mysqlspringhibernatecriteria

Hibernate Criteria query does not work with datetime columns after migration to MySQL 8

After migrating our DB from MySQL 5.7 to 8.0, we have noticed that queries including datetime columns doesn't work properly, particularly with equal comparisons.

The code that worked before the migration is the following:

Criteria crit = dao.getSession().createCriteria(IncidenceEntity.class);
crit.createAlias("event", "event");
crit.add(Restrictions.eq("creationDate", creationDate));
crit.add(Restrictions.eq("event.id", event));
IncidenceEntity result = (IncidenceEntity) crit.uniqueResult();

This is used when we don't have the entity id so we search for the specific same datetime. It should return a single row, but now doesn't return any result.

Searching without the date and re-searching with the obtained date also returns 0 results, so it definitelly is not a problem of not being detailed enough with the datetime

Criteria crit = dao.getSession().createCriteria(IncidenceEntity.class);
    crit.createAlias("event", "event");
    crit.add(Restrictions.eq("event.id", eventId));
    List<IncidenceEntity> resultList = (List<IncidenceEntity>) crit.list();
    crit = dao.getSession().createCriteria(IncidenceEntity.class);
    crit.createAlias("event", "event");
    crit.add(Restrictions.eq("creationDate", resultList.get(0).getCreationDate()));
    crit.add(Restrictions.eq("event.id", eventId));
    
    IncidenceEntity result = (IncidenceEntity) crit.uniqueResult(); // this is null

During the migration we changed the Connector/J and updated it to last version of 5.1, since we cannot use Connector/J 8.0 yet because it requires Java 8+. We were aware that the migration from MySQL 5.7 to 8.0 implies some issues with dates but the Upgrade Checker doesn't saw any problem.

Our Hibernate version is 3.2.3, Java 6 and Spring 3.1.2.

Solution

  • It seems that the issue in MySQL 8 is that the date columns has no milliseconds precision (eg. 2018-11-01 21:11:34) and the data we send has milliseconds so, while in MySQL 5 this info is truncated, in MySQL 8 seems like it's rounded up.

    We solved the issue changing the datetime columns, adding milliseconds.

    ALTER TABLE INCIDENCE CHANGE CREATION_DATE CREATION_DATE TIMESTAMP(3);