Hive Metastore Not able connect MYSQL database while installation Hive Service

[#1]

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

[#2]

@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_...

.

[#3]

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

[#4]

@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.

[#5]

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

[#6]

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.

[#7]

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 ~]#

[#8]

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

[#9]

@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_...

[#10]

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               |