Error : "server time zone value 'CEST' is unrecognized or represents more than one time zone"
Timezones problem (following the time change on Sunday March 28th in France) :
- Asqatasun service tries to restart continuously (systemd unit)
- But Asqatasun can't connect to the MySQL database every time
- MySQL service sees the connection attempts of the Asqatasun application, but cannot establish the connection
Logs
MySQL
Aborted connection to db: 'asqatasun' user: 'asqatasun' host: 'localhost' (Got an error reading communication packets)
Asqatasun
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.
Server config
ls -l /etc/localtime
# ... /etc/localtime -> /usr/share/zoneinfo/Europe/Paris
cat /etc/timezone
# Europe/Paris
timedatectl
# Local time: Sun 2021-04-11 23:12:18 CEST
# Universal time: Sun 2021-04-11 21:12:18 UTC
# RTC time: Sun 2021-04-11 21:12:19
# Time zone: Europe/Paris (CEST, +0200)
# System clock synchronized: yes
# systemd-timesyncd.service active: yes
Solutions
Ressources
- https://stackoverflow.com/questions/26515700/mysql-jdbc-driver-5-1-33-time-zone-issue
- https://github.com/jhipster/generator-jhipster/issues/8896
- https://openclassrooms.com/forum/sujet/erreur-servertimezone-connexion-a-mysql
Possible solutions
- use one of versions of
mysql-connector-java
that is not affected by this bug (5.1.39
or>= 6.0.3
) ---> not tested- Asqatsun uses
5.1.48
- this solution does not seem to work --> see: https://stackoverflow.com/a/38005969
- Asqatsun uses
- Add parameters to JDBC URL (
serverTimezone
,useJDBCCompliantTimezoneShift
anduseLegacyDatetimeCode
) ---> not testedjdbc:mysql://localhost:3306/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Europe/Paris
- Update Asqatasun code to correct this problem with the following modification: ---> not tested
String url = "jdbc:mysql://localhost:3306/db?serverTimezone=" + TimeZone.getDefault().getID()
- Update Mysql server configuration (this works, but must be changed each time the winter and summer time change)
Note that the solution n°2 via the addition of parameters in the JDBC URL cannot work at the moment, because additional parameters are added to this JDBC URL in the Asqatsun code without taking into account existing parameters in this URL. See: #560 (closed)
Temporary solution: update Mysql server configuration
Temporary solution used to fix this problem on an Asqatasun server:
echo "default_time_zone='+02:00'" >> /etc/mysql/mysql.conf.d/zz_asqatasun.cnf
systemctl restart mysql
This works, but must be changed each time the winter and summer time change!
Edited by Matthieu FAURE