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