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!

Astred

You need to change that in your my.cnf file. Have a look at this answer – Deodorant

Answer 1

@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).

Boehmite

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: dev.mysql.com/doc/refman/5.6/en/… and execute mysql -u root mysql < timezone_posix.sql – Calceiform

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! – Estivation

mysql 8 on ubuntu will not start with this [ERROR] [MY-010361] [Server] Fatal error: Illegal or unknown default time zone 'Europe/Paris' – Salient

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. – Rogation

Answer 2

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.

Dannielledannon

It worked for me. Note that serverTimezone=CEST doesn't work but Europe/Rome does. – Zoomorphism

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. – Dannielledannon

Thanks a lot that works for me by changing Europe/Paris – Hirsute

Thanks a lot that works for me by changing Europe/Berlin – Hinrichs

Answer 3

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

Filing

Had to use "systemctl restart mysql" to restart, but all other commands saved me :) – Louralourdes

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. – Boak

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. – Rogation

Answer 4

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

Padova