Mysql JDBC Driver 5.1.33 - Time Zone Issue

Connection string in XML files

When using XML configurations such as in Tomcat or within a Spring database.xml file, remember to properly encode your connection string using &.

<property name="url" value="jdbc:mysql://localhost/databasename?useUnicode=true&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC"/>


To avoid encoding issues altogether, you can encapsulate the connection string within a CDATA block:

<property name="url"><![CDATA[jdbc:mysql://localhost/databasename?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC]]></property>


Detailed analysis and troubleshooting

Understanding the conundrum

A commonly reported error is: "The server timezone value 'UTC' is unrecognized or represents more than one timezone." Conflicting or vague timezone settings between MySQL server and Java can lead to this issue. Notably, JDBC Driver versions 5.1.33 and onwards have improved timezone handling to prevent such errors.

Server adjustments

You can set a timezone on the MySQL server using this SQL command:

SET GLOBAL time_zone = '+3:00'; //Don't forget to adjust your timezone!


For changes to persist, you should adjust the server configuration file (my.ini or my.cnf):

[mysqld]
default_time_zone='+3:00' // Save your changes here!

Driver improvements

Upgrade your JDBC driver to version 5.1.33 or higher to better handle timezones. Don't forget to update the connector version in your Maven pom.xml file.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version> //Make sure to use the correct driver version!
</dependency>


After you've updated it, rebuild your project with Maven to apply the changes.

Picking the perfect timezone settings

The difference between GMT and UTC isn't just semantics. It affects accuracy and compatibility. We recommend configuring databases to UTC+0 for settings like AWS where global distribution is involved.

Timezone in .properties files

For easy swapping between different environments, use flexible timezone definitions in .properties files:

database.url=jdbc:mysql://localhost:3306/databasename?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC


Synchronization between server and JDBC driver

Make sure your JDBC driver and the MySQL server timezones are in sync for smooth interactions. Mismatched timezone settings can be a source of pesky bugs and unforeseen errors.

Why useLegacyDatetimeCode=false

Using useLegacyDatetimeCode=true can make the driver use outdated timezone formatting, and we're not archaeologists, are we? Therefore, it's prudent to use useLegacyDatetimeCode=false for modern applications.

Your string encoding should be Unicode

Adding useUnicode=true in the connection strings supports character encoding, ensuring that data is represented correctly, in any language or special characters.