MySQL Bugs: #75509: Exception when setting useLegacyDatetimeCode=false

Bug #75509	Exception when setting useLegacyDatetimeCode=false
Submitted:	14 Jan 2015 19:29	Modified:	17 Jan 2015 3:24
Reporter:	Jesse Barnum	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	timezone

Description:
When I set useLegacyDatetimeCode=false in my JDBC connection properties, I get this error message:

java.sql.SQLException: The server timezone value 'CET' represents more than one timezone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc timezone value if you want to utilize timezone support. The timezones that 'CET' maps to are: Europe/Tirane, Europe/Andorra, Europe/Vienna, Europe/Minsk, Europe/Brussels, Europe/Sofia, Europe/Prague, Europe/Copenhagen, Europe/Tallinn, Europe/Berlin, Europe/Gibraltar, Europe/Athens, Europe/Budapest, Europe/Rome, Europe/Riga, Europe/Vaduz, Europe/Vilnius, Europe/Luxembourg, Europe/Malta, Europe/Chisinau, Europe/Tiraspol, Europe/Monaco, Europe/Amsterdam, Europe/Oslo, Europe/Warsaw, Europe/Lisbon, Europe/Kaliningrad, Europe/Madrid, Europe/Stockholm, Europe/Zurich, Europe/Kiev, Europe/Uzhgorod, Europe/Zaporozhye, Europe/Simferopol, Europe/Belgrade, Africa/Algiers, Africa/Tripoli, Africa/Casablanca, Africa/Tunis, Africa/Ceuta.

I understand what the message is telling me, but I'm not sure what to do about it. I don't have any way of knowing what time zone the MySQL server is running in (this is software that my customers install, and the servers are not managed by me).

I need to set this property to false to fix MySQL time zone bugs.

How to repeat:
With a MySQL server running in US Eastern Time zone, try to create a JDBC connection to it with the useLegacyDatetimeCode property set to false.

Suggested fix:
I'm sure that MySQL can detect what the time zone is of the server that it's running on, why not just automatically use that?

Hi Jesse,

First, I would recommend you to update your Connector/J to the latest version. Time Zones support was revised and updated on version 5.1.33. Even the information you are getting in the error message is outdated by now.

You can consult the server Time Zone settings by executing the query "SELECT @@global.time_zone, @@session.time_zone", but you also got this information in the error message. In your case, the Time Zone abbreviation CET which represents a bunch of standard Time Zone that eventually share the same rules.

But... bear in mind that when you turn on any kind of Time Zones "correction" in Connector/J, either by setting 'useLegacyDatetimeCode=false' or 'useTimezone=true', you are saying that you want your date and time values to be somehow translated between client and server Time Zones. And that brings with it the burden of all historical daylight switches and zone adjustments that occurred in the past for the zones in use, client's and server's. Having said that, I want to point out that the Connector/J can't deal with most of the commonly used abbreviations out there, like CET, simply because it actually represents more than one standard Time Zone.

The next question regarding Time Zones conversions you have to put yourself is if you really need or how you want them, knowing that, currently, MySQL is unable to store TZ information in date and time fields, which in practice means that some daylight switches or Time Zone adjustments that occurred in the past are non-deterministic values. Think of all overlaps that occur in the autumn when clocks are adjusted one hour backward.

So, if this the path you want to take, I would recommend you to set the property 'useLegacyDatetimeCode=false' and assure that your MySQL server is configured with a non-DST aware time zone, like GMT, which would be my first choice. If you are unable to configure your MySQL server Time Zone, then you can still override it using the property 'serverTimezone'.

I'm closing this report as "Not a Bug" because your suggested fix is actually what Connector/J currently does. It simply can't know what to do when the information it gets is inaccurate. And that's why you are getting the exception.

If you need, we can continue discussing these matters, but the MySQL forums [http://forums.mysql.com/list.php?39] would be a better place to do it.

Thank you,

This seems to be the same bug I reported here:
http://bugs.mysql.com/bug.php?id=79343