Infiniroot Blog: We sometimes write, too.

Of course we cannot always share details about our work with customers, but nevertheless it is nice to show our technical achievements and share some solutions.

Error on Tomcat start java.sql.SQLException: The server time zone value CEST is unrecognized after updating MySQL JDBC connector

Published on August 11th 2020 - see original post


On a Jira test sever installation, the newest MySQL JDBC driver was downloaded and installed to check for performance and compatibility.

Replacing/updating the JDBC connector

In a standard installation (with the default Jira paths), the JDBC connector can be found in the path /opt/atlassian/jira/lib/. On this particular Jira installation, an old MySQL connector (5.1.41) is still in use. This connector was replaced with a newer version (8.0.21), downloaded from MySQL Community Downloads:

root@inf-jira01-t:~# /etc/init.d/jira stop
root@inf-jira01-t:~# mv /opt/atlassian/jira/lib/mysql-connector-java-5.1.41-bin.jar .
root@inf-jira01-t:~# unzip mysql-connector-java-8.0.21.zip
root@inf-jira01-t:~# cp mysql-connector-java-8.0.21/mysql-connector-java-8.0.21.jar /opt/atlassian/jira/lib/
root@inf-jira01-t:~# /etc/init.d/jira start

Note: As of this writing, Jira recommends to use JDBC connector version 5.1. The new version was simply tested to see whether it was possible and if performance would be better.

After the start there are two important log files to watch:

Driver-class name has changed

While watching the startup, one specific line has caught our eye:

2020-08-11 15:38:05,931 JIRA-Bootstrap INFO      [c.a.j.config.database.DatabaseConfigHandler] Database password decryption not performed.
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

Although informational, this reveals that the driver-class name has changed from com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver. In the future this might cause problems, so Jira's dbconfig.xml needs to be adjusted.

In a default installation, the path to dbconfig.xml is /var/atlassian/application-data/jira/dbconfig.xml. This results in the following new driver-class:

<driver-class>com.mysql.cj.jdbc.Driver</driver-class>

After this change, Jira was restarted again.

Jira Bootstrap error

This time a severe error was logged and Jira refused to start up because of it:

2020-08-11 15:43:47,097 JIRA-Bootstrap ERROR      [c.a.config.bootstrap.DefaultAtlassianBootstrapManager] Could not successfully test your database:
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.

The Tomcat's JVM detected that the system time was set to CEST, which can be verified easily using date:

root@inf-jira01-t:~# date
Tue Aug 11 16:23:48 CEST 2020

Obviously Central European Summer Time is not fancied* by Java so this can be changed to UTC (which is the default for Tomcat anyway). Once again, this needs to be done in dbconfig.xml by adjusting the url value. Here the mentioned serverTimezone variable was appended into the connection string:

<url>jdbc:mysql://localhost:3306/Jira?useUnicode=true&characterEncoding=UTF8&sessionVariables=default_storage_engine=InnoDB&serverTimezone=UTC</url>

* The reason for the error showing up is that "CEST" is not recognized by the the mysql connector library. The timezone is simply non-existant in the source code.

Another restart of Jira would reveal whether or not it would work.

Database configuration OK

This time, the logs did not show any errors and the Jira application log revealed that the database configuration was OK:

root@inf-jira01-t:~# tail -f /var/atlassian/application-data/jira/log/atlassian-jira.log
[...]
    Database configuration OK

    ___ Database Configuration _________________

         Loading entityengine.xml from                 : file:/opt/atlassian/jira/atlassian-jira/WEB-INF/classes/entityengine.xml
         Entity model field type name                  : mysql
         Entity model schema name                      :
         Database Version                              : MySQL - 5.5.5-10.3.23-MariaDB-1:10.3.23+maria~bionic
         Database Driver                               : MySQL Connector/J - mysql-connector-java-8.0.21 (Revision: 33f65445a1bcc544eb0120491926484da168f199)
         Database URL                                  : jdbc:mysql://localhost:3306/Jira?useUnicode=true&characterEncoding=UTF8&sessionVariables=default_storage_engine=InnoDB&serverTimezone=UTC
         Database JDBC config                          : mysql jdbc:mysql://localhost:3306/Jira?useUnicode=true&characterEncoding=UTF8&sessionVariables=default_storage_engine=InnoDB&serverTimezone=UTC


2020-08-11 15:51:09,763 JIRA-Bootstrap INFO      [c.a.jira.startup.JiraStartupLogger]
[...]

Note: In this test installation MariaDB 10.3 is installed. It is however discovered by Jira (or the MySQL JDBC connector?) as MySQL 5.5 - although MySQL 10.3 should be MySQL 5.7. This causes the Jira application to show a compatibility warning in the UI.

Atlassian Jira or Confluence Hosting needed?

If you seek professional Atlassian Jira or Confluence server hosting in Switzerland, head on over to Infiniroot!