Bug #101924 Please improve java connector timezone error reporting
Submitted: 9 Dec 2020 9:10 Modified: 28 Mar 11:11
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: cest, cet, timezone

[9 Dec 2020 9:10] Simon Mudd
Description:
I am splitting bug#94257 which has been verified in 2: a client side part and a server side part. This is for the connector J client side.

From the original bug report:

MySQL reports it is using system timezone as CEST, when not in summer time:

[ 10 ] USER@HOST-2006: ~ % ➜  sudo mysql -e 'select @@global.time_zone, @@session.time_zone, @@global.system_time_zone'                                                                                  
+--------------------+---------------------+---------------------------+
| @@global.time_zone | @@session.time_zone | @@global.system_time_zone |
+--------------------+---------------------+---------------------------+
| SYSTEM             | SYSTEM              | CEST                      |
+--------------------+---------------------+---------------------------+

Mysqld had been running for 112 days, so it was started when CEST was being used. 
After restarting mysqld, the correct time zone is shown:

[ 11 ] USER@HOST-2006: ~ % ➜  sudo mysql -e 'select @@global.time_zone, @@session.time_zone, @@global.system_time_zone'
+--------------------+---------------------+---------------------------+
| @@global.time_zone | @@session.time_zone | @@global.system_time_zone |
+--------------------+---------------------+---------------------------+
| SYSTEM             | SYSTEM              | CET                       |
+--------------------+---------------------+---------------------------+

When a java application tried to connect to this server, it failed with this error:

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'CEST' 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.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
    at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:132)
    at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2241)
    at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2265)
    at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1319)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:966)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
    at com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:402)
    at com.booking.jdbc.SingleMysqlDataSource.getConnection(SingleMysqlDataSource.java:22)
    at com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:125)
    at com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:110)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:353)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:473)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:562)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
    ...

Looking at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:132) it calls into java.util.TimeZone.getAvailableIDs() which returns a list that does not include CEST, so it bails:

[ 944 ] USER@HOST: ~/bin/mysql-connector-j (release/8.0) % ➜  jython
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by jnr.posix.JavaLibCHelper$ReflectiveAccess (file:/usr/share/java/jnr-posix.jar) to method sun.nio.ch.SelChImpl.getFD()
WARNING: Please consider reporting this to the maintainers of jnr.posix.JavaLibCHelper$ReflectiveAccess
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
Jython 2.7.1 (, Oct 22 2017, 13:43:00) 
[OpenJDK 64-Bit Server VM (Oracle Corporation)] on java10.0.2
Type "help", "copyright", "credits" or "license" for more information.
>>> import java.util.TimeZone
>>> [a for a in java.util.TimeZone.getAvailableIDs() if a in ['CEST', 'CET']]
[u'CET']
>>> 

The connector J gets confused because it tries to use the timezone provided by the server and considers it invalid.

For non-Europeans, CEST is Central European Summer Time, applicable from March to October.

First we know that CEST is a valid timezone. Also it does NOT represent more than one timezone. I suspect that trying to use CEST during "winter time" is invalid, so the timezone specification is invalid during a certain part of the year. Here I am guessing. Clearly Java was not happy about it.

How to repeat:
See above.

Workaround set the timezone explicitly, though this should not be needed.

Suggested fix:
Please make the error reporting clearer:

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'CEST' is unrecognized or represents more than one time zone. 

This give 2 possible problems.

Split this into 2 different errors, and make the error report more concisely explain the actual problem it is seeing so the user can decide how to handle this.

Given current MySQL behaviour exposing CEST during the winter, if this is going to cause issues on the client side ensure documentation mentions this problem specifically.
[9 Dec 2020 12:30] MySQL Verification Team
Hello Simon,

Thank you for the report and feedback.
I'm verifying this as a feature request to improve the error message.

regards,
Umesh
[26 Mar 2021 13:17] Filipe Silva
Hi Simon,

First of all, my apologies for not replying before.

Commenting on a couple of your statements:

> First we know that CEST is a valid timezone.

Yes, this is true. But a time zone abbreviation says little about your real intents regarding date/time handling. Only in a few rare cases the abbreviation and the geographical places it applies to match exactly. I'll explain below.

> Also it does NOT represent more than one timezone.

This is not completely true. It does represent a specific set of time rules but the countries (I'll call them geographical regions) that observe it aren't always the same during the history of this abbreviation.

So, it is generally fine use a time zone abbreviation when you work with relatively close instant values. CEST, today, describes the TZ rules for many geographical regions in Europe, during summer of course, including Europe/Amsterdam for example. But, CEST does not tell us exactly in which region you are, so, if you need to store past date/time values CET/CEST would give you generic time adjustments, not exactly the TZ rules you would expect for your region. Take Europe/Amsterdam again for example. Today Europe/Amsterdam shares CET/CEST TZ rules with many other regions, but it wasn't always like that. Say, date/time values before 1940 in this region were bound to different TZ rules while other regions where already using them. I believe this is also why Java doesn't support all most common TZ abbreviations.

Because we don't what kind of data you want to store, and since MySQL doesn't really support date/time values with time zone information, Connector/J tries its best to preserve accurate instant values for you (depending on how you configure it), but in order to do that, we need to know unmistakably what are your real intentions. That is only possible if we you give us all the information we need by providing a "stable" TZ identification we can trust.

This is also why we generally recommend setting your server to an anchor Time Zone that doesn't observe DST, usually just UTC. This way client applications can convert local date/time values, wherever they are, into date/time representations that don't carry TZ offset info, that represent accurate points in the time line. This approach has issues too, though. Users must learn all they can regarding this subject and test thoroughly until they find the setup that better fits their needs.

As of version 8.0.23, Connector/J date/time handling was revised and completely overhauled. Now, Connector/J won't fail automatically when the server time zone cannot be used. It will still happen but only when and if the TZ is needed for the first time for doing required adjustments. I'd suggest reading https://insidemysql.com/support-for-date-time-types-in-connector-j-8-0/ for some insights.

We also have augmented the documentation to cover some of this in https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-date-time.html. I know it's never enough but hopefully it helps users starting. We are working every day to make it better.

Please explore all the new features in Connector/J 8.0.23 and let us know what you think.

Having said all this I don't think the error message Connector/J returns is wrong.
[27 Apr 2021 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[24 May 2021 14:34] Simon Mudd
To provide some requested feedback:

> This is also why we generally recommend setting your server to an anchor Time Zone that doesn't observe DST, usually just UTC.

Where is this documented? I must have missed it. If you do recommend that please point me to the clear recommendation. (The recommendation is fine.)

While I created this bug report it was split from the original bug#94257 created by a colleague.

It's good to see there have been improvements in the code and documentation. If the client no longer reports the same error as indicated before with newer code than I guess we can consider this resolved.
[28 Jun 2021 15:32] Filipe Silva
Hi Simon,

>> This is also why we generally recommend setting your server to an anchor Time Zone that doesn't observe DST, usually just UTC.
>
>Where is this documented? I must have missed it. If you do recommend that please point me to the clear recommendation. (The recommendation is fine.)

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-known-issues-limitatio...

Note that this depends entirely on your needs. When users report us issues with DST switches or other time zone related issues, we generally recommend setting the server to UTC. Such change must be done very carefully, though. Only after much analysis and understanding, especially if there's already data in the tables. This is also why we don't do it more openly.

As you have noticed we made several improvements in the date/time handling code and documentation: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-date-time.html. In the process we have introduced several new connection options https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-datetime-types-proces.... You can also read about them here: https://insidemysql.com/support-for-date-time-types-in-connector-j-8-0/.

So, as of Connector/J 8.0.23, the error above may still occur, but only in a per-usage basis (when setting/getting date/time values) and not while creating new connections, which makes it more contextualized. The fact that time zones abbreviations should be avoided holds true, though. And that can't be changed.

Please let use know if you find anything in the new code that needs to be fixed.

Thank you,
[28 Mar 10:42] Eduardo Ortega
HI:

Are there any updates regarding this? For me, and irrespective of other suggestions regarding how to configure MySQL server, it is very counter intuitive that the official client library Connector/J cannot deal with a time zone that is good, known and valid for the server. 

Either both CET and CEST should work, or none; in which case the server should always send something that is known to work with any of the official client connector libraries. Anything else seems a bit like poor interoperability between server and client libraries.
[28 Mar 11:11] Simon Mudd
A follow up on this comment:

"Note that this depends entirely on your needs. When users report us issues with DST switches or other time zone related issues, we generally recommend setting the server to UTC. Such change must be done very carefully, though. Only after much analysis and understanding, especially if there's already data in the tables. This is also why we don't do it more openly."

This might be a good idea in theory but unfortunately it requires changing the whole application to handle the UTC timezone by default and so in practice on systems that running 24x7x365 this is extremely complex to achieve.  A lot of application changes are likely to be needed.

There is currently no way to provide a timezone user specific setting so that we can configure the user to see the same timezone as before and at the same time change the server-configured timezone (to UTC), thus allowing for each user's queries to be adapted so that we can switch their session connection to UTC.

This would simplify the migration of systems configured from a local timezone to UTC.

This would be a great thing to add to mysql.user.User_attributes, e.g. "session_overrides": { "TZ": "UTC" } ...