MyBatis version

3.5.1

Database vendor and version

Mysql 8.0.11

Test case or example project

Example project. mybatis-bug.zip

Steps to reproduce

  1. Unzip project.
  2. Go to mybatis-bug/env folder and start mysql using docker-compose: docker-compose up.
  3. Go to root folder of project and run it: ./gradlew clean bootRun.

Expected result

--> DB: City{id=3, name='Kyiv', created=2019-05-04T14:38:00.756421}
<-- DB: City{id=3, name='Kyiv', created=2019-05-04T14:38:00.756}

Actual result

Field created is returned in UTC.

--> DB: City{id=2, name='Kyiv', created=2019-05-04T14:35:12.162898}
<-- DB: City{id=2, name='Kyiv', created=2019-05-04T11:35:12.163}

Seems like this commit introduced this issue: https://github.com/mybatis/mybatis-3/commit/963a8a577bc1d9a98e5182a7779a85a3ca834984

Comment From: harawata

Hi @moleksyuk , Thank you for the repro, it really saves our time. 👍

As you noticed, we changed the LocalDateTimeTypeHandler implementation in #1478 . Please see the issue for details.

With your test steps, the server time zone is UTC. And you (=client) seem to run the test on GMT+0300 time zone.

Although I couldn't find official statement, I think you are supposed to add serverTimezone=GMT%2B3 to the JDBC connection URL in this case. i.e.

jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B3

Without serverTimezone, you should be able to replicate the issue with a plain JDBC code. e.g.

try (Connection con = getJdbcConnection()) {
  try (Statement stmt1 = con.createStatement()) {
    try {
      stmt1.execute("drop table test");
    } catch (Exception e) {
      // 
    }
    stmt1.execute("create table test (id integer, t datetime(6))");
  }
  LocalDateTime ldt = LocalDateTime.now();
  int id = 1;
  try (PreparedStatement stmt = con.prepareStatement("insert into test (id, t) values (?, ?)")) {
    stmt.setInt(1, id);
    stmt.setObject(2, ldt);
    stmt.execute();
  }
  try (PreparedStatement stmt = con.prepareStatement("select t from test where id = ?")) {
    stmt.setInt(1, id);
    try (ResultSet rs = stmt.executeQuery()) {
      rs.next();
      System.out.println("--> DB: " + ldt);
      System.out.println("<-- DB: " + rs.getObject(1, LocalDateTime.class));
    }
  }
}

FYI, I tested various drivers and MySQL Connector/J is the only one (so far) that requires such extra configuration to make it work.

Anyway, please let us know if adding serverTimezone didn't resolve the issue or you found an official documentation that contradicts my explanation.

Comment From: moleksyuk

Hi @harawata

With your test steps, the server time zone is UTC. And you (=client) seem to run the test on GMT+0300 time zone. Yep, you are right.

I did some investigation and problem is actually related to mysql driver:

  • mysql:mysql-connector-java:5.1.47 - stores LocalDateTime to db as is, and it seems logical. Example: db time - UTC java app time - UTC+3 So, if for example LocalDateTime.now() == '2019-05-09 13:00:00 it will be stored in db as 2019-05-09 13:00:00. And of course during read it to LocalDateTime will return the same value.

  • mysql:mysql-connector-java:8.0.16 - stores LocalDateTime to db with convertion to db timezone 😠 . Why??? Example: db time - UTC java app time - UTC+3 So, if for example LocalDateTime.now() == '2019-05-09 13:00:00 it will be stored in db as 2019-05-09 10:00:00. And during read it to LocalDateTime will return 2019-05-09 10:00:00.

Close this ticket due to error in mysql driver.

Update: We switched to Instant and it works correctly. Don't use LocalDateTime!

Comment From: harawata

@moleksyuk , I thought it might be the designed behavior, but you are right. It is filed on their tracker as a bug. https://bugs.mysql.com/bug.php?id=93444

Comment From: harawata

MyBatis' InstantTypeHandler internally performs conversion between java.time.Instant and java.sql.Timestamp. You should check that the inserted value in the database matches what you expect. See this ticket for details.

Comment From: berniegp

I'm facing the same problem with MySQL Connector/J 8.0.17

While #1478 changed this:

  // LocalDateTypeHandler.java 
  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, LocalDate parameter, JdbcType jdbcType)
          throws SQLException {
-    ps.setDate(i, Date.valueOf(parameter));
+    ps.setObject(i, parameter);
  }

Unfortunately, MySQL Connector/J then does this (AbstractQueryBindings.java#L246):

            } else if (parameterObj instanceof LocalDate) {
                setDate(parameterIndex, Date.valueOf((LocalDate) parameterObj));

            }

which nullifies the gains from #1478 with regards to LocalDate with MySQL 8. This can be fixed with this custom type handler:

public class CustomLocalDateTypeHandler extends LocalDateTypeHandler {

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, LocalDate parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setDate(i, java.sql.Date.valueOf(parameter), Calendar.getInstance());
  }
}

until/if the MySQL Connector/J is fixed.

Relevant discussions: - https://github.com/jdbi/jdbi/issues/1446 - https://hibernate.atlassian.net/browse/HHH-11396

Comment From: berniegp

This is fixed in MySQL Connector/J 8.0.20 :

When a Calendar was not used, a java.sql.Date value could not always be stored into and then retrieved from a MySQL server consistently. It was because Connector/J always converted a Date value to the server's time zone when storing it on the server as a MySQL DATE; but since a MySQL DATE does not have any time value, the hour, minute, and second parts of the original date was effectively lost. If the converted value is one day ahead of or behind the original value, when the value was retrieved through Connector/J and converted back to the local time zone, there was no time value for adjusting the date back to its original value, resulting in a one-day error. With this fix, any Date value is converted to MySQL DATE value using the JVM's time zone, so that the value is always consistent when being stored and then read back.

Comment From: harawata

Thanks for the info @berniegp !

I did some tests and the fix made in mysql-connector-java 8.0.20 only affected the behavior of LocalDate (and java.sql.Date) and there still was a corner case like 2011-12-30 in Pacific/Apia.

In 8.0.22, bug #93444 has been fixed and LocalDate, LocalDateTime, LocalTime type handlers now should work as expected even when the client is on a different time zone than the server.

Comment From: wupwap

Thanks for the info @berniegp !

I did some tests and the fix made in mysql-connector-java 8.0.20 only affected the behavior of LocalDate (and java.sql.Date) and there still was a corner case like 2011-12-30 in Pacific/Apia.

In 8.0.22, bug #93444 has been fixed and LocalDate, LocalDateTime, LocalTime type handlers now should work as expected even when the client is on a different time zone than the server.

no,it does not.It still a mybatis bug.Becuase the old version LocalDateTimeTypeHandler get timestamp first,and the timestamp handler in jdbc can convert the timezone.But the new version LocalDateTimeTypeHandler direct get the value,so does not convert the timezone,so it is a bug and not fix yet.