Error starting HIVE metastore

[#1]

Follow the suggestions from https://community.hortonworks.com/questions/98770/hive-metastore-does-not-start.html, I had changed the value of 'bind-address' in the mysql configuration file /etc/mysql/mysql.conf.d/mysqld.cnf to '0.0.0.0', I still get following errors

I setup the cluster using Ambari on Ubuntu 16

Traceback (most recent call last):
  File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 203, in <module>
    HiveMetastore().execute()
  File "/usr/lib/ambari-agent/lib/resource_management/libraries/script/script.py", line 375, in execute
    method(env)
  File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 56, in start
    create_metastore_schema()
  File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive.py", line 417, in create_metastore_schema
    user = params.hive_user
  File "/usr/lib/ambari-agent/lib/resource_management/core/base.py", line 166, in __init__
    self.env.run()
  File "/usr/lib/ambari-agent/lib/resource_management/core/environment.py", line 160, in run
    self.run_action(resource, action)
  File "/usr/lib/ambari-agent/lib/resource_management/core/environment.py", line 124, in run_action
    provider_action()
  File "/usr/lib/ambari-agent/lib/resource_management/core/providers/system.py", line 262, in action_run
    tries=self.resource.tries, try_sleep=self.resource.try_sleep)
  File "/usr/lib/ambari-agent/lib/resource_management/core/shell.py", line 72, in inner
    result = function(command, **kwargs)
  File "/usr/lib/ambari-agent/lib/resource_management/core/shell.py", line 102, in checked_call
    tries=tries, try_sleep=try_sleep, timeout_kill_strategy=timeout_kill_strategy)
  File "/usr/lib/ambari-agent/lib/resource_management/core/shell.py", line 150, in _call_wrapper
    result = _call(command, **kwargs_copy)
  File "/usr/lib/ambari-agent/lib/resource_management/core/shell.py", line 303, in _call
    raise ExecutionFailed(err_msg, code, out, err)
resource_management.core.exceptions.ExecutionFailed: Execution of 'export HIVE_CONF_DIR=/usr/hdp/current/hive-metastore/conf/conf.server ; /usr/hdp/current/hive-server2-hive2/bin/schematool -initSchema -dbType mysql -userName hive -passWord [PROTECTED] -verbose' returned 1. SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.5.0-292/hive2/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.5.0-292/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL: jdbc:mysql://msl-dpe-perf74.msl.lab/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: com.mysql.cj.jdbc.exceptions.CommunicationsException : Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
SQL Error code: 0
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
at org.apache.hive.beeline.HiveSchemaHelper.getConnectionToMetastore(HiveSchemaHelper.java:80)
at org.apache.hive.beeline.HiveSchemaTool.getConnectionToMetastore(HiveSchemaTool.java:133)
at org.apache.hive.beeline.HiveSchemaTool.testConnectionToMetastore(HiveSchemaTool.java:187)
at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:291)
at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:277)
at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:526)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.RunJar.run(RunJar.java:233)
at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:832)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.apache.hive.beeline.HiveSchemaHelper.getConnectionToMetastore(HiveSchemaHelper.java:76)
... 11 more
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:91)
at com.mysql.cj.NativeSession.connect(NativeSession.java:152)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:952)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:822)
... 17 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:589)
at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:173)
at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65)
... 20 more
*** schemaTool failed ***

[#2]

@Harry Li

Looks like still Hive is unable to connect to mysql database - can you manually connect and check? use the below command to connect.

mysql -u USERNAME -pPASSWORD -h HOSTNAMEORIP DATABASENAME 

[#3]

Hi Amarnath,

Here is what I had tried on the server with hive installed.

root@msl-dpe-perf74:~# mysql -u root -h localhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> quit
Bye

root@msl-dpe-perf74:~# mysql -u root -h msl-dpe-perf74.msl.lab
ERROR 2003 (HY000): Can't connect to MySQL server on 'msl-dpe-perf74.msl.lab' (111)
root@msl-dpe-perf74:~# 

[#4]

@Harry Li are you trying from same host where metastore is failed to come up? can you try simple command line JDBC connect using https://community.hortonworks.com/articles/88645/how-to-test-mysql-jdbc-connection-through-cli.html and see?

[#5]

What I found out was I succeeded when using 'localhost', but access denied when using 'FQDN' name. How can I fix this?

harry.li@msl-dpe-perf74:/usr/local/JDBC-test/jline_sqlline__mysql_connector$   java -Djava.ext.dirs=/usr/local/JDBC-test/jline_sqlline__mysql_connector/ sqlline.SqlLine
sqlline version 1.0.2 by Marc Prud'hommeaux
sqlline> 
sqlline>  !connect jdbc:mysql://localhost:3306/hive hive hive
Connecting to jdbc:mysql://localhost:3306/hive
Connected to: MySQL (version 5.7.23-0ubuntu0.16.04.1)
Driver: MySQL-AB JDBC Driver (version mysql-connector-java-5.1.17-SNAPSHOT ( Revision: ${bzr.revision-id} ))
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:mysql://localhost:3306/hive>  !connect jdbc:mysql://msl-dpe-perf74.msl.lab:3306/hive hive hive
Connecting to jdbc:mysql://msl-dpe-perf74.msl.lab:3306/hive
Error: Access denied for user 'hive'@'msl-dpe-perf74.msl.lab' (using password: YES) (state=28000,code=1045)
1: jdbc:mysql://msl-dpe-perf74.msl.lab:3306/h> 

[#6]

@Harry Li, you can grant access to hive user from any host.

Connect as a root user and run the below SQL statement.

1. GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'localhost' IDENTIFIED BY 'hive';
2. GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'<FQDN_of_Mysql>'IDENTIFIED BY 'hive';

More on that can be found at https://stackoverflow.com/questions/8348506/grant-remote-access-of-mysql-database-from-any-ip-addres...

[#7]

You can execute below SQL and try again.

GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'msl-dpe-perf74.msl.lab'IDENTIFIED BY 'hive'

[#8]

@Harry Li Can you show the content of /etc/hosts/ file?

For your hostname there should be associated IP address.

Ex:

127.0.0.1 localhost

172.26.96.94 msl-dpe-perf74.msl.lab

[#9]

Hi Amarnath,

With your suggested changes, I still cannot start hive metastore with SQL error code 1045. I also tried to comment out bind-address or set it to 0.0.0.0. Do you have more suggestions?

Thanks!

[#10]

Here is the content of /etc/hosts and /etc/hostname. My cluster contains 3 nodes. perf74 is Name Node, perf75 is secondary Name Node and Perf76 is Data node

All 3 machines has very similar setup

harry.li@msl-dpe-perf74:/etc/mysql/mysql.conf.d$  cat /etc/hostname
msl-dpe-perf74.msl.lab
harry.li@msl-dpe-perf74:/etc/mysql/mysql.conf.d$  cat /etc/hosts
127.0.0.1localhost
#127.0.1.1msl-dpe-perf74.msl.lab
10.1.30.221msl-dpe-perf74.msl.lab
10.1.30.223msl-dpe-perf75.msl.lab
10.1.30.192msl-dpe-perf76.msl.lab
10.10.98.64    perf74
10.10.98.43    perf75
10.10.98.51    perf76
10.10.98.67    perf77
# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

[#11]

Hi Amarnath,

A question regarding the JDBC test program you recommended. When run the test, why I only success with 'localhost' but failed with FQDN name? Could you indicating that somewhere, the setting is wrong? Here is what i got:

harry.li@msl-dpe-perf76:/usr/local/JDBC-test$  java -Djava.ext.dirs=/usr/local/JDBC-test/jline_sqlline__mysql_connector/ sqlline.SqlLine
sqlline version 1.0.2 by Marc Prud'hommeaux
sqlline> !connect jdbc:mysql://msl-dpe-perf74.msl.lab:3306/hive hive hive
Connecting to jdbc:mysql://msl-dpe-perf74.msl.lab:3306/hive
Error: Access denied for user 'hive'@'msl-dpe-perf76.msl.lab' (using password: YES) (state=28000,code=1045)
0: jdbc:mysql://msl-dpe-perf74.msl.lab:3306/h> !connect jdbc:mysql://localhost:3306/hive hive hive
Connecting to jdbc:mysql://localhost:3306/hive
Error: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. (state=08S01,code=0)
1: jdbc:mysql://localhost:3306/hive> 

[#12]

Hi Amarnath and Geoffrey,

I cleaned up my system and reinstalled everything with Ambari. The installation went well, but I still cannot start Metastore. But this time, I got different error messages. I created a new ticket. Could you help check it out?

Thanks,

Harry

[#13]

@Harry Li

Can you try to login mysql as "hive" user:

mysql -u hive -passWord  <Password>  -h <FQDN>

[#14]

No. I don't think so. And I don't recall that I created user for mysql during Ambari installation process

root@msl-dpe-perf74:~# mysql -u hive -passWord  hive  -h msl-dpe-perf74.msl.lab
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on 'msl-dpe-perf74.msl.lab' (111)

[#15]

@Harry Li

Do the following as root create the hive user with the following privileges

mysql -u root -h localhost 
Create user hive identified by 'hive'; 
grant all on hive.* to hive; 
GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'localhost' IDENTIFIED BY 'hive'; 
GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'<FQDN_of_Mysql>'IDENTIFIED BY 'hive'; 
flush privileges; 
quit; 

As the hive user with the assumption, here database,user and password are hive for simplicity

mysql -u hive -phive 
create database hive; 
show databases; 
quit;

Now use these credentials for setting up the hive database and user n Ambari user and test connection to the hive database, it should succeed before you can successfully start hive !!

[#16]

I already have hive user in mysql. But seems only good if I use 'local' host. When use FQDN, access to data base is denied

root@msl-dpe-perf74:/home/harry.li# mysql -u hive -phive -h localhost
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 293
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
+--------------------+
2 rows in set (0.00 sec)
mysql> quit
Bye
root@msl-dpe-perf74:/home/harry.li# mysql -u hive -phive -h msl-dpe-perf74.msl.lab
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'hive'@'msl-dpe-perf74.msl.lab' (using password: YES)

[#17]

@Harry Li

Can you share your the output of ,please adopt were necessary the proper user root or use sudo

# hostname -f 

Set the hostname

sudo hostnamectl set-hostname your-new-name 

Now you will need to edit 2 files the /etc/hostname and /etc/hosts file and replace the hostname with your earlier choice above

sudo -H gedit /etc/hostname 
sudo -H gedit /etc/hosts 

Without restarting your machine, just run the command below to restart the hostname service to apply changes:

sudo systemctl restart systemd-logind.service 

When editing the /etc/ hosts, don't tamper with the first line is always going to be for localhost with the loopback IP address.The second line is where you change the hostname

127.0.0.1 localhost 
127.0.0.1 your_new_hostname 

If you want to reference the hostname with the server public IP address and not the loopback, you can add a third line with server public IP and hostname.

127.0.0.1 localhost 
127.0.0.1 your_new_hostname 
10.56.100.30 your_new_hostname    # server IP and hostname  

That should resolve your problem. Please revert

[#18]

Hi Geoffrey,

With changes, I can not access mysql using FQDN, but still failed with hive metastore. The error message is a little different than the original with SQL error code 1045.

Please advise,

Thanks.

Traceback (most recent call last):
  File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 203, in <module>
    HiveMetastore().execute()
  File "/usr/lib/ambari-agent/lib/resource_management/libraries/script/script.py", line 375, in execute
    method(env)
  File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 56, in start
    create_metastore_schema()
  File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive.py", line 417, in create_metastore_schema
    user = params.hive_user
  File "/usr/lib/ambari-agent/lib/resource_management/core/base.py", line 166, in __init__
    self.env.run()
  File "/usr/lib/ambari-agent/lib/resource_management/core/environment.py", line 160, in run
    self.run_action(resource, action)
  File "/usr/lib/ambari-agent/lib/resource_management/core/environment.py", line 124, in run_action
    provider_action()
  File "/usr/lib/ambari-agent/lib/resource_management/core/providers/system.py", line 262, in action_run
    tries=self.resource.tries, try_sleep=self.resource.try_sleep)
  File "/usr/lib/ambari-agent/lib/resource_management/core/shell.py", line 72, in inner
    result = function(command, **kwargs)
  File "/usr/lib/ambari-agent/lib/resource_management/core/shell.py", line 102, in checked_call
    tries=tries, try_sleep=try_sleep, timeout_kill_strategy=timeout_kill_strategy)
  File "/usr/lib/ambari-agent/lib/resource_management/core/shell.py", line 150, in _call_wrapper
    result = _call(command, **kwargs_copy)
  File "/usr/lib/ambari-agent/lib/resource_management/core/shell.py", line 303, in _call
    raise ExecutionFailed(err_msg, code, out, err)
resource_management.core.exceptions.ExecutionFailed: Execution of 'export HIVE_CONF_DIR=/usr/hdp/current/hive-metastore/conf/conf.server ; /usr/hdp/current/hive-server2-hive2/bin/schematool -initSchema -dbType mysql -userName hive -passWord [PROTECTED] -verbose' returned 1. SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.5.0-292/hive2/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.5.0-292/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL: jdbc:mysql://msl-dpe-perf74.msl.lab/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.
Wed Aug 01 16:01:39 PDT 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: java.sql.SQLException : Access denied for user 'hive'@'localhost' (using password: YES)
SQL Error code: 1045
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
at org.apache.hive.beeline.HiveSchemaHelper.getConnectionToMetastore(HiveSchemaHelper.java:80)
at org.apache.hive.beeline.HiveSchemaTool.getConnectionToMetastore(HiveSchemaTool.java:133)
at org.apache.hive.beeline.HiveSchemaTool.testConnectionToMetastore(HiveSchemaTool.java:187)
at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:291)
at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:277)
at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:526)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.RunJar.run(RunJar.java:233)
at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
Caused by: java.sql.SQLException: Access denied for user 'hive'@'localhost' (using password: YES)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:832)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.apache.hive.beeline.HiveSchemaHelper.getConnectionToMetastore(HiveSchemaHelper.java:76)
... 11 more
*** schemaTool failed ***

[#19]

@Harry Li

Can you run the below as root on against your Mysql databases, assuming again the user/password and db is hive

GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'localhost' IDENTIFIED BY 'hive'; 
flush privileges; 
quit;

The retry I see now is a permission issue.

HTH

[#20]

Tried, no help. I also listed all users available from mysql below

harry.li@msl-dpe-perf74:/etc/mysql/mysql.conf.d$  sudo mysql -u root -h localhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'localhost' IDENTIFIED BY 'hive'; 
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
Bye
harry.li@msl-dpe-perf74:/etc/mysql/mysql.conf.d$  sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
harry.li@msl-dpe-perf74:/etc/mysql/mysql.conf.d$ 

harry.li@msl-dpe-perf74:/etc/mysql/mysql.conf.d$  mysql -u hive -phive -h msl-dpe-perf74.msl.lab
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
+--------------------+
2 rows in set (0.00 sec)
mysql> quit;
Bye

[#21]

@Harry Li

Try following:

create user 'hive'@'msl-dpe-perf74.msl.lab' identified by 'hive';
grant all privileges on *.* to 'hive'@'msl-dpe-perf74.msl.lab';
SHOW GRANTS FOR 'hive'@'msl-dpe-perf74.msl.lab';
flush privileges;
commit;
quit;

Set the password of 'hive'@'msl-dpe-perf74.msl.lab' same as what you have given via Ambari.

Other thing I could see from the error you mentioned in the beginning on this post was :

org.apache.hadoop.hive.metastore.HiveMetaException:Failed to get schema version

Can you check if following works for you:

/usr/hdp/current/hive/bin/schematool -initSchema -dbType  mysql