MySQL Bugs: #90813: Timezone not correctly read from host

Bug #90813	Timezone not correctly read from host
Submitted:	9 May 2018 20:11	Modified:	11 May 2018 7:10
Reporter:	Dale Preston	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6.40	OS:	CentOS (7.3)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Trying to connect to MySQL I get this error:

com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'CDT' 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.

The problem is that the timezone is properly set as America/Chicago. In Centos (or RHEL) when you specify that correct timezone some tools report that as CDT (or CST depending on season) but other methods of reading timezone display the proper timezone.

For instance, date shows CDT but ls -l /etc/localtime shows America/Chicago. timedatectl shows America/Chicago (CDT, -0500).

So, depending on how the system timezone is read, the correct information is available to MySQL. MySQL is not reading the timezone correct. MySQL should change how it reads timezone to get the correct value from the host since the correct value is available on the host.

How to repeat:
This error came using Hive connecting to Hive Meta Store. I assume any jdbc connection would have the same error by default.

Suggested fix:
MySQL should change how it reads timezone to get the correct value from the host since the correct value is available on the host.

Note that it is an option in many jdbc connections to add the timezone to the connection string but not in the case of applications creating it in code. In any case, fixing it once in MySQL will save millions of developers from always having to code around this bug and will allow Hive to work with MySQL

Hello Dale Preston,

Thank you for the bug report.
Could you please provide us post installation configuration steps ( - please make it as private if you prefer) to confirm this issue at our end?
Please follow the below instructions :
 https://dev.mysql.com/doc/refman/5.6/en/postinstallation.html "Optionally, populate time zone tables to enable recognition of named time zones. For instructions, see  Section 5.1.12, “MySQL Server Time Zone Support”. 

Thanks,
Chiranjeevi.

I had already followed the steps for adding the timezone information to the table and verified that the timezone data was there and I still get the same issue.

I have noticed in various technical forums that this issue has been around a very long time; there are a lot of people suffering the same problem.  The simple fix is for MySQL to simply change where it gets timezone information.  It should not be detecting my timezone as CDT; it should be recognizing the configured America/Chicago.  Actually, I believe, that even if the timezone table was not populated, it would be good if MySQL would simply add this value since it is the known timezone of the host.

MySQL has been around a long time and has a great reputation.  I think it would be good for them to eliminate this long-standing issue that no other database has.  This only occurs in MySQL.

I have the same problem. I want to install OpenGTS on one system, with MySql on another system. Setting up the database by a script fails. Communication between the system works. The error is:
[DBAdmin.execCommands:1007] java.sql.SQLException: The server time zone value 'CEST' is unrecognized or represents more than one time zone.
On the MySQL server are 4 databases running already so I do not want to change any settngs on the server. Global TimeZone on the MySQL server is SYSTEM which is Europe/Amsterdam.

I also have this issue. I've done quite a bit of research and it appears to be a connector/j issue and likely a regression. I've verified that different versions of the connector/j driver do not have this error when connecting to the same database server.

The work-around I am using is to add the timezone to the connection string like this:

jdbc:mysql://localhost:3306/foobarbaz?serverTimezone=UTC

However, I agree with the other commenters, this is a long standing issue that should be fixed.

I have the same problem when running Java code (attached) on my localhost. The thing is I'm not calling any timezone tables, so I cannot understand why it affects me. Attached also the table definition.

Java code to reproduce the problem

Attachment: Java code (application/octet-stream, text), 682 bytes.

COFEESS table definition

Attachment: table definition (application/octet-stream, text), 540 bytes.