Bug #94257 Server timezone stays on CEST after changing summer time is over
Submitted: 8 Feb 2019 12:53 Modified: 3 Jun 2021 17:21
Reporter: Eduardo Ortega Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.7.23 OS:CentOS
Assigned to: CPU Architecture:Any

[8 Feb 2019 12:53] Eduardo Ortega
Description:
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']
>>> 

I think that the server should return the right current system time zone (CET, not CEST), and/or, the Java connector should handle mapping CEST to CET knowing that, at the current time of the year, CET is used, not CEST.

The simple jython test above was don on OpnJDK, but the actual production issue was found with mysql-connector-java-8.0.14 on Java 1.8.0_181.

How to repeat:
1. Start mysqld on CEST.
2. Wait till CEST is over, and time is switched to CET.
3. Run mysql -e 'select @@global.time_zone, @@session.time_zone, @@global.system_time_zone'
[8 Feb 2019 13:24] MySQL Verification Team
Hi Eduardo,

Thank you for your bug report.

In my opinion, MySQL server behaves just fine. It switches from CEST to CET when the switching is supposed to occur.

What seems wrong is that Connector/Java should recognise from the server's uptime and server's timezone that change has occurred interim.

Hence, I would say that this is Connector/Java's bug.

If you agree, I would change the category to Connector.

Thanks in advance.
[8 Feb 2019 13:25] MySQL Verification Team
I do think that MySQL server should not keep an array of timezones that it has passed through. I hope that you agree.
[8 Feb 2019 13:52] Eduardo Ortega
Hi, Sinisa:

Thanks for the feedback. I am not sure MySQL is doing the right thing here. 
It claims it is using CEST when it is not and should not, since at the moment CEST is not the current time zone. CET is. IMHO, it should not need a restart to present the right timezone. 

That being said, I agree things could be handled better from the connector. But having MySQL report it is on CEST when it is not does not seem the right behavior for me. Or am I missing something here?

E.
[8 Feb 2019 14:17] MySQL Verification Team
Hi Eduardo,

I agree with you. Restart should not be required so that server changes a timezone.

Verified as reported.
[8 Apr 2019 11:09] MySQL Verification Team
Bug #94932 marked as duplicate of this one
[8 Apr 2019 11:16] MySQL Verification Team
Bug #93091 marked as duplicate of this one.
[29 Apr 2020 18:24] Björn Voigt
MySQL 5.9.30 showns on Windows 10 (German):

select @@global.time_zone, @@session.time_zone, @@global.system_time_zone;
+--------------------+---------------------+------------------------------+
| @@global.time_zone | @@session.time_zone | @@global.system_time_zone    |
+--------------------+---------------------+------------------------------+
| SYSTEM             | SYSTEM              | Mitteleuropäische Sommerzeit |
+--------------------+---------------------+------------------------------+
1 row in set (0.00 sec)

The localized timezone name "Mitteleuropäische Sommerzeit" is not recognized by MySQL Connector/J:

java.sql.SQLException: The server time zone value 'Mitteleuropäische Sommerzeit' 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.
[30 Apr 2020 12:21] MySQL Verification Team
Hi Mr. Voigt,

First of all, we never released any version like 5.9.*. So, we do not know whether you are running our server or not.

Second, what you are reporting is a behaviour in Connector/JDBC. If you think that it is a bug, report a new bug under that category. However, it should be repeatable on our Connector and with our server.
[30 Apr 2020 12:56] Björn Voigt
Sorry, I meant MySQL Server 5.7.30.

Yes, my mentioned timezone problem is shown as a MySQL Connector/J issue, but it can be solved on the server side. So it's a compatibility problem.

I solved it this way:
1. Import of the current MySQL timezone data on MySQL 5.7.30 Windows (generated from a Linux server with "mysql_tzinfo_to_sql" or timezone download version)
2. Explicit timezone setting on the server side:
   [mysqld]
   default-time-zone = Europe/Berlin

Many posts mention this problem. For instance: https://stackoverflow.com/questions/26515700/mysql-jdbc-driver-5-1-33-time-zone-issue/3829...
[9 Dec 2020 9:21] Simon Mudd
Let's split this bug into 2 as there are 2 issues:
* client side issue: I've opened a new bug report: https://bugs.mysql.com/bug.php?id=101924
* server side issue: this bug report

So let's clarify what we see as being wrong:

In many European countries the timezone switches between CET (winter) and CEST (summer). CEST is active between late March and late October.

The MySQL server when started picks up the timezone from the OS, and reports this in system_time_zone.  However, if later the timezone changes MySQL keeps reporting the same timezone even though the underlying OS (Linux in my case) now reports a different timezone.

E.g. today my server reports CET as the timezone yet the same (local) mysqld reports CEST:

$ date +"%Z %z"
CET +0100
$ mysql -e "SELECT @@system_time_zone, @@version"
+--------------------+------------+
| @@system_time_zone | @@version  |
+--------------------+------------+
| CEST               | 5.7.31-log |
+--------------------+------------+
$

I think the expectation should be that for a client asking the server which timezone it is using mysqld should, like the shell, report the current timezone, not the timezone it was started with.

We have seen from the duplicate bug reports in this bug and in bug#101924 that this can cause problems as developers will ask java to ask the server to use its timezone and this generates an error.

Clearly this is not just an issue of CET/CEST but any timezone where the name of the timezone changes over time.
[9 Dec 2020 9:59] Simon Mudd
I checked latest documents for the 8.0 server on system_time_zone: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_system_time_zo...

It does say:

The server system time zone. When the server begins executing, it inherits a time zone setting from the machine defaults, possibly modified by the environment of the account used for running the server or the startup script. The value is used to set system_time_zone. Typically the time zone is specified by the TZ environment variable. It also can be specified using the --timezone option of the mysqld_safe script.

So technically there is no bug.

However:
* how can a client identify the timezone being used by the server currently?
* does it make sense to reference a timezone that in theory should only be used during a certain part of the year?

This would seem to mean that you should query the server's uptime, get the system_time_zone setting and in one way or another calculate what the current time_zone might be now.  That sounds like a horrendously complicated thing to do when really you just want to ask the server to report what its current time zone is.
[9 Dec 2020 14:36] MySQL Verification Team
Hi Mr. Mudd,

Thank you for your idea. It has been fully implemented.

This bug belongs to the server, while #101924 belongs to the client-side. This has been marked clearly in the internal bugs database, as well.
[3 Jun 2021 17:21] Paul DuBois
Posted by developer:
 
Fixed in 8.0.26.

The system_time_zone system variable is initialized at server startup
from the server host settings and the mysqld environment, but if the
server host time zone changed (for example, due to daylight saving
time), system_time_zone did not reflect the change. Now it does.
[4 Jun 2021 12:12] MySQL Verification Team
Thank you, Paul .......