The server time zone value 'CEST' is unrecognized

Question

I am using hibernate (Hibernate Maven 5.2.15.Final, Mysql-connector Maven 8.0.9-rc) whith mysql 5.7 on lampp environment on linux so.

I am in Italy (Central European Summer Time) and once March 25, occurs follow error on connection db:

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.

On mysql console, I ran:

SHOW GLOBAL VARIABLES LIKE 'time_zone';
SET GLOBAL time_zone='Europe/Rome';

but that did not persist.

Then I added to my.cnf file (in /etc/mysql):

[mysqld] 
default-time-zone = 'Europe/Rome'

and also:

default_time_zone = 'Europe/Rome'

but the db server did not start still...

Why does the error occur? Could someone help me?

Thank you!

You need to change that in your my.cnf file. Have a look at [this answer](https://stackoverflow.com/a/19069310/643039) — Mathieu de Lorimier, Mar 27 '18 at 17:12

Claude Brisson · Answer 1 · 2020-04-29T15:54:41.640

33

@aiman's answer is not correct since in your case the effective server timezone is not UTC.

You'll find on the net some solutions including additional parameters on the jdbc connection string, but there are cases where you cannot change this string.

Here's how I fixed it:

First import the system timezones in mysql:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Then set your default mysql server timezone in the [mysqld] section of /etc/mysql/my.cnf (or of /etc/mysql/mysql.conf.d/mysqld.cnf on recent Debian/Ubuntu distros) to your actual server timezone, for instance:

default_time_zone = Europe/Paris

and don't forget to restart mysql

$ sudo service mysql restart

(or the appropriate command depending on your distro).

edited Apr 29 '20 at 15:54

answered Jul 24 '18 at 08:41

Claude Brisson

4,085
1
22
30

just in case someone has this problem on Windows, this worked for me to import the timezones into MySQL: follow the instructions to download an SQL file here: https://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html#time-zone-installation and execute `mysql -u root mysql < timezone_posix.sql` – Kai Carver Aug 16 '18 at 02:39
This should be the correct answer. Any other answer I searched online are either changing time zone (!!!) or changing in the connection string, what is sometimes impossible to change. THANK YOU! – David Sánchez Jul 21 '19 at 15:24
mysql 8 on ubuntu will not start with this `[ERROR] [MY-010361] [Server] Fatal error: Illegal or unknown default time zone 'Europe/Paris'` – Pavel Niedoba May 08 '20 at 21:42
After restart you should check, what is the timezone. `SELECT @@global.time_zone;`. If you see SYSTEM there, this means the change didn't work. I had set as root aDB user explicitly `SET GLOBAL time_zone = 'Europe/Stockholm';` and then I could connect with hibernate. – divanov Oct 30 '21 at 11:30

Hans Wouters · Answer 2 · 2020-10-13T06:59:57.103

18

If the problem is when connecting to the db, I found the solution in the NOTE of this answer.

In your connection url use the following:

String url = "jdbc:mysql://localhost/mydb?serverTimezone=Europe/Rome";

This worked for me to connect with flyway and timezone Europe/Amsterdam.

edited Oct 13 '20 at 06:59

answered Sep 21 '20 at 07:34

Hans Wouters

588
5
16

2

It worked for me. Note that serverTimezone=CEST doesn't work but Europe/Rome does. – xtian Oct 12 '20 at 17:48
Yeah, I believe that is because CEST is not specific enough, plus the S stands for Summer time, so you would tell the server/db it is set to summer time no matter the time of year. – Hans Wouters Oct 13 '20 at 06:58
2

Thanks a lot that works for me by changing Europe/Paris – Abdelmjid EL KIHEL Aug 20 '22 at 13:29
Thanks a lot that works for me by changing Europe/Berlin – Twistleton Sep 22 '22 at 08:03

score 14 · Answer 3 · answered May 06 '18 at 15:40

14

First see your mysql server timezone:
mysql -e "SELECT @@global.time_zone;" -u <mysqluser> -p.
Most probably it should be SYSTEM.
Find your system timezone: date +”%Z.
See if its CEST.
You need to change your system timezone:

#cd /usr/share/zoneinfo
#ls -l
#rm /etc/localtime
#ln -s /usr/share/zoneinfo/UTC /etc/localtime

Then restart your mysql server: /etc/init.d/mysqld restart.

Enjoy

answered May 06 '18 at 15:40

aiman

1,049
19
57

1

Had to use "systemctl restart mysql" to restart, but all other commands saved me :) – MrG Jun 17 '20 at 14:26
1

Thanks, it worked for me as well after trying with no success other solutions. Adding an info for the ones that might face the same problem i had: I got a "permission denied" while trying to execute the commands "rm /etc/localtime" and "ln -s /usr/share/zoneinfo/UTC /etc/localtime". I've solved it adding "sudo" in the beginning, ex: sudo rm /etc/localtime. – Henrique Apr 11 '21 at 11:42
So you change your actual timezone, which is CEST, to UTC, which is not your time zone. This is not a solution, but rather a dirty workaround. – divanov Oct 30 '21 at 11:01

score 0 · Answer 4 · answered Aug 31 '22 at 18:38

0

This cropped up out of the blue on my dev machine - restarting MySQL fixed for me

answered Aug 31 '22 at 18:38

happysailingdude

185
1
14

The server time zone value 'CEST' is unrecognized

4 Answers4

Linked

Related