HiveServer2 fails to start because host "is not allowed to connect to this MySQL server"
Article ID: 295014
Updated On:
Products
Services Suite
Issue/Introduction
Symptoms:
When trying to start HiveServer2 via Ambari, the following error is seen:
Error Message:
Extract of the 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.
Environment
Cause
The HIVE metastore is a different host than 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, 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 the 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 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 [email protected] | +-----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'hive'@'dn1.lab' IDENTIFIED BY PASSWORD '*7ACE763ED393514FE0C162B93996ECD195FFC4F5' | +-----------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
9. Restart HiveServer2 via Ambari.
Feedback
Yes
No
Powered by
