question Re: Error setting up hive on HDP 2.6.5.....timezone on mysql in Support Questions

Error setting up hive on HDP 2.6.5.....timezone on mysql

wpakkala — Fri, 07 Sep 2018 08:01:55 GMT

I receive the following error after installing hive

Metastore connection URL:	 jdbc:mysql://europa.diamond/hive?createDatabaseIfNotExist=true
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 hive
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.
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: java.sql.SQLException : The server time zone value 'EDT' 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.
SQL Error code: 0
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.

How do I solve this issue?

Re: Error setting up hive on HDP 2.6.5.....timezone on mysql

jsensharma — Fri, 07 Sep 2018 08:05:38 GMT

@Wayne Pakkala

You can try any ofm the following approach to address this issue:

Option-1). From MySQL Side:
Edit the "/etc/my.cnf" file to match timezone to EDT or something like following (please pick correct value):

default_time_zone='+08:00'

Restart mysql server (OR try the following) Please choose your own time_zone, following is just for an example.

mysql> SET GLOBAL time_zone = 'Australia/Sydney';

Following by a MySQL restart.

Reference: https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

Option-2).
You can also try adding the "serverTimezone=EDT" in your MySQL URL in hive settings from Ambari UI:

Change From:

jdbc:mysql://xxxx.yyyy/hive?createDatabaseIfNotExist=true

Change To:

jdbc:mysql://xxxx.yyyy/hive?createDatabaseIfNotExist=true&serverTimezone=EDT

Re: Error setting up hive on HDP 2.6.5.....timezone on mysql

wpakkala — Fri, 07 Sep 2018 16:33:09 GMT

Thx @Jay Kumar SenSharma, this solved my problem. I used your suggestion and modified the jdbc string.