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

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

Answer 2

This is a bug in mysql-connector-java from version 5.1.33 to 5.1.37. I've reported it here: http://bugs.mysql.com/bug.php?id=79343

Edited: This has been corrected from mysql-connector-java 5.1.39

It was a typo in TimeUtil class in loadTimeZoneMappings method that raises a NPE locating /com/mysql/jdbc/TimeZoneMapping.properties file. If you look at the code, the file should be located within TimeUtil class loader, not TimeZone:

TimeUtil.class.getResourceAsStream(TIME_ZONE_MAPPINGS_RESOURCE);

The parameter useLegacyDatetimeCode allows to correct the difference between client and server timezones automatically when using dates. So it helps you precissely not having to specify timezones in each part. Althought using serverTimeZone parameter is a workaround, and meanwhile the patch is released, you can try better correcting the code by yourself as I did.

• If it's a standalone application, you can try simply to add a corrected com/mysql/jdbc/TimeUtil class to your code and be careful with jar loading order. This can help: https://owenou.com/2010/07/20/patching-with-class-shadowing-and-maven.html

• If it's a web application, the easier solution is to create your own mysql-connector-java-5.1.37-patched.jar, substituting the .class directly into the original jar.

Answer 3

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!

Answer 4

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.

Answer 5

I've solved this problem by configuring MySQL.

SET GLOBAL time_zone = '+3:00';

Answer 6

I solved putting below connection string in the URL

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