MySQL JDBC Driver 5.1.33 - Time Zone Issue
Solution 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
Solution 2
I've solved this problem by configuring MySQL.
SET GLOBAL time_zone = '+3:00';
Solution 3
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 withuseJDBCCompliantTimezoneShift=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 isuseLegacyDateTimeCode=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)
Solution 4
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.
Solution 5
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.
bluecollarcoder
Updated on July 08, 2022Comments
-
bluecollarcoder almost 2 years
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?