LocalDate MySQL issue - wrong day persisted

Support
1

There seems to be a problem with the LocalDate in a MySQL database. Depending on the set time zone in the application the persisted date is wrong. LocalDate AFAIK should not be affected by time zones at all. It should just use exactly the date provided and store it, but this is not the case.

This is quite difficult to track down, so I have made a test project that shows the problem in a unit test. Docker is required for this, as it spins up a testcontainers mysql database. Please run the test in TimezoneMysqlBugApplicationTests and have a look at the @BeforeEach and the @Test methods.

timezone-mysql-bug.zip (81.4 KB)

I guess the problem is somewhere in the JPA (EclipseLink) implementation.

EDIT: Ok, the problem is probably not EclipseLink - following SQL is found in the logs:

08:35:37.671 DEBUG eclipselink.logging.sql       - <t 619529602, conn 300612436> INSERT INTO TEST_LOCAL_DATE_ENTITY (ID, LOCAL_DATE, NAME) VALUES (?, ?, ?)
	bind => [a3dff467875b43da67794d629ea51441, 2022-02-01, Test1]

(when provided a 2022-02-01 date)

1 Like
2

@klaus We added an (old mysql 5.5) database as secondary datastore for among other things data migration.

You added the timezone identifier to the JDBC (as we did) as well. Does it match the timezone on MySQL server side?

3

You added the timezone identifier to the JDBC (as we did) as well. Does it match the timezone on MySQL server side?

Yes. For the tests, the docker containers should run in UTC time zone and my production database server is also set to UTC, where I can reproduce the issue. But even if they don’t match, this shouldn’t be an issue, because LocalDate should always be a date without a time zone.

4

@klaus Ok, our case is a little different.

  1. We are operating with local- and offset date time and the database is not generated by Jmix/Liquibase

  2. We are using these entities in read-only context - not storing to the MySQL database at all

    @Column(name = “date”, columnDefinition = “DATETIME”)
    private LocalDateTime datum;

    @Column(name = “date_sql”, columnDefinition = “TIMESTAMP”)
    private OffsetDateTime datumSQL;

Sorry, for confusing your issue.

5

Ok I think I finally got it.

  • The mysql connector is able to return LocalDate, but it is not used - java.sql.date is used.
  • This uses a time zone for conversion to local date.
  • The time zone is cached in the server session of the mysql connector.
  • If you change the time zone “on the fly” (in my case it was application startup, but already too late) the time zone is already cached in the connector.
  • Thus it will convert to a false LocalDate

org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor uses java.sql.ResultSet#getDate(int) but could use the newer com.mysql.cj.jdbc.result.ResultSetImpl#getLocalDate when used with mysql connector 8

→ You can append cacheDefaultTimeZone=false to the JDBC query string and all tests pass.

2 Likes