Bug #85570 java.sql.Date persisted to MySQL as 1 day less after Mar 12, 2017

• Submitted: 21 Mar 2017 18:49
• Modified: 22 Mar 2017 15:05
• Reporter: Peter McGill
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 6.0.5, 6.0.6
• OS: Windows (Windows 10)
• CPU Architecture: Any

[21 Mar 2017 18:49] Peter McGill

Description:
I have a production application using Java + MySQL.

On Feb 13th, with no code or library changes it started saving java.sql.Date as 1 day less.

For example Date of 2017-03-12 or older saves to MySQL correctly.
However, Date of 2017-03-13 saves to MySQL as 2017-03-12,
and Date of 2017-03-14 saves to MySQL as 2017-03-13, etc...

In the following sample test only the final assert fails:
java.lang.AssertionError: expected date [2017-03-13] but found [2017-03-12]

I write 2017-03-13 to MySQL, but it returns 2017-03-12.

How to repeat:
import java.sql.*;
import java.time.*;
import org.testng.annotations.Test;

public class DateTest {
    private String url = "jdbc:mysql://monarch.corp.goco.net:3306/test";
    private String username = "test";
    private String password = "test";

    @Test
    public void testLocalDate() throws Exception
    {
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            PreparedStatement statement = connection.prepareStatement(
                    "SELECT '2017-03-12', '2017-03-13', ?, ?, ?, ?;");
            Date twelfth = new Date(LocalDate.of(2017, 3, 12).atStartOfDay(
                    ZoneId.systemDefault()).toInstant().toEpochMilli());
            Date thirteenth = new Date(LocalDate.of(2017, 3, 13).atStartOfDay(
                    ZoneId.systemDefault()).toInstant().toEpochMilli());
            assert "2017-03-12".equals(twelfth.toString()) :
                    "expected string [2017-03-12] but found [" + twelfth.toString() + "]";
            assert "2017-03-13".equals(thirteenth.toString()) :
                    "expected string [2017-03-13] but found [" + thirteenth.toString() + "]";
            statement.setString(1, twelfth.toString());
            statement.setString(2, thirteenth.toString());
            statement.setDate(3, twelfth);
            statement.setDate(4, thirteenth);
            ResultSet resultSet = statement.executeQuery();
            assert resultSet.next() : "missing result set";
            String string;
            assert "2017-03-12".equals(string = resultSet.getString(1)) :
                    "expected string [2017-03-12] but found [" + string + "]";
            assert "2017-03-13".equals(string = resultSet.getString(2)) :
                    "expected string [2017-03-13] but found [" + string + "]";
            assert "2017-03-12".equals(string = resultSet.getString(3)) :
                    "expected string [2017-03-12] but found [" + string + "]";
            assert "2017-03-13".equals(string = resultSet.getString(4)) :
                    "expected string [2017-03-13] but found [" + string + "]";
            Date date;
            assert twelfth.equals(date = resultSet.getDate(5)) :
                    "expected date [2017-03-12] but found [" + date.toString() + "]";
            assert thirteenth.equals(date = resultSet.getDate(6)) :
                    "expected date [2017-03-13] but found [" + date.toString() + "]";
        }
    }
}

[22 Mar 2017 15:05] Peter McGill

I was unaware that you had to restart MySQL server after updating the system timezone information and/or changing between standard and daylight/summer time.

After restart and explicitly setting MySQL global timezone the problem has been resolved.

Although, I find it strange that Connector/J alters a java.sql.Date value according to timezone, despite MySQL documentation stating that DATE, TIME, and DATETIME columns are not altered by timezone, only TIMESTAMP is supposed to change.