Getting past "Access denied for user" error during Hive Metastore failure

[#1]

Error

org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: java.sql.SQLException : Access denied for user 'hive'@'node1.hortonworks.com' (using password: YES)
SQL Error code: 1045
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)

Background

The above output is an attempt of Ambari trying to initiate the hive metastore process based on the parameters supplied in the following properites

javax.jdo.option.ConnectionDriverName
javax.jdo.option.ConnectionURL
javax.jdo.option.ConnectionUserName	

This is mostly not required if all the credentials are aligned, however, its possible we might end up with such issues when we are trying to use customized DB access. The above error is relevant to mysql db as metastore repository, however, same principal can be applied to other DBs as well.

Mysql's authentication mechanism allows user/host credentials to be defined in a certain order. Here is an example.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> select user,host from user;
+------+-----------------------+
| user | host                  |
+------+-----------------------+
| hive | %                     |
| hive | 127.0.0.1             |
| root | 127.0.0.1             |
| hive | ::1                   |
| root | ::1                   |
| hive | localhost             |
| root | localhost             |
| hive | node1.hortonworks.com |
| root | node1.hortonworks.com |
| hive | node2.hortonworks.com |
| hive | node3.hortonworks.com |
+------+-----------------------+
11 rows in set (0.00 sec)

We can verify the error as indicated by the ambari log output at mysql level, using the superuser access, because this is what Ambari server is trying to do.

[root@node1 ~]# mysql -u hive -h node1.hortonworks.com -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'hive'@'node1.hortonworks.com' (using password: YES)

How-to-fix

• Observe the output before exception from Ambari log, look at the username, password and the command string which is being attempted.

resource_management.core.exceptions.Fail: 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. which: no hbase in (/usr/sbin:/sbin:/usr/lib/ambari-server/*:/usr/sbin:/sbin:/usr/lib/ambari-server/*:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.111-1.b15.el7_2.x86_64/jre/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/var/lib/ambari-agent:/var/lib/ambari-agent)

• If you are not using the root user, ensure that you add another property to hive-site.xml i.e., javax.jdo.option.ConnectionPassword with password for the user that is configured for property javax.jdo.option.ConnectionUserName
• At mysql level, login as root user and grant with the following syntax (you might need to execute 'flush privileges' post this, not necessary. We do need to note that if you do not specify "identified by", the error would still remain even though the user 'hive' has been configured to be identified by 'password' which was set while creating the user.

mysql> grant all on *.* to 'hive'@'node1.hortonworks.com' identified by 'changeme';
Query OK, 0 rows affected (0.00 sec)

• Even if local to the host, test out if you are able to login with the supplied credentials

[root@node1 ~]# mysql -u hive -h node1.hortonworks.com -p 
Enter password: 

[#2]

Hello i've performend all steps

creating and granting privileges. But it still says this. But if I try to login using that user 'hive'@'metastore_fqdn' I'm able to login using password provided.

But if I restart the hive it still says same error. Please some help.

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.2.0-205/hive2/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.2.0-205/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://vm-hadoop-x1.fhills.local/hive
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 hive
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: java.sql.SQLException : Access denied for user 'hive'@'vm-hadoop-s3.fhills.local' (using password: YES)
SQL Error code: 1045
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: java.sql.SQLException: Access denied for user 'hive'@'vm-hadoop-s3.fhills.local' (using password: YES)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
	at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2488)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2521)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:839)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
	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:411)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:421)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:350)
	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