Unable to connect to Hiveserver2 in HDP 3.1

[#1]

Hi Team,

2 below errors i am facing..

Just newly deployed Hdp 3.1 cluster and want to configure Hive.. having below error.. 

1. ERROR: Unable to connect to the DB. Please check DB connection properties.
org.postgresql.util.PSQLException: Connection to hostname:5432 refused.
Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

2. and after going for zookeeper client and doing ls -/hiveserver2 it showing below error

Node does not exist: /hiveserver2

Please share the steps.. Thanks in Advance

[#2]

@irfangk1 

Looks like you are using Postgres as Non Default Database for your Hive.

It will be good to verify few things:

1. If you have setup your Postgres Database for Hive as mentioned in the doc: https://docs.cloudera.com/HDPDocuments/Ambari-2.7.3.0/administering-ambari/content/amb_using_hive_wi...

2. On the Postgres Database host please verify if the Postgres Database is running fine and the port  "5432" is listening?

# netstat -tnlpa | grep 5432

NOTE: please take a look at your postgres Db configs to be double sure that it is configured to listen to 5432 port.  You can find the ports your postgres is listening to by passing the Postgres PID to the following command:

# netstat -tnlpa | grep $POSTGRES_PID

3. On the Postgres Database host please verify if the "firewall" /"iptables" is disabled  so that from the Hive Metastore host the Postgres Post can be accessed.

4. From HiveMetastore host please try to telnet / netcat the Postgres DB host & port to verify if at the network level the port is accessible ?

# telnet  $POSTGRES_DB_HOSTNAME   5432
(OR)
# nv -v  $POSTGRES_DB_HOSTNAME   5432

 .

5. Regarding the "/hiveserver2" Zookeeper znode.... it will be available only when your HiveServer2 starts successfully without any issue.   So please verify the Hive Metastore and hive server2 logs first once they are running fine then you should see the Znode without any issue.

If you still face any issue then please share the above requested informations along with the Postgres Server log and the Hive Logs.


If your question is answered then, Please make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.

[#3]

I am getting the below error as Hiveserver2 is in stop mode

2019-09-27 15:41:46,351 - Retrying after 10 seconds. Reason: Execution of '/usr/jdk64/jdk1.8.0_112/bin/java -cp /usr/lib/ambari-agent/DBConnectionVerification.jar:/usr/hdp/current/hive-server2/lib/postgresql-42.2.8.jar org.apache.ambari.server.DBConnectionVerification 'jdbc:postgresql://inairsr542007v3.ntil.com:5432/hive' hive [PROTECTED] org.postgresql.Driver' returned 1. ERROR: Unable to connect to the DB. Please check DB connection properties.
org.postgresql.util.PSQLException: Connection to inairsr542007v3.ntil.com:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

[#4]

@irfangk1 

 As we see :

Connection to inairsr542007v3.xxxxxxx.com:5432 refused.

So please check the telnet / netstat commands which i shared in my previous update to findout if your Postgres DB is actually running fine or not and if that is accessible from the HiveMetastore/ HS2 instances or not?

ON Postgresd DB Host (Port Bind and listening Check / Firewall check)
----------------------------
# netstat -tnlpa | grep 5432
# netstat -tnlpa | grep $POSTGRES_PID
# systemctl status firewalld
# systemctl stop firewalld
(OR)
# service iptables stop 


ON HiveMetastore/HS2 host (Connection check)
----------------------------
# telnet  $POSTGRES_DB_HOSTNAME   5432
# nv -v  $POSTGRES_DB_HOSTNAME   5432

.

[#5]

Sys DB and Information Schema not created yet

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, returns=returns)
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 314, in _call
raise ExecutionFailed(err_msg, code, out, err)
resource_management.core.exceptions.ExecutionFailed: Execution of '/usr/jdk64/jdk1.8.0_112/bin/java -cp /usr/lib/ambari-agent/DBConnectionVerification.jar
:/usr/hdp/current/hive-server2/lib/postgresql-42.2.8.jar org.apache.ambari.server.DBConnectionVerification 'jdbc:postgresql://inairsr542007v3.ntil.com:5432/hive' hive [PROTECTED] org.postgresql.Driver' returned 1. ERROR: Unable to connect to the DB. Please check DB connection properties.
org.postgresql.util.PSQLException: Connection to inairsr542007v3.ntil.com:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

[#6]

@irfangk1 

You have not responded with the command output that we requested. So it is hard to guess what might be wrong.

We see the errors as following:

org.postgresql.util.PSQLException: Connection to inairsr542007v3.ntil.com:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Possible Cause:

1. Either your Postgres DB is not running.

2. Postgres DB hostname Or Port is not correctly specified.

3. Postgres Ports are not accessible remotely.

So in order to isolate the issue and find out what might be causing the Postgres connection failure we will need the following informations.  I will repost my comment which needs to be looks up first.

Please check the telnet / netstat commands which i shared in my previous update to findout if your Postgres DB is actually running fine or not and if that is accessible from the HiveMetastore/ HS2 instances or not?

ON Postgresd DB Host (Port Bind and listening Check / Firewall check)
----------------------------
# netstat -tnlpa | grep 5432
# netstat -tnlpa | grep $POSTGRES_PID
# systemctl status firewalld
# systemctl stop firewalld
(OR)
# service iptables stop 


ON HiveMetastore/HS2 host (Connection check)
----------------------------
# telnet  $POSTGRES_DB_HOSTNAME   5432
# nv -v  $POSTGRES_DB_HOSTNAME   5432

.

Also it will be best to see your PostgresDB logs if the issue persist.

[#7]

Hi,

The  out put of above cmd is as follows

# netstat -tnlpa | grep 5432
Listen & Establish
# netstat -tnlpa | grep $POSTGRES_PID
Usage: grep [OPTION]... PATTERN [FILE]...
Try 'grep --help' for more information.

# systemctl status firewalld - Dead
# systemctl stop firewalld
(OR)
# service iptables stop - iptables.service not loaded.

ON HiveMetastore/HS2 host (Connection check)
----------------------------
# telnet $POSTGRES_DB_HOSTNAME 5432
Trying 0.0.21.56...
telnet: Unable to connect to remote host: Invalid argument
# nv -v $POSTGRES_DB_HOSTNAME 5432
command not found

[#8]

Hi,

Previously i was on Postgresql, now i am using mysql and today i found a new issue. i am not able to start mysql.. below are the error..

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.