Support Questions
Find answers, ask questions, and share your expertise

NO able to connect to metastore mysql database

Explorer

[ec2-user@ip-192-168-0-42 etc]$ mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [ec2-user@ip-192-168-0-42 etc]$

SLF4J: Found binding in [jar:file:/usr/hdp/2.5.5.0-157/hive2/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.5.5.0-157/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://ip-192-168-0-42.eu-west-1.compute.internal/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: java.sql.SQLException : null,  message from server: "Host 'ip-192-168-0-50.eu-west-1.compute.internal' is not allowed to connect to this MySQL server"
SQL Error code: 1130
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: null,  message from server: "Host 'ip-192-168-0-50.eu-west-1.compute.internal' is not allowed to connect to this MySQL server"
3 REPLIES 3

Re: NO able to connect to metastore mysql database

Super Mentor

@Priyaranjan Swain

Regarding the Error:

[ec2-user@ip-192-168-0-42 etc]$ mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Please reset the "root" user password for your MySQL as described in : https://community.hortonworks.com/questions/141454/what-is-the-mysql-root-password-from-hive-install...

.

Regarding the error while starting/connecting HiveServer metastore. Please refer to the following doc to setup the MySQL DB for Hive: https://docs.hortonworks.com/HDPDocuments/Ambari-2.5.2.0/bk_ambari-administration/content/using_hive...

# mysql -u root -p
CREATE USER '<HIVEUSER>'@'localhost' IDENTIFIED BY '<HIVEPASSWORD>';
GRANT ALL PRIVILEGES ON *.* TO '<HIVEUSER>'@'localhost';
CREATE USER '<HIVEUSER>'@'%' IDENTIFIED BY '<HIVEPASSWORD>';
GRANT ALL PRIVILEGES ON *.* TO '<HIVEUSER>'@'%';
CREATE USER '<HIVEUSER>'@'<HIVEMETASTOREFQDN>' IDENTIFIED BY '<HIVEPASSWORD>';
GRANT ALL PRIVILEGES ON *.* TO '<HIVEUSER>'@'<HIVEMETASTOREFQDN>';
FLUSH PRIVILEGES;
CREATE DATABASE <HIVEDATABASE>

.

Re: NO able to connect to metastore mysql database

Explorer

Hi Jay,

Thanks for your reply.. I tried with this but no luck..

[root@ip-192-168-0-42 etc]# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@ip-192-168-0-42 etc]# mysql -urootmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 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> UPDATE user SET Password=PASSWORD('root@123') where USER='root'; ERROR 1046 (3D000): No database selected 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> UPDATE user SET Password=PASSWORD('root@123') where USER='root'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)

mysql> exit Bye

[root@ip-192-168-0-42 etc]# mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Still not able to log in as root

Re: NO able to connect to metastore mysql database

Mentor

@Priyaranjan Swain

This should have been the correct sequence

# # mysql -uroot -p
xxxx
xxxxxx
xxxxx
mysql> use mysql;
mysql> update user set password=PASSWORD('New_password')where User='root';
mysql> flush privileges;
mysql> quit

Now after this you should be able to connect as root with the password (New_password ) please set the password as desired