Pivotal Knowledge Base

Follow

HiveServer2 fails to start because host "is not allowed to connect to this MySQL server"

Environment

Product Version
Pivotal HD 3.0.x
Ambari 1.7.1 / 2.1.2
HIVE 0.14.x

Symptom

When trying to start HiveServer2 via Ambari the following error is seen:

 

Error Message:

Extract of error message text seen in Ambari:

2016-04-25 13:29:33,102 - Ranger admin not installed
2016-04-25 13:29:33,103 - call['ambari-sudo.sh su hive -l -s /bin/bash -c 'hive --config /etc/hive/conf.server --service metatool -listFSRoot' 2>/dev/null | grep hdfs:// | cut -f1,2,3 -d '/' | grep -v 'hdfs://phd301.lab:8020' | head -1'] {}
2016-04-25 13:29:41,003 - call returned (0, '')
2016-04-25 13:29:41,004 - Execute['/var/lib/ambari-agent/tmp/start_hiveserver2_script /var/log/hive/hive-server2.out /var/log/hive/hive-server2.log /var/run/hive/hive-server.pid /etc/hive/conf.server /var/log/hive'] {'environment': {'HIVE_BIN': 'hive', 'JAVA_HOME': '/usr/jdk64/jdk1.7.0_67', 'HADOOP_HOME': '/usr/phd/current/hadoop-client'}, 'not_if': "ls /var/run/hive/hive-server.pid >/dev/null 2>&1 && ps -p `ambari-sudo.sh su hive -l -s /bin/bash -c 'cat /var/run/hive/hive-server.pid'` >/dev/null 2>&1", 'user': 'hive', 'path': ['/usr/sbin:/sbin:/usr/lib/ambari-server/*:/sbin:/usr/sbin:/bin:/usr/bin:/var/lib/ambari-agent:/usr/phd/current/hive-server2/bin:/usr/phd/current/hadoop-client/bin']}
2016-04-25 13:29:41,038 - Execute['/usr/jdk64/jdk1.7.0_67/bin/java -cp /usr/lib/ambari-agent/DBConnectionVerification.jar:/usr/phd/current/hive-server2/lib/mysql-connector-java.jar org.apache.ambari.server.DBConnectionVerification 'jdbc:mysql://dn2.lab/hive?createDatabaseIfNotExist=true' hive [PROTECTED] com.mysql.jdbc.Driver'] {'path': ['/usr/sbin:/sbin:/usr/local/bin:/bin:/usr/bin'], 'tries': 5, 'try_sleep': 10}
2016-04-25 13:29:42,121 - Retrying after 10 seconds. Reason: Execution of '/usr/jdk64/jdk1.7.0_67/bin/java -cp /usr/lib/ambari-agent/DBConnectionVerification.jar:/usr/phd/current/hive-server2/lib/mysql-connector-java.jar org.apache.ambari.server.DBConnectionVerification 'jdbc:mysql://dn2.lab/hive?createDatabaseIfNotExist=true' hive [PROTECTED] com.mysql.jdbc.Driver' returned 1. ERROR: Unable to connect to the DB. Please check DB connection properties.
java.sql.SQLException: null,  message from server: "Host 'dn1.lab' is not allowed to connect to this MySQL server"
2016-04-25 13:29:53,536 - Retrying after 10 seconds. Reason: Execution of '/usr/jdk64/jdk1.7.0_67/bin/java -cp /usr/lib/ambari-agent/DBConnectionVerification.jar:/usr/phd/current/hive-server2/lib/mysql-connector-java.jar org.apache.ambari.server.DBConnectionVerification 'jdbc:mysql://dn2.lab/hive?createDatabaseIfNotExist=true' hive [PROTECTED] com.mysql.jdbc.Driver' returned 1. ERROR: Unable to connect to the DB. Please check DB connection properties.

Cause

The HIVE metastore is a different host to the host running HiveServer2. Permissions on the metastore do not allow connections from the HiveServer2 host.

Resolution

The steps below explain how to set access up via MySQL if another database engine is used for the HIVE Metastore please refer to the database documentation.

1. Log into the HIVE Metastore node via SSH.

2. Locate the "hive" password for MySQL and confirm the login works then exit:

[root@dn2 ~]# mysql -h dn2.lab -u hive -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 214
Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, 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> exit
Bye
[root@dn2 ~]#

3. Log into mysql with the user "root":

[root@dn2 ~]# mysql -h dn2.lab
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 223
Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, 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>

4. Create a new "hive" user using the hostname of the server where HiveServer2 is running (in the example the hostname where HiveServer2 is running is dn1.lab) and where 'changeme' is the password found in step 2:

mysql> CREATE USER 'hive'@'dn1.lab' IDENTIFIED BY 'changeme';
Query OK, 0 rows affected (0.01 sec)

5. Confirm the user has been created and take note of the "hashed" password:

mysql> select Host,User,Password from mysql.user;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | |
| dn2.lab | root | |
| 127.0.0.1 | root | |
| dn1.lab | hive | *7ACE763ED393514FE0C162B93996ECD195FFC4F5 |
| dn2.lab | hive | *7ACE763ED393514FE0C162B93996ECD195FFC4F5 |
+-----------+------+-------------------------------------------+
5 rows in set (0.00 sec) mysql>

6. Grant permissions for the new user. In the field after "password" the "hashed" password found in step 5 that should be used:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'hive'@'dn1.lab' IDENTIFIED BY PASSWORD '*7ACE763ED393514FE0C162B93996ECD195FFC4F5';
Query OK, 0 rows affected (0.00 sec)
mysql>

7. Grant access to the database for the user:

mysql> GRANT ALL on hive.* to 'hive'@'dn1.lab';

8. Flush the privileges: 

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec) mysql>

10. Confirm the grants have been set up correctly: 

mysql> show grants for 'hive'@'dn1.lab';
+-----------------------------------------------------------------------------------------------------------+
| Grants for hive@dn1.lab |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hive'@'dn1.lab' IDENTIFIED BY PASSWORD '*7ACE763ED393514FE0C162B93996ECD195FFC4F5' |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) mysql>

9. Restart HiveServer2 via Ambari. 

Comments

Powered by Zendesk