Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution
Highlighted

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

Explorer

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

Super Mentor

@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

View solution in original post

2 REPLIES 2
Highlighted

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

Super Mentor

@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

View solution in original post

Highlighted

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

Explorer

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

Don't have an account?
Coming from Hortonworks? Activate your account here