Bug #85816 MySQLConnector/J cannot handle server timezone "PDT" for timestamp fields
Submitted: 5 Apr 2017 18:29 Modified: 21 Apr 2017 23:44
Reporter: Gisella Saavedra Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.x, 6.x OS:Any
Assigned to: Filipe Silva CPU Architecture:Any
Tags: timezone PDT timestamp

[5 Apr 2017 18:29] Gisella Saavedra
Description:
1. Env: Java 8, MySQL Server 5.1, MySQL Server 5.6

2. A MySQL server set to have timezone = SYSTEM, in California produces the following:
SELECT @@session.time_zone, @@system_time_zone;
SYSTEM	        PDT

3."PDT" is a world-known timezone.

4. "PDT" is not a valid timezone for Java 8, neither for the mappings defined in 
Connector/J 5.1.x:
mysql-connector-java-5.1.41-sources/com/mysql/jdbc/TimeZoneMapping.properties
Connector 6.x:
/mysql-connector-java-6.0.6-sources/com/mysql/cj/jdbc/util/TimeZoneMapping.properties

5.  So, the timezone defaults to client since timezone for server cannot be resolved.
Connector/J 5.1.x:
mysql-connector-java-5.1.41-sources/com/mysql/jdbc/ConnectionImpl.java:configureTimezone()
Connector 6.x:
mysql-connector-java-6.0.6-sources/com/mysql/cj/mysqla/MysqlaSession.java:configureTimezone()

6). If client is in a different timezone from MySQL Server, and client has started with config property 
useLegacyDatetimeCode = false
timestamp fields display incorrectly.

7. Expected:
That a "PDT" mapping to "America/Los_Angeles" be added to TimeZoneMapping.properties so that the timestamp fields are handled correctly.

How to repeat:
1. Have a MySQL server set up, with either
a). California, now that the timezone is PDT
b). Set the timezone = PDT

2). Create a table with a timestamp field and enter some values.

3). Start a client app, 
a). where the timezone is different, say UTC. 
b). Set MySQL connector property: useLegacyDatetimeCode = false

3). Display the timestamp fields in UTC.

4). Values show incorrect.

Suggested fix:
That a "PDT" mapping to "America/Los_Angeles" be added to TimeZoneMapping.properties

Connector/J 5.1.x:
mysql-connector-java-5.1.41-sources/com/mysql/jdbc/TimeZoneMapping.properties
Connector 6.x:
/mysql-connector-java-6.0.6-sources/com/mysql/cj/jdbc/util/TimeZoneMapping.properties
[5 Apr 2017 19:21] Gisella Saavedra
In section "Description," correction for (2):
SELECT @@time_zone, @@system_time_zone;
SYSTEM	        PDT
[6 Apr 2017 8:21] Chiranjeevi Battula
Hello Gisella,

Thank you for the bug report.
We should recommend you to set the server time zone to UTC and change the locale accordingly (https://dev.mysql.com/doc/refman/5.7/en/locale-support.html) or
you can keep the default time zone in server and force the client to use one that C/J recognizes by setting the property 'serverTimezone', for example "serverTimezone=Europe/Amsterdam"

Thanks,
Chiranjeevi.
[6 Apr 2017 15:23] Gisella Saavedra
1. I do not see what the locale has to do with the issue in question.

2. It seems you are not familiar with MySQL Connector/J in depth, for 5.x you HAVE to set the useLegacyDatetimeCode to at least be able to have the server timezone set; otherwise, the client time zone will always be used, and the timestamp conversion of timezones will fail.
[21 Apr 2017 23:44] Filipe Silva
Hi Gisella,

Thank you for your interest in MySQL and Connector/J.

Note that Connector/J 5.1 provides two ways of performing time zone adjustments on temporal data, the legacy mode (default) and non-legacy mode. In both modes you can set the server timezone in the connection string, which overrides the values defined in the server. So, you can either set "useLegacyDatetimeCode=true" (you can omit it as this is the default) and "useTimezone=true", or set "useLegacyDatetimeCode=false" to instruct the driver to read and process the server time zone. In case of an unknown time zone or impossible to map you'll get an exception. Not using one of those options falls back to the client time zone and disables time zone adjustments altogether.

What you want to do can be accomplished in two ways: either set the server time zone to "America/Los_Angeles" or set "serverTimezone=America/Los_Angeles" in your connection string in the client, in conjunction to one of the modes I mentioned before. Either way I have to warn you against this because of the risk of getting wrong values in the gaps caused by DST swaps (mind that MySQL doesn't store zone info in any of the supported temporal data types so the same clock time in one of the overlapping periods could mean two different instances). As such, what I recommend you is to set your server with a non DST aware time zone, such as UTC, and then let all adjustments be made between the client(s) time zone and this neutral time zone.

As of your suggested fix by adding a mapping from "PDT" to "America/Los_Angeles" this is something we simply can't do. This is so because we base our time zone mappings data on the information available in the official time zones databases - the IANA Time Zone Database and the Unicode CLDR - and, although the abbreviation "PDT" is valid, it also maps to multiple time zones, in several different periods of time, as such, there's no way for us to know exactly what mapping should be used.

Specifically, "PDT" maps to "America/Los_Angeles", "America/Juneau", "America/Sitka", "America/Metlakatla", "America/Boise", "America/Vancouver", "America/Dawson_Creek", "America/Fort_Nelson", "America/Inuvik", "America/Whitehorse", "America/Dawson" and "America/Tijuana". So, it is impossible for us to know which one of those zones should be used, and mind that choosing any wouldn't work either because ultimately they have different DST rules in different periods of time. For example, if you had happened to be on the time zone "America/Dawson" and you were handling dates before 1973, you wouldn't want to have you dates adjusted by the rules of "America/Los_Angeles" because they simply weren't compatible back then.

The abbreviations you see mapped in our TimeZoneMapping.properties are the ones that uniquely correspond to a single time zone, as such as can safely assume the replacement, and this is the only reason we keep those.

Unfortunately, there is nothing we can do with regard to your claims.

My apologies for taking so long replying.

Thank you,
[14 Jun 2017 13:49] Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=86425 marked as duplicate of this one.
[27 Jun 2018 17:40] Andrew Williams
Severity non-critical? That's a laugh.

It used to work pre-Connector/J, now it's been borked.
[27 Jun 2018 18:30] Filipe Silva
Hi Andrew,

Can you define "used to work pre-Connector/J"?
[2 Jul 2018 19:21] Todd Farmer
Just a note that this appears to introduce incompatibilities between C/J 5.1 and 8.0.  Given the same MySQL Server deployment, connections fail using C/J 8.0 where using C/J 5.1 succeeds.  I don't find this called out in the documentation:

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-whats-new.html

It might be useful to do so, plus any other considerations that users should be aware of prior to replacing C/J 5.1 libraries with C/J 8.0.
[5 Jul 2018 21:30] Andrew Williams
@Filipe Silva

By used to work means this.

Our system was using the 5.1.46 connector. We (generally) update things on a cycle so we keep near enough to the bleeding edge.

Not expecting any problems, we upgraded to connector/j 8.0.11

And then a swathe of errors all whining about the timezone came bounding at us. Now, we've never had to play with timezones at all. So, quelle surprise, when the flood of timezone errors appeared. So, we were not happy, at the prospect of trying some sort of manual workaround. Just rolled back the connector/j facility, threatened it with a baseball bat, luger and bad breath.
[9 Jan 15:16] kriti suwalka
We are facing the same issue. PDT timezone used to work(at least while establishing connection) with Connector/J 5.x versions but after upgrading to Connector/J 8.0.12 connection itself started failing with following error

The server time zone value 'PDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

Wondering if timezone specific checks are introduced during connection phase itself which is causing connection to fail for us.
The what's new article https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-other-changes.html talks about connector/J performing time offset adjustments on date-time value by default and always but it doesn't say anything about connection failure if two conditions mentioned in the article are not met. Connection used to pass with 5.x series driver so behavior seen with 8.x is definitely a change in behavior and should be part of what's new documentation.
[10 Jan 11:41] Umesh Shastry
Bug #93871 marked as duplicate of this one
[10 Jan 12:18] Filipe Silva
Hi Kriti,

The behavior is the same in Connector/J 5.1 if you set the connection property `useLegacyDatetimeCode=false`.

Since Connector/J 8.0 no longer contains such legacy code, the default behavior is to apply time zone adjustments all times, as described in the documentation. In order to do so, Connector/J needs to understand what time zone is configured in the server side and that is done at connection time. So, the behavior of failing to connect if unable to recognize the server's time zone hasn't changed, the only thing that actually changed was the default setting for time zone adjustments mode.
[10 Jan 15:51] kriti suwalka
Thanks Filipe.
So we set the serverTimezone property to "Pacific Daylight Time" as per documentation but it connection is still failing with "No timezone mapping entry for 'Pacific Daylight Time'" error. We extracted the mysql-connector-java-8.0.12.jar file and check that com\mysql\cj\util\TimeZoneMapping.properties does have an entry for "Pacific Daylight Time" (check following entry)
Pacific\ Daylight\ Time=America/Los_Angeles

So We expected it to map Pacific Daylight Time to America/Los_Angeles as per the mapping and the connection to go through fine but it is failing with no entry found error. 
Could you clarify how this mapping in TimeZoneMapping.properties works and why it is complaining about no entry found even if an entry is there ?
[10 Jan 17:17] Filipe Silva
Hi Kriti,

The value you set in `serverTimezone` must be a time zone Java recognizes, for example `serverTimezone=America/Los_Angeles` is valid option.

The mappings in `com\mysql\cj\util\TimeZoneMapping.properties` are used only when Connector/J detects the server time zone automatically, i.e., from the server variables `time_zone` or `system_time_zone` (check what you have with `show variables like '%time_zone';`). Connector/J starts by reading the time zone from `time_zone` and, if this value equals to "SYSTEM", then it falls-back to whatever is in `system_time_zone`.

Depending on your settings on server or on the operating system the server is running, these variables sometimes have non-standard values. For example, if I'm not mistaken, in Windows systems the value for `system_time_zone` could be "Pacific Daylight Time" and such, which is not a standard time zone denomination. So, the mapping file is just our best effort to try to understand those non-standard time zones.

When you set the connection string `serverTimezone` you are simply overriding the server time zone detection by telling Connector/J what is the time zone it should assume the server has. It's kind of doing the mapping I described before but manually providing the time zone to which (not from) to map.

IHTH
[12 Jan 16:18] kriti suwalka
Hi Filipe,

Thanks for the explanation.

But I still have a question. It would be great if you could help me with that.

As you said my server's "time_zone" property is set to system so it falls back to "Pacific Daylight Time". Now if I don't set `serverTimezone` connection property in my connection string then it should use `com\mysql\cj\util\TimeZoneMapping.properties`file for the mapping. Since properties file have a mapping defined for "Pacific Daylight Time" and it maps it to America/Los_Angeles, shouldn't it map the timezone to America/Los_Angeles automatically for my connection? And since America/Los_Angeles is recognized by java, shouldn't my connection be successful ?

Please correct me if my understanding is wrong here.
[14 Jan 10:27] Filipe Silva
Hi Kriti,

> As you said my server's "time_zone" property is set to system so it falls back to "Pacific Daylight Time".

No. I said it falls-back to whatever is set in server variable `system_time_zone`. What does this return in your server: "SHOW GLOBAL VARIABLES LIKE '%time_zone';"?

Note that I mean literally "Pacific Daylight Time", not the abbreviated form "PDT". I already explained in this report why we can't map "PDT" to "America/Los_Angeles", but with regard to "Pacific Daylight Time" we do it because there is not much else to do according to the standards, which you can consult here (http://unicode.org/repos/cldr/trunk/common/supplemental/windowsZones.xml -- search for "Pacific Standard Time"). It is also true that "Pacific Standard Time" is used in multiple territories but, in this case, we just go with the generic main one, i.e., "territory=001".

IHTH
[14 Jan 16:09] kriti suwalka
Hi Filipe,

Sorry for not being very clear in my last note. What I meant was when I run following query 
SELECT @@global.time_zone, @@session.time_zone;
It gives me "SYSTEM" as output so I run "SELECT @@system_time_zone" query and it shows me "Pacific Daylight Time".

However I was wrong here and was under the impression that it returns "Pacific Daylight Time" which is not true(Looks like I couldn't properly remember the value while posting my last comment). I verified it again after reading your comment and found that it actually returns "PDT" not "Pacific Daylight Time". So it was my mistake.

Thanks for responding to the query, it actually helped :)