Hive Metastore does not start

[#1]

Hive Metastore doesn't start. It seems there is an issue with the hive database creation or connection. It's been two days I am going throw this issue. Do you have any suggestions ?

Here is the stderr :

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 260, in <module>
    HiveMetastore().execute()
  File "/usr/lib/python2.6/site-packages/resource_management/libraries/script/script.py", line 314, in execute
    method(env)
  File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 60, in start
    create_metastore_schema()
  File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive.py", line 371, in create_metastore_schema
    user = params.hive_user
  File "/usr/lib/python2.6/site-packages/resource_management/core/base.py", line 155, in __init__
    self.env.run()
  File "/usr/lib/python2.6/site-packages/resource_management/core/environment.py", line 160, in run
    self.run_action(resource, action)
  File "/usr/lib/python2.6/site-packages/resource_management/core/environment.py", line 124, in run_action
    provider_action()
  File "/usr/lib/python2.6/site-packages/resource_management/core/providers/system.py", line 262, in action_run
    tries=self.resource.tries, try_sleep=self.resource.try_sleep)
  File "/usr/lib/python2.6/site-packages/resource_management/core/shell.py", line 72, in inner
    result = function(command, **kwargs)
  File "/usr/lib/python2.6/site-packages/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/python2.6/site-packages/resource_management/core/shell.py", line 150, in _call_wrapper
    result = _call(command, **kwargs_copy)
  File "/usr/lib/python2.6/site-packages/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.0.3-8/hive2/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.0.3-8/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://adrien.cluster/hive?createDatabaseIfNotExist=true
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 hive
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: com.mysql.jdbc.exceptions.jdbc4.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.jdbc.exceptions.jdbc4.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 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.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:983)
	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:339)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2252)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2285)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2084)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:795)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
	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.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
	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: 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.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:214)
	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:298)
	... 26 more
*** schemaTool failed ***

[#2]

@Martin Muller

based on the error message - it appears like Hive is not able to connect to the database. is the metastore and hive DB is in same host? can you try to connect manually and see if there is any issue with DB.

[#3]

There are 2 solutions to this :

1. Change the value of 'bind-address' in the mysql configuration file /etc/mysql/mysql.conf.d/mysqld.cnf to '0.0.0.0' cd /etc/mysql/mysql.conf.d gedit mysqld.cnf

2. Change the mysql server hostname with IP address in the 'Metastore connection URL'. So in your case change 'jdbc:mysql://adrien.cluster/hive?createDatabaseIfNotExist=true' to 'jdbc:mysql://127.0.0.1/hive?createDatabaseIfNotExist=true'. If the ip address is different from 127.0.0.1 then please use that.

[#4]

Thanks! Thats really usefull!

[#5]

1. Connect to mysql as root.

2. Execute these queries.

mysql> use mysql;

mysql> select * User,Host,Password from mysql;

Normally, that returned two or three results for user 'hive'.

hive | localhost | *encodedPassword

hive | adrien.cluster | *encodedPassword

hive | % | *encodedPassword

if you can't get above results, then execute queries.

mysql> create user 'hive'@'adrien.cluster' identified by 'PASSWORD'

mysql> create user 'hive'@'%' identified by 'PASSWORD'

mysql> grant all privileges on *.* to 'hive'@'adrien.cluster'

mysql> grant all privileges on *.* to 'hive'@'%'

mysql> flush privileges;

3. Try connection test in Ambari Web's Hive menu.

[#6]

In my case I was able to connect to mysql from terminal using user hive [mysql -u hive -p] . So I was sure that the issue was happening only when trying to connect to mysql programatically using JDBC driver.

But when tried to establish connection from java then also got the 'Communications link failure' . But once I replace the server name FQDN in JDBC url with 127.0.0.1 the problem is gone.

Alternately I change the value of 'bind-address' in the mysql configuration file /etc/mysql/mysql.conf.d/mysqld.cnf to '0.0.0.0'. Then the connection got established even with server FQDN in JDBC url.