Use of Mysql's version 8 Connector/J jar file will throw error about Timezone.

Use of Mysql's version 8 Connector/J jar file will throw error about Timezone.

book

Article ID: KB0076976

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization 7.0.x and higher

Description

At the time of this writing, the current TDV v8.2 supports Mysql version 5.1 and version 5.5 as datasources. Mysql is currently vending their version 8.0 jdbc driver (Connector/J) that is documented by them as compatible with Mysql version 5.5 server.

If Mysql version 8 jar file (mysql-connector-java.jar) is used and is installed at the proper location in TDV under: \conf\adapters\system\mysql_5_5, TDV may still throw an error similar to this popping up in Studio, during a connection attempt to the Mysql database.

Cause: The server time zone value 'PDT' 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.

Root Cause: The Mysql Connector/J version 8.0 is now requiring a valid and specific timezone to be specified.

Issue/Introduction

Use of Mysql's version 8 Connector/J jar file will throw error about Timezone.

Resolution

Currently in TDV v8.2 and prior, the default url pattern for a Mysql datasource was:  jdbc:mysql://<HOST>:<PORT>/<DATABASE_NAME>
This url pattern suffices when using earlier versions of Mysql jdbc connectors.

A workaround when using a Mysql version 8.0 jar file is to manually modify our Connection URL Pattern field seen in the datasource's Advanced tab and compose the final url yourself, adding this url option:  ?serverTimezone=PST

Note: The actual timezone may vary

Example of a final url in the Connection URL Pattern field, for a server in the eastern timezone:   
jdbc:mysql://<your host or ip>:3306/<your db name>?serverTimezone=EST