Abstract

The time zone named CST is a confusing time zone, and Java will mistake cST-0500 for CST +0800 when negotiating the session time zone with MySQL.

CST time zone

The time zone named CST is a confusing time zone with four meanings:

  • Central Standard Time (USA) UTC-06:00
  • Central Standard Time (Australia) UTC+09:30
  • China Standard Time UTC+08:00
  • Cuba Standard Time UTC-04:00

Today is April 28th. Why date? Because the United States observes daylight saving time from March 11 to November 7, central Time in the United States is changed to UTC-05:00, which is 13 hours different from UTC+08:00.

Debugging process

In the project, it happened that the unix_TIMESTAMP () value of the Timestamp field stored in the database was 13 hours below its true value. A time zone negotiation problem was found in com.mysql.cj. JDBC through debugging trace.

When the JDBC and MySQL began to establish a connection, will be called com. MySQL. Cj. JDBC. ConnectionImpl. InitializePropsFromServer () to obtain the server parameters, Which we call this. See the session. ConfigureTimezone () function, which is responsible for configuration time zone.

public void configureTimezone(a) {
    String configuredTimeZoneOnServer = getServerVariable("time_zone");

    if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
        configuredTimeZoneOnServer = getServerVariable("system_time_zone");
    }

    String canonicalTimezone = getPropertySet().getStringReadableProperty(PropertyDefinitions.PNAME_serverTimezone).getValue();

    if(configuredTimeZoneOnServer ! =null) {
        // user can override this with driver properties, so don't detect if that's the case
        if (canonicalTimezone == null || StringUtils.isEmptyOrWhitespaceOnly(canonicalTimezone)) {
            try {
                canonicalTimezone = TimeUtil.getCanonicalTimezone(configuredTimeZoneOnServer, getExceptionInterceptor());
            } catch (IllegalArgumentException iae) {
                throwExceptionFactory.createException(WrongArgumentException.class, iae.getMessage(), getExceptionInterceptor()); }}}if(canonicalTimezone ! =null && canonicalTimezone.length() > 0) {
        this.serverTimezoneTZ = TimeZone.getTimeZone(canonicalTimezone);

        // The Calendar class has the behavior of mapping unknown timezones to 'GMT' instead of throwing an exception, so we must check for this...
        if(! canonicalTimezone.equalsIgnoreCase("GMT")
            && this.serverTimezoneTZ.getID().equals("GMT")) {
            throw. }}this.defaultTimeZone = this.serverTimezoneTZ;
}Copy the code

If the MySQL time_zone value is SYSTEM, system_time_zone is used as the coordinated time zone.

Let’s log in to the MySQL server to verify these two values:

mysql> show variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | Time_zone | SYSTEM | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

That’s the point! ifString configuredTimeZoneOnServerGet isCSTThen Java will mistake this forCST -0500, soTimeZone.getTimeZone(canonicalTimezone)Incorrect time zone information is given.

debug variables

As shown in the figure, the default time zone of the machine is Asia/Shanghai +0800. The time zone of the server is mistaken as CST-0500, but actually the server is CST +0800.

We would have thought that, even if the time zone there are misunderstandings, if the Timestamp is long said the Timestamp of the transmission, also won’t appear problem, let us track to com. The mysql. Cj.. JDBC PreparedStatement. SetTimestamp ().

public void setTimestamp(int parameterIndex, Timestamp x) throws java.sql.SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        setTimestampInternal(parameterIndex, x, this.session.getDefaultTimeZone()); }}Copy the code

This notice here. The session. GetDefaultTimeZone () is just the CST – 0500.

private void setTimestampInternal(int parameterIndex, Timestamp x, TimeZone tz) throws SQLException {
    if (x == null) {
        setNull(parameterIndex, MysqlType.TIMESTAMP);
    } else {
        if (!this.sendFractionalSeconds.getValue()) {
            x = TimeUtil.truncateFractionalSeconds(x);
        }

        this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = MysqlType.TIMESTAMP;

        if (this.tsdf == null) {
            this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US);
        }

        this.tsdf.setTimeZone(tz);

        StringBuffer buf = new StringBuffer();
        buf.append(this.tsdf.format(x));
        if (this.session.serverSupportsFracSecs()) {
            buf.append('. ');
            buf.append(TimeUtil.formatNanos(x.getNanos(), true));
        }
        buf.append('\' '); setInternal(parameterIndex, buf.toString()); }}Copy the code

Timestamp is converted to the time string of the session time zone. The question is clear:

  1. JDBC mistakenly thinks the session time zone is cST-5
  2. Timestamp+0 = string-5
  3. MySQL considers session time zone at CST+8, converts string-5 to Timestamp-13

The final results were 13 hours apart! That’s 14 hours in winter!

The solution

The solution is also simple: specify the time zone of the MySQL database explicitly, and do not use the misleading CST:

mysql> set global time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> set time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)Copy the code

Or modify the my.cnf file to add default-time-zone = ‘+08:00’ under section [mysqld].

You need to restart the MySQL server. You are advised to change the time zone during maintenance.