Deleting a Service From Ambari Database

[#1]

I was stuck in a situation where service(in my case Mahout) was not present in Ambari UI but it was present in Ambari database (as a installed/stuck service) which was causing upgrade issues. After reading couple of blogs I found these set of sql statement to delete service from Ambari database:

ALTER TABLE clusterservices DISABLE TRIGGER ALL;

ALTER TABLE servicecomponentdesiredstate DISABLE TRIGGER ALL;

DELETE FROM servicedesiredstate WHERE service_name = 'MAHOUT';

DELETE FROM servicecomponentdesiredstate WHERE service_name = ‘MAHOUT’;

DELETE FROM clusterservices WHERE service_name = 'MAHOUT';

DELETE FROM hostcomponentstate WHERE service_name = 'MAHOUT';

DELETE FROM hostcomponentdesiredstate WHERE service_name = 'MAHOUT';

ALTER TABLE clusterservices ENABLE TRIGGER ALL;

ALTER TABLE servicecomponentdesiredstate ENABLE TRIGGER ALL;

Can anyone confirm whether these statements cover all the tables to remove the service or am I missing some tables here?

[#2]

I can confirm for you that the deletes look correct. I normally do not do the alters, but I don't see them causing an issue. Remember to STOP ambari-server before editing. Take a good backup of the db. Then when done, start ambari-server.

[#3]

Thank you @Orlando Teixeira.

[#4 · Accepted solution]

@Shashant Panwar

you will also need to clean alerts and service config tables:

Some of them are as followed:

serviceconfig

serviceconfigmapping

alert_definition

alert_current

alert_group

alert_history

I will rather encourage to resolve the issue of showing Mahout in the UI and then delete service from UI (if using ambari-2.4.0 or above) or from Ambari API

Can you let us know what version of ambari are you using ? Also what is the content of clusterservices and servicedesiredstate tables specifically if Mahout service entry is present or not?

[#5]

Thank you @jaimin. I went ahead and delete the service. I was using Ambari 2.2.2.0 and upgraded to 2.4.2.0.

But thanks for pointing out extra tables to take care.