Ranger Test Connection Failed

[#1]

Hi Team,

I am getting below error while doing db test connection on ranger.(Using Mysql as a back end) Please help me to fix this.

2021-01-05 03:44:52,150 - Host checks started.
2021-01-05 03:44:52,151 - Check execute list: db_connection_check
2021-01-05 03:44:52,151 - DB connection check started.
WARNING: File /var/lib/ambari-agent/cache/DBConnectionVerification.jar already exists, assuming it was downloaded before
WARNING: File /var/lib/ambari-agent/cache/mysql-connector-java.jar already exists, assuming it was downloaded before
2021-01-05 03:44:52,155 - call['/usr/jdk64/jdk1.8.0_112/bin/java -cp /var/lib/ambari-agent/cache/DBConnectionVerification.jar:/var/lib/ambari-agent/cache/mysql-connector-java.jar -Djava.library.path=/var/lib/ambari-agent/cache org.apache.ambari.server.DBConnectionVerification "jdbc:mysql://master.hadoop:3306/ranger" "rangeradmin" [PROTECTED] com.mysql.jdbc.Driver'] {}
2021-01-05 03:44:53,464 - call returned (1, "Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.\nERROR: Unable to connect to the DB. Please check DB connection properties.\ncom.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure\n\nThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.")
2021-01-05 03:44:53,465 - DB connection check completed.
2021-01-05 03:44:53,467 - Host checks completed.
2021-01-05 03:44:53,467 - Check db_connection_check was unsuccessful. Exit code: 1. Message: Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
ERROR: Unable to connect to the DB. Please check DB connection properties.
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Command failed after 1 tries

[#2]

@saivenkatg55 

My Assumptions

You already executed the HDP environment preparation. If not see ​prepare the environment
https://docs.cloudera.com/HDPDocuments/Ambari-2.7.3.0/bk_ambari-installation/content/prepare_the_env...
You are running on Linux [RedHat, Centos] and you have root access!

Note: Replace test.ambari.com with the output of your $ hostname -f

 Re-adapt to fit your cluster

# root password = welcome1
# hostname = test.ambari.com 
# ranger user and password is the same

Steps

 Install the MySQL connector if not installed [Optional]

# yum install -y mysql-connector-java

 Shutdown Ambari

# ambari-server stop

Re-run the below command it won't hurt

# ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar

 Backup the ambari server properties file

# cp /etc/ambari-server/conf/ambari.properties /etc/ambari-server/conf/ambari.properties.bak

Change the timeout of the ambari server

# echo 'server.startup.web.timeout=120' >> /etc/ambari-server/conf/ambari.properties
# echo 'server.jdbc.connection-pool.acquisition-size=5' >> /etc/ambari-server/conf/ambari.properties
# echo 'server.jdbc.connection-pool.max-age=0' >> /etc/ambari-server/conf/ambari.properties
# echo 'server.jdbc.connection-pool.max-idle-time=14400' >> /etc/ambari-server/conf/ambari.properties
# echo 'server.jdbc.connection-pool.max-idle-time-excess=0' >> /etc/ambari-server/conf/ambari.properties
# echo 'server.jdbc.connection-pool.idle-test-interval=7200' >> /etc/ambari-server/conf/ambari.properties

Recreate a new ranger schema & Database

# mysql -u root -pwelcome1
CREATE USER 'rangernew'@'%' IDENTIFIED BY 'rangernew';
GRANT ALL PRIVILEGES ON *.* TO 'rangernew'@'localhost';
CREATE USER 'rangernew'@'%' IDENTIFIED BY 'rangernew';
GRANT ALL PRIVILEGES ON rangernew.* TO 'rangernew'@'%';
GRANT ALL PRIVILEGES ON rangernew.* TO 'rangernew'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON rangernew.* to 'rangernew'@'localhost' identified by 'rangernew';
GRANT ALL PRIVILEGES ON rangernew.* to 'rangernew'@'test.ambari.com' identified by 'rangernew';
GRANT ALL PRIVILEGES ON rangernew.* TO 'rangernew'@'test.ambari.com';
GRANT ALL PRIVILEGES ON rangernew.* TO 'rangernew'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit;

 Create the new ranger database

# mysql -u rangernew -prangernew
create database rangernew;
show databases;
quit;

Start the ambari server

# ambari-server start
......Desired output.........
..................
.................
Ambari Server 'start' completed successfully.

For ranger Ambari UI setup
Use the hostname in this example test.ambari.com and the corresponding passwords

Test the Ranger DB connectivity
The connection test should succeed if it does then you can now start Ranger successfully.

Drop the old Ranger DB

# mysql -u root -pwelcome1
mysql> Drop database old_Ranger_name;

The above steps should resolve your Ranger issue.

Was your question answered? If so make sure to mark the answer as the accepted solution.
If you find a reply useful, Kudos this answer by hitting the thumbs up button.