MySQL Bugs: #85816: MySQLConnector/J cannot handle server timezone "PDT" for timestamp fields

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

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

In section "Description," correction for (2):
SELECT @@time_zone, @@system_time_zone;
SYSTEM	        PDT

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.

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.

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,

http://bugs.mysql.com/bug.php?id=86425 marked as duplicate of this one.

Severity non-critical? That's a laugh.

It used to work pre-Connector/J, now it's been borked.

Hi Andrew,

Can you define "used to work pre-Connector/J"?

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.

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

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.

Bug #93871 marked as duplicate of this one

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.

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 ?

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

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.

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

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 :)

@Felipe

By working I mean using the 5.1.47 jar file - that worked

By not working, I (currently) mean connector/j 8.15 (when PDT kicks in)

It is such a simple fix, adding the mapping to the properties file.   I'm surprised that it has not been done.

@Andrew,

With a major version bump as this one it is expected that some things don't work exactly the same as before. Not always possible to just do a simple jar replacement. We tried to document the differences between Connector/J 5.1 and 8.0, this one in particular is documented here: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-other-changes.html.

@Robert,

I explained in my replay to this bug report on 21 Apr 2017 why PDT cannot be added to the time zone mappings. On this matter Connector/J relies on the time zone information from the the IANA Time Zone Database and the Unicode CLDR. Our time zone mappings result from the best interpretation possible of these standards. In the end, if some mapping is not deterministic, we can't use it.

Bug #95625 marked as duplicate of this one

Bug #95680 marked as duplicate of this one

Hi Filipe

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

According to doc, other than TIMESTAMP, date & datetime values are not converted for storage using any time zone. This makes since, since if one uses LocalDate & LocalDateTime in Java, which also doesn't contain any time zone info, and since MySQL says no timezone conversion happens for storing/retreiving date/datetime values, why should I define a timezone to MySQL connection to retrieve those values correctly?

@Filipe, any comment on my previous comment/question?

Hi Gokhan,

The way MySQL Server handles temporal data and temporal data exchanged between a Client and a Server are different things.

The documentation you referred pertains to how MySQL Server stores and retrieves temporal data and if some kind of TZ adjustments occur in the process. For example, suppose the server is configured with TZ UTC+02:00 and you store the same instant X in a TIMESTAMP column and a DATETIME column; when you retrieve the same data you'll see the same instant X values, but if you change the server TZ to UTC+03:00 and retrieve the same records, the TIMESTAMP value will have an instant X plus one hour ahead of the DATETIME.

So, this is only on server side. Between client and server a similar transformation occurs, except that Connector/J applies it to all temporal data types and not only TIMESTAMP. One of the reasons for this is because TIMESTAMP is quite limited in terms of range. So, if you have two clients in different time zones retrieving the same data you either expect to get them adjusted to their time zones or not and in both cases Connector/J needs to know exactly what's the time zone in the server.

There is a significant difference between Connector/J 5.1 and 8.0, though. While in 5.1 this TZ conversion process it turned off by default, in 8.0 it is turned on and you can't disable it, but you can achieve equal results with a few connection options:
- Enable TZ adjustments:
  C/J 5.1 with useLegacyDatetimeCode=false&serverTimezone=XYZ <--> C/J 8.0 with serverTimezone=XYZ;
- Disable TZ adjustments:
  C/J 5.1 with defaults <--> C/J 8.0 with serverTimezone=CTZ (where CTZ is the time zone where C/J is running)

IHTH

@Filipe

Thanks for the detailed explanation.

But what’ll happen when timezone changes due to daylight savings? Will that effect anything or any special treatment needed?

It depends on what you need and on how much flexibility you want to allow in your application.

If you want to store/retrieve temporal data without TZ adjustment you should be aware that some instants can't be represented in the local time zone. For example, think about two clients in different places in the globe, if client A stores a TIMESTAMP/DATETIME instant that is perfectly correct in his time zone but happens to fall into the DST gap in client B time zone, this instant will not be "correctly" retrieved from client B as Java will end up adjusting it somehow. There is a chance, thought, that you use a LocalDateTime object and the same value can be seen as the same by the two clients.

If you need time zone adjustments so that each client sees the same instant under the rules of each one's own time zone, then you should enable this by using the server time zone as base of all TZ adjustments. However, the server time zone should be set to a non-DST aware TZ, or else you'll face the same issues as before. We always recommend setting the server time zone to UTC (UTC+00:00). This way you are safe to store and retrieve all possible instants without the fear of DST overlaps or gaps.

Finally, none of this is a simple choice. You have to know your needs, how your temporal data looks like and how the client-server interaction operates in all cases. For example, you can easily be tricked to think in terms of client side time zone when executing queries with DATETIME or TIMESTAMPS in WHERE clauses or when using server side date and time functions using arguments from both server stored data and given from client, and then things don't match up.

As a final note, you should also be aware that Connector/J is only able to perform TZ adjustments when you hand over the date/time objects to the driver. If you write a datetime value in the middle of a query using a string format then no TZ adjustment will ever occur. For example you need to find all records with some DATETIME column after mid-night Jan 1st, so you may be inclined to write "SELECT * FROM tbl WHERE instant > '2019-01-01 00:00:00'", which could be OK but you need to know that the values in the table haven't been shifted before.

IHTH