How to Stop service using Ambari DB if Ambari API is not working

[#1]

1. Lets assume you have HDP cluster installed and managed by Ambari.

2. When we want to delete a service [either Custom service or HDP service] using api, you generally use below command -

curl -u admin:admin -H "X-Requested-By: ambari" -X DELETE http://<ambari-server>:8080/api/v1/clusters/c1/services/<SERVICENAME>;

2. After executing above command you might see below error -

$curl -u admin:admin -H "X-Requested-By: ambari" -X DELETE http://<ambari-server>:8080/api/v1/clusters/c1/services/HBASE

{ "status" : 500, "message" : "org.apache.ambari.server.controller.spi.SystemException: An internal system exception occurred: Cannot remove HBASE. Desired state STARTED is not removable. Service must be stopped or disabled." 

3. If you see the above error while Removing/Stopping service please use below steps to resolve the issue

4. Login to ambari database [In my case its postgresql] and check the values of the service in below tables -

# psql -U ambari
[Default password is 'bigdata']

ambari=> select * from servicedesiredstate where service_name='HBASE';
ambari=> select * from servicecomponentdesiredstate where service_name='HBASE';

5. Make sure here in above output the value of column 'desired_state' should be INSTALLED

6. If you see the above value of "desired_state" is set to STARTED then update the column and set it to STARTED using below command -

ambari=> update servicedesiredstate set desired_state='INSTALLED' where service_name='HBASE';

7. Follow same steps for "servicecomponentdesiredstate" table -

ambari=> update servicecomponentdesiredstate set desired_state='INSTALLED' where service_name='HBASE';

8. Now try removing/deleting the service now. It should work.

$curl -u admin:admin -H "X-Requested-By: ambari" -X DELETE http://<ambari-server>:8080/api/v1/clusters/c1/services/HBASE