MySQL JDBC Driver 5.1.33 - Time Zone Issue

Question

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?

Answer 1

The connection string should be set like this:

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

If you are defining the connection in an xml file (such as persistence.xml, standalone-full.xml, etc..), instead of & you should use & or use a CDATA block.

Answer 2

1. I added in mysql config file in section [mysqld]

   default_time_zone='+03:00'
   

2. And restart mysql server:

   sudo service mysql restart
   

Where +03:00 my UTC time zone.

Path to config file on my os ubuntu 16.04:

/etc/mysql/mysql.conf.d/mysqld.cnf

WARNING: IF YOUR TIME ZONE HAVE SUMMER AND WINTER TIME. YOU MUST CHANGE UTC IN CONFIG IF CHANGE TIME. TWICE IN YEAR(USUALLY) OR SET CRONTAB WITH SUDO.

My url jdbc connection:

"jdbc:mysql://localhost/java"

Answer 3

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 

Answer 4

package pack1;

import java.sql.Connection;
import java.sql.DriverManager;
import javax.swing.JOptionPane;

public class sqliteConnection {
    Connection conn=null;

    public static Connection dbConnector() {
        try {Class.forName("com.mysql.jdbc.Driver");

        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306", "root", "");
        JOptionPane.showMessageDialog(null, "connection is succesful");
        return conn;

        } catch(Exception e) {
            JOptionPane.showMessageDialog(null, e);
            return null;

        ////you need also to execute this in database ===> SET GLOBAL time_zone = '+3:00';
        }
    }
}

Answer 5

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.

Answer 6

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.