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:
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
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:
Connector 6.x:

5.  So, the timezone defaults to client since timezone for server cannot be resolved.
Connector/J 5.1.x:
Connector 6.x:

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:
Connector 6.x:
[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"

[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 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 18:30] Filipe Silva
Hi Andrew,

Can you define "used to work pre-Connector/J"?
[2 Jul 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:


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 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.