Reputation: 14398

MySQL JDBC Driver 5.1.33 - Time Zone Issue

Some background:

I have a Java 1.6 webapp running on Tomcat 7. The database is MySQL 5.5. Previously, I was using Mysql JDBC driver 5.1.23 to connect to the DB. Everything worked. I recently upgraded to Mysql JDBC driver 5.1.33. After the upgrade, Tomcat would throw this error when starting the app.

WARNING: Unexpected exception resolving reference
java.sql.SQLException: The server timezone value 'UTC' is unrecognized or represents
  more than one timezone. You must configure either the server or JDBC driver (via
  the serverTimezone configuration property) to use a more specifc timezone value if
  you want to utilize timezone support.

Why is this happening?

Upvotes: 471

Answers (30)

epol

Reputation: 1044

After reading several posts on this topic, testing different configurations and based on some insights from this mysql bug thread that's what I have understood:

the server time zone is important in particular to convert dates stored in the database to the time zone of the application server. there are other implications but this is the most noticeable one
GMT x UTC time zone systems. GMT was conceived in the late 19th century and can be shifted between standard time and summer time. this property could lead to a situation where the database server shifts to summer time and the application doesn't notice it (perhaps there are other complications but I didn't research further). UTC does not vary over time (it is always within about 1 second of mean solar time at 0° longitude).
serverTimeZone definition was introduced in mysql jdbc connectors versions 5.1 ahead. until version 8 it could be ignored with useLegacyDatetimeCode=true , which in conjunction with useJDBCCompliantTimezoneShift=true would make the application get the database time zone on every connection. In this mode GMT time zones such as 'British Summer Time' would be converted to the internal java/JDBC format. New time zones could be defined in a .properties file such as this one
Starting with jdbc driver version 8, automatic time matching (useJDBCCompliantTimezoneShift) and legacy time format (useLegacyDatetimeCode) were removed (see mysql jdbc connector changelog). therefore setting these 2 parameters has no effect as they are completely ignored (new default is useLegacyDateTimeCode=false)
In this manner setting serverTimezone became mandatory if any of the time zones (application/database servers) are not in the format 'UTC+xx' or 'GMT+xx'
There is no impact of setting server time as UTC (for instance with jdbc:mysql://localhost:3306/myschema?serverTimezone=UTC, even if your application / database servers are not in this timezone. The important is for the application connection string + database to be synchronized with the same time zone. In different words, simply setting serverTimezone=UTC with a different time zone on the database server will shift any dates extracted from the database
The MySQL default time zone can be set to UTC+0 with the my.ini or my.cnf files (windows / linux respectively) by adding the line default-time-zone = '+00:00' (details in this StackOverflow post)
Databases configured on AWS (amazon web services) are automatically assigned UTC+0 default time (see AWS help page here)

Upvotes: 82

Gabriel Amazonas Mesquita

Reputation: 953

If you are using Maven, you can just set another MySQL connector version (I had the same error, so I changed from 6.0.2 to 5.1.39) in pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version>
</dependency>

As reported in another answers, this issue has been fixed in versions 6.0.3 or above, so you can use the updated version:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.3</version>
</dependency>

Maven will automatically re-build your project after you save the pom.xml file.

Upvotes: 55

Shubham Kumar Gupta

Reputation: 1147

my.ini

At the end add this line:

default-time-zone = '+05:30'

from the terminal run this command

>> sudo mysql -e "SET GLOBAL time_zone = ‘+5:30’;" -u root
>> sudo mysql -e "SELECT @@global.time_zone;" -u root

If both of them do not work try using this request from sqoop using terminal

>> sqoop list-databases --connect "jdbc:mysql://localhost/employees?serverTimezone=UTC" --username sqoop -P

Or you can just replace your request URL with this

jdbc:mysql://localhost/employees?serverTimezone=UTC

Upvotes: 2

Rossi Alex

Reputation: 141

The connection string worked for me this

jdbc:mysql://localhost/<yourDbName>?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

I am using Gradle my build.gradle file is like this

dependencies {
    // https://mvnrepository.com/artifact/mysql/mysql-connector-java
    implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.17'
    testCompile group: 'junit', name: 'junit', version: '4.12'
}

Upvotes: 1

alex

Reputation: 2484

Setting the time zone by location for a spring boot application inside the application.properties file to

spring.datasource.url=jdbc:mysql://localhost:3306/db?serverTimezone=Europe/Berlin

resolved the problem for the CET / CEST time zone. The pom.xml uses the maven artifact

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.22</version>
    </dependency>

Upvotes: 4

chanaka wickramasinghe

Reputation: 121

I faced the same error and in my case, I change the Server Port Number to 3308 where previously it was 3306. This connect my project to the MySQL database.

Here we have to change the connection code also.

Class.forName("com.mysql.cj.jdbc.Driver");
cn=(java.sql.Connection)DriverManager.getConnection("jdbc:mysql://localhost:3308/test2?zeroDateTimeBehavior=convertToNull","root","");

Changing the port number in the connection code is also necessary as localhost:3308 to resolved the error.

Also, the admin properties in my case.

Upvotes: 1

Hermann N'ZI

Reputation: 131

There is no impact of setting server time as UTC (for instance with jdbc:mysql://localhost:3306/myschema?serverTimezone=UTC, even if your application/database servers are not in this timezone. The important is for the application connection string + database to be synchronized with the same time zone.

In other words, simply setting serverTimezone=UTC with a different time zone on the database server will shift any dates extracted from the database

Upvotes: 14

Pratham Sarankar

Reputation: 69

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/resultout? useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC","root",""))

This is actually the solution to this problem, but don't just copy and paste it in your program. If you just read the line you will find 'resultout', that's the name of my database, and you have to write your's.

There are three string components, first one is url, second is username, and third one is password. In above paragraph we cleared, url. The second and third String components as said your username and password you have to change accordingly.

Thanks

Upvotes: 6

Mittal

Reputation: 11

Just modify the connection string with the following code in the application.properties file.


spring.datasource.url=jdbc:mysql://localhost:3301/Db?
   useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=
   false&serverTimezone=UTC

Upvotes: -2

ttt

Reputation: 6839

I have added the following line to my /etc/mysql/my.cnf file:

default_time_zone='+00:00'

Restarted the MySQL server:

systemctl restart mysql

And it works like a charm.

Upvotes: 4

harun ugur

Reputation: 1852

Apparently, to get version 5.1.33 of MySQL JDBC driver to work with UTC time zone, one has to specify the serverTimezone explicitly in the connection string.

spring.datasource.url = jdbc:mysql://localhost:3306/quartz_demo?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Upvotes: 10

iifast2

Reputation: 334

i Got error similar to yours but my The server time zone value is 'Afr. centrale Ouest' so i did these steps :

MyError (on IntelliJ IDEA Community Edition):

    InvalidConnectionAttributeException: The server time zone value 'Afr. centrale Ouest' 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 u....

I faced this issue when I upgraded my mysql server to SQL Server 8.0 (MYSQL80).

The simplest solution to this problem is just write the below command in your MYSQL Workbench -

  SET GLOBAL time_zone = '+1:00'

The value after the time-zone will be equal to GMT+/- Difference in your timezone. The above example is for North Africa(GMT+1:00) / or for India(GMT+5:30). It will solve the issue.

Enter the Following code in your Mysql Workbench and execute quesry

[source link for question/problem ]

[source link for answer]

[Solution ScreenShot ]

Upvotes: 5

saurabh asthana

Reputation: 41

Run below query to mysql DB to resolve the error

MariaDB [xxx> SET @@global.time_zone = '+00:00';
Query OK, 0 rows affected (0.062 sec)

MariaDB [xxx]> SET @@session.time_zone = '+00:00';
Query OK, 0 rows affected (0.000 sec)

MariaDB [xxx]> SELECT @@global.time_zone, @@session.time_zone;

Upvotes: 4

Arefe

Reputation: 12471

You can use the MySQL connector in the Maven dependency,

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.14</version>
</dependency>

Then you need the set the right parameters in the application.properties file,

spring.datasource.url=jdbc:mysql://localhost:3306/UserReward?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.username=testuser
spring.datasource.password=testpassword
# MySQL driver
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect

Upvotes: 14

Vishrant

Reputation: 16698

I executed following on my database side.

mysql> SET @@global.time_zone = '+00:00';

mysql> SET @@session.time_zone = '+00:00';

mysql> SELECT @@global.time_zone, @@session.time_zone;

I am using Server version: 8.0.17 - MySQL Community Server - GPL

source: https://community.oracle.com/thread/4144569?start=0&tstart=0

Upvotes: 18

bluecollarcoder

Reputation: 14398

Apparently, to get version 5.1.33 of MySQL JDBC driver to work with UTC time zone, one has to specify the serverTimezone explicitly in the connection string.

jdbc:mysql://localhost/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Upvotes: 832

Aathil Ahamed

Reputation: 474

The above program will generate that time zone error.

After your database name you have to add this: ?useTimezone=true&serverTimezone=UTC. Once you have done your code will work fine.

Best of luck :)

Upvotes: 18

Berkay Ernalbant

Reputation: 51

I had the same problem when I try to work with spring boot project on windows.

Datasource url should be:

spring.datasource.url=jdbc:mysql://localhost/database?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Upvotes: 4

Gerard Indika

Reputation: 1

I also got the same running java JDBC in NetBeans. This is how it fixed

I use Xampp. In conf button of Apache I opened httpd.conf file and on first line I typed

# Set timezone to Europe/Athens UTC+02:00 
SetEnv TZ Europe/Athens.

In conf button of MySQL I opened my.ini file and on last line I typed "Europe/Athens"

Stopped and started both Apache and MySQL

Problem fixed.

*(Local mechine time zone is different, but no problem.)

Upvotes: -2

Anil Gowda

Reputation: 69

This worked for me.

on DBeaver 6.0 : Go to Connection Settings > Driver Properties > Server Time Zone > Set UTC.

Also, in spring boot config, had to set below property.

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

Upvotes: 7

Dmitriusan

Reputation: 12409

In my case, it was a test environment and I had to make an existing application to work without any configuration changes, and if possible without any MySQL config changes. I was able to fix the issue by following @vinnyjames suggestion and changing server timezone to UTC:

ln -sf /usr/share/zoneinfo/UTC /etc/localtime
service mysqld restart

Doing that was enough for me to solve the issue.

Upvotes: 2

Rituraj Singh

Reputation: 639

I am late, But If you are struggling through the following error and using datasource(javax.sql.DataSource):

The server time zone value 'CEST' is unrecognized or represents more than one time zone.

Set following line to get rid of the error:

MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setServerTimezone("UTC");

Upvotes: 1

ColdFire

Reputation: 1431

It worked for me just by adding serverTimeZone=UTC on application.properties.
spring.datasource.url=jdbc:mysql://localhost/db?serverTimezone=UTC

Upvotes: 38

toof06

Reputation: 81

From mysql workbench run the following sql statements:

SET @@global.time_zone = '+00:00';
SET @@session.time_zone = '+00:00';

with the following sql statements check if the values were set:

SELECT @@global.time_zone, @@session.time_zone;

Upvotes: 7

Ingvar

Reputation: 151

Everything that we need to fix the problem with serverTimezone:

String url = "jdbc:mysql://localhost:3306/db?serverTimezone=" + TimeZone.getDefault().getID()

Upvotes: 15

RAdrian

Reputation: 111

I'm using mysql-connector-java-8.0.13 and had the same problem. I created my database in the command line console and solved this problem by using @Dimitry Rud's solution on the command line:

SET GLOBAL time_zone = '-6:00';

I didn't need to restart anything, set the time and immediately run my code in eclipse, it connected with no problems.

The bug is supposed to be fixed in an older version, but I think I got this error because after I created the database in the console, I didn't set this. I'm not using workbench nor another app to manage this rather than the console.

Upvotes: 11

Yerbol

Reputation: 43

Agree with @bluecollarcoder answer, but it's better to use TimeZone.getDefault().getID(); at the end of the connection string:

"jdbc:mysql://localhost/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=" + TimeZone.getDefault().getID();

In this case Timezone parameter automatically updates depending on the local machine timezone.

Upvotes: 0

felice de simone

Reputation: 161

I have the same problem and i solved it append only "?serverTimezone=UTC" to my string connection.

sinossi my problem:

java.sql.SQLException: 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.

my dbDriver = com.mysql.jdbc.Driver

my jar = mysql-connector-java-8.0.12.jar

my java = 1.8

my tomcat = Apache Tomcat Version 8.5.32

my MySql server = MySql ver.8.0.12

Upvotes: 16

GordR

Reputation: 91

I also was having the exact same problem in LibreOffice Base. So I just specified a non 'daylight savings time zone' in the connection string.

I tried without the "&serverTimezone=MST" but that failed as well.

I also tried "&serverTimezone=MDT" and that failed, so for some reason, it doesn't like daylight savings time!

Upvotes: 3

Rafael Costa Cavalcante

Reputation: 341

I solved putting below connection string in the URL

jdbc:mysql://localhost:3306/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Upvotes: 34

MySQL JDBC Driver 5.1.33 - Time Zone Issue

Answers (30)

Related Questions