| Bug #101924 | Please improve java connector timezone error reporting | ||
|---|---|---|---|
| Submitted: | 9 Dec 2020 9:10 | Modified: | 28 Mar 2022 11:11 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| 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
[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 2022 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 2022 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" } ...
