On Ambari 2.1.1 (Centos/RHEL 7): removed service keeps reappearing after server restart

[#1]

While developing Zeppelin Ambari service, I was going through the cycle of updating the code, restarting ambari, installing service. Then once the install failed (e.g. due to error in my code), I would delete the failed install via REST api and restart ambari and then start over. Initially this worked fine (first 3 or 4 times), but then it gets into this weird state where the failed install icon comes back after posting the DELETE request and running ambari restart. Usually with failed installs, one can go under Services tab of Ambari and see the failed install service listed and attempt to re-install, but in this weird state, the service does not appear under Services any more. So basically I'm stuck with a cluster I can't remove this service from.

I have been able to reproduce this on 2 envs: one on CentOS 7 and one on RHEL 7, but never seen the problem on CentOS 6.

Questions

1. Is this a bug?

2. How do I manually remove all traces of this service on my cluster before attempting to re-install the service?

Ambari log shows that its unable to delete the service due to FK constraint

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: update or delete on table "servicecomponentdesiredstate" violates foreign key constraint "hstcomponentstatecomponentname" on table "hostcomponentstate"
  Detail: Key (component_name, cluster_id, service_name)=(ZEPPELIN_MASTER, 2, ZEPPELIN) is still referenced from table "hostcomponentstate".
Error Code: 0
Call: DELETE FROM servicecomponentdesiredstate WHERE (((cluster_id = ?) AND (component_name = ?)) AND (service_name = ?))
        bind => [3 parameters bound]
Query: DeleteObjectQuery(org.apache.ambari.server.orm.entities.ClusterServiceEntity@3426596b)
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1611)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:898)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:962)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:631)
        at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatch(ParameterizedSQLBatchWritingMechanism.java:149)
        at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatchedStatements(ParameterizedSQLBatchWritingMechanism.java:134)
        at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.appendCall(ParameterizedSQLBatchWritingMechanism.java:82)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:603)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
        at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
        at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:298)

[#2 · Accepted solution]

Try this,

Stop ambari-server

DELETE FROM hostcomponentstate WHERE service_name IN ('ZEPPELIN');

DELETE FROM hostcomponentdesiredstate WHERE service_name IN ('ZEPPELIN');

DELETE FROM servicecomponentdesiredstate WHERE service_name IN ('ZEPPELIN');

DELETE FROM servicedesiredstate WHERE service_name IN ('ZEPPELIN');

DELETE FROM clusterservices WHERE service_name IN ('ZEPPELIN');

Start ambari-server

[#3]

This is helpful but we should file this as bug @jeff@hortonworks.com @mahadev@hortonworks.com

[#4]

If all else fails, login to ambari server, connect to the ambari database

delete from hostcomponentstate where service_name = "ZEPPELIN";

[#5]

Adding in @mahadev@hortonworks.com since it seems we have some issues with delete service via the API.

https://hortonworks.jira.com/browse/BUG-46931

[#6]

I got the following to work to remove HBASE and NIFI from Ambari:

[root@ambari-server ~]# ambari-server stop
Using python /usr/bin/python
Stopping ambari-server
Ambari Server stopped
[root@ambari-server ~]# su - postgres
-bash-4.1$ psql
psql (8.4.20)
Type "help" for help.
postgres=# \connect ambari
psql (8.4.20)
You are now connected to database "ambari".
ambari=# DELETE FROM ambari.hostcomponentstate WHERE service_name IN ('HBASE');
DELETE 14
ambari=# DELETE FROM ambari.hostcomponentdesiredstate WHERE service_name IN ('HBASE');
DELETE 14
ambari=# DELETE FROM ambari.servicecomponentdesiredstate WHERE service_name IN ('HBASE');
DELETE 4
ambari=# DELETE FROM ambari.servicedesiredstate WHERE service_name IN ('HBASE');
DELETE 1
ambari=# DELETE FROM ambari.clusterservices WHERE service_name IN ('HBASE');
DELETE 1
ambari=# \q
-bash-4.1$ exit
logout
[root@ambari-server ~]# ambari-server start
Using python /usr/bin/python
Starting ambari-server
Ambari Server running with administrator privileges.
Organizing resource files at /var/lib/ambari-server/resources...
Server PID at: /var/run/ambari-server/ambari-server.pid
Server out at: /var/log/ambari-server/ambari-server.out
Server log at: /var/log/ambari-server/ambari-server.log
Waiting for server start....................
Ambari Server 'start' completed successfully.
[root@ambari-server ~]# ambari-server stop
Using python /usr/bin/python
Stopping ambari-server
Ambari Server stopped
[root@ambari-server ~]# su - postgres
-bash-4.1$ psql
psql (8.4.20)
Type "help" for help.
postgres=# \connect ambari
psql (8.4.20)
You are now connected to database "ambari".
ambari=# DELETE FROM ambari.hostcomponentstate WHERE service_name IN ('NIFI');
DELETE 1
ambari=# DELETE FROM ambari.hostcomponentdesiredstate WHERE service_name IN ('NIFI');
DELETE 1
ambari=# DELETE FROM ambari.servicecomponentdesiredstate WHERE service_name IN ('NIFI');
DELETE 1
ambari=# DELETE FROM ambari.servicedesiredstate WHERE service_name IN ('NIFI');
DELETE 1
ambari=# DELETE FROM ambari.clusterservices WHERE service_name IN ('NIFI');
DELETE 1
ambari=# \q
-bash-4.1$ exit
logout
[root@ambari-server ~]# ambari-server start
Using python /usr/bin/python
Starting ambari-server
Ambari Server running with administrator privileges.
Organizing resource files at /var/lib/ambari-server/resources...
Server PID at: /var/run/ambari-server/ambari-server.pid
Server out at: /var/log/ambari-server/ambari-server.out
Server log at: /var/log/ambari-server/ambari-server.log
Waiting for server start....................
Ambari Server 'start' completed successfully.
[root@ambari-server ~]#

,

This worked for me and now HBASE and NIFI no longer show up in Ambari:

[root@ambari-server ~]# ambari-server stop
Using python /usr/bin/python
Stopping ambari-server
Ambari Server stopped
[root@ambari-server ~]# su - postgres
-bash-4.1$ psql
psql (8.4.20)
Type "help" for help.
postgres=# \connect ambari
psql (8.4.20)
You are now connected to database "ambari".
ambari=# DELETE FROM ambari.hostcomponentstate WHERE service_name IN ('HBASE');
DELETE 14
ambari=# DELETE FROM ambari.hostcomponentdesiredstate WHERE service_name IN ('HBASE');
DELETE 14
ambari=# DELETE FROM ambari.servicecomponentdesiredstate WHERE service_name IN ('HBASE');
DELETE 4
ambari=# DELETE FROM ambari.servicedesiredstate WHERE service_name IN ('HBASE');
DELETE 1
ambari=# DELETE FROM ambari.clusterservices WHERE service_name IN ('HBASE');
DELETE 1
ambari=# \q
-bash-4.1$ exit
logout
[root@ambari-server ~]# ambari-server start
Using python /usr/bin/python
Starting ambari-server
Ambari Server running with administrator privileges.
Organizing resource files at /var/lib/ambari-server/resources...
Server PID at: /var/run/ambari-server/ambari-server.pid
Server out at: /var/log/ambari-server/ambari-server.out
Server log at: /var/log/ambari-server/ambari-server.log
Waiting for server start....................
Ambari Server 'start' completed successfully.
[root@ambari-server ~]# ambari-server stop
Using python /usr/bin/python
Stopping ambari-server
Ambari Server stopped
[root@ambari-server ~]# su - postgres
-bash-4.1$ psql
psql (8.4.20)
Type "help" for help.
postgres=# \connect ambari
psql (8.4.20)
You are now connected to database "ambari".
ambari=# DELETE FROM ambari.hostcomponentstate WHERE service_name IN ('NIFI');
DELETE 1
ambari=# DELETE FROM ambari.hostcomponentdesiredstate WHERE service_name IN ('NIFI');
DELETE 1
ambari=# DELETE FROM ambari.servicecomponentdesiredstate WHERE service_name IN ('NIFI');
DELETE 1
ambari=# DELETE FROM ambari.servicedesiredstate WHERE service_name IN ('NIFI');
DELETE 1
ambari=# DELETE FROM ambari.clusterservices WHERE service_name IN ('NIFI');
DELETE 1
ambari=# \q
-bash-4.1$ exit
logout
[root@ambari-server ~]# ambari-server start
Using python /usr/bin/python
Starting ambari-server
Ambari Server running with administrator privileges.
Organizing resource files at /var/lib/ambari-server/resources...
Server PID at: /var/run/ambari-server/ambari-server.pid
Server out at: /var/log/ambari-server/ambari-server.out
Server log at: /var/log/ambari-server/ambari-server.log
Waiting for server start....................
Ambari Server 'start' completed successfully.
[root@ambari-server ~]#