Support Questions

Find answers, ask questions, and share your expertise

Hive Metastore Not able connect MYSQL database while installation Hive Service

Expert Contributor

Not able to Test the MYSQL database connection from the Ambari-server , while adding the Hive service.

13 REPLIES 13

Super Mentor

@zkfs

1. Do you see any error in ambari-server.log?

2. Do you have the mysql-connector-java.jar jdbc driver installed on ambari host and the symlink is present in the "/usr/share/java" directory? Else run the following command on the Ambari Server host to make the JDBC driver available and to enable testing the database connection.

 

ambari-server setup --jdbc-db=mysql --jdbc-driver=/path/to/mysql/mysql-connector-java.jar

 

3. From ambari host are you able to telnet to mysql host & port? (just to isolate the firewall/network issue).

4. Verify if the mysql username & password is correct and has remote connection permission. As mentioned in: http://docs.hortonworks.com/HDPDocuments/Ambari-2.4.2.0/bk_ambari-reference/content/using_hive_with_...

.

Expert Contributor
3)[root@centos ambari-server]# telnet centos2
Trying 192.168.154.113...
telnet: connect to address 192.168.154.113: Connection refused
[root@centos ambari-server]#

Please advise

Super Mentor

@zkfs

You are doing telnet on default port. Can you check your MySQL port and the use that connector port to see ambari is able to connect to mysql on that port?

Disable firewall on MySQl port (default mysql port is 3306)

Example: if "centos2" is your mysql host and mysql port is 3306 then you might check like following:

# telnet centos2  3306

. Or check if your MySQL is using the same port?

Else fix the N/W or firewall issue to allow access to that mysql port from ambari host.

Super Mentor

@zkfs

Also have you performed the Hive MySQl setup as mentioned in the previously shared doc. like user creation and giving enough permission to the user.

Example: Where <HIVEUSER> is the Hive user name, <HIVEPASSWORD> is the Hive user password and <HIVEMETASTOREFQDN> is the Fully Qualified Domain Name of the Hive Metastore host.

 # 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;

Expert Contributor

sure i try for as below and update you.

Jay SenSharma  zkfs · 13 hours ago 0 Vote   Reply Share  More...

@zkfs
You are doing telnet on default port. Can you check your MySQL port and the use that connector port to see ambari is able to connect to mysql on that port?
Disable firewall on MySQl port (default mysql port is 3306)
Example: if "centos2" is your mysql host and mysql port is 3306 then you might check like following:
# telnet centos2  3306. Or check if your MySQL is using the same port?
Else fix the N/W or firewall issue to allow access to that mysql port from ambari host.


Expert Contributor

I am getting the response from server

[root@centos ~]# telnet centos2.test.com 3306 Trying 192.168.154.113... Connected to centos2.test.com. Escape character is '^]'. J 5.6.35BNBA\/▒<1!ONu%U^Vtgmysql_native_password ^CConnection closed by foreign host.

[root@centos ~]#

Expert Contributor

1) No error in ambar-server log as below.

28 Mar 2017 21:43:08,779  INFO [main] Configuration:791 - Reading password from existing file
28 Mar 2017 21:43:08,791  INFO [main] Configuration:1128 - Hosts Mapping File null
28 Mar 2017 21:43:08,791  INFO [main] HostsMap:60 - Using hostsmap file null
28 Mar 2017 21:43:09,327  INFO [main] ControllerModule:195 - Detected POSTGRES as the database typ from the JDBC URL
2) [root@centos ambari-server]# ls -lrt /usr/share/java/mysql-connector-java.jar
lrwxrwxrwx 1 root root 31 Mar 23 20:26 /usr/share/java/mysql-connector-java.jar -> mysql-connector-java-5.1.29.jar

Super Mentor

@zkfs

Ambari log information indicates that you are using Postgres for Ambari Server which is OK. Because ambari might be using Postgres for ambari DB connectivity.

Detected POSTGRES as the database typ from the JDBC URL

.

You are facing issue with HIve (mysql database) connectivity. So please check if you are following all the informations mentioned the below link to Verify if the mysql username & password is correct and has remote connection permission and Firewall issues.. As mentioned in: http://docs.hortonworks.com/HDPDocuments/Ambari-2.4.2.0/bk_ambari-reference/content/using_hive_with_...

Expert Contributor

Thanks for immediate reply, yep we followed the same while installation and database as below.

From the Mysql database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |


@zkfs

Are you able to connect mysql using hive user and password?

try this first?

mysql -u <hive db user> -p <hive db user password>

Expert Contributor

Please find information as below

[root@centos2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql>


you are able to connect mysql with root user, please try with hive user as well for connecting hive DB.

Explorer

I also faced the same issue.Found the issue was with mysql-connector-java.jar. I followed the below steps 

1. Check whether you are able to connect remotely to mysql database.

2.If you are able to connect , then , its mysql-connector-java.jar in ambari

3. Download the correct version of mysql jar from https://dev.mysql.com/downloads/connector/j/

4. Stop ambari server .

5.Remove the mysql connector jar from ambari

6. Set up again using

ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/ mysql-connector-java-8.0.16

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.