Delete a service using Ambari API

[#1]

I am trying to delete a service but I am unable to do it. In the past I was successful in deleting the service but this time it is throwing weird error.

curl -u admin:xxxxxxxx -H  "X-Requested-By: ambari" -X DELETE   http://localhost:8080/api/v1/clusters/prod/services/PRESTO
{
  "status": 500,
  "message": "Server Error"
} 

How do I delete the service ?

[#2]

@Anandha L Ranganathan

Before deleting the PRESTO service have you properly stopped it? Like following:

curl -u admin:xxxxxxxx -i -H 'X-Requested-By: ambari' -X PUT -d '{"RequestInfo": {"context" :"Stop PRESTO via REST"}, "Body": {"ServiceInfo": {"state": "INSTALLED"}}}' http://localhost:8080/api/v1/clusters/prod/services/PRESTO

.

If it is still failing with the "500 Error" then please share the ambari-server.log that will provide us the complete stackTrace of the error.

[#3]

Thanks for the quick reply. Here are the logs from ambari-server.

20 Feb 2017 03:28:57,527  INFO [qtp-ambari-client-3389] ClusterImpl:2052 - Deleting service for cluster, clusterName=prod, serviceName=PRESTO
20 Feb 2017 03:28:57,528  INFO [qtp-ambari-client-3389] ServiceImpl:608 - Deleting all components for service, clusterName=prod, serviceName=PRESTO
20 Feb 2017 03:28:57,528  INFO [qtp-ambari-client-3389] ServiceImpl:574 - Deselecting config mapping for cluster, clusterId=2, configTypes=[]
20 Feb 2017 03:28:57,531 ERROR [qtp-ambari-client-3389] AmbariJpaLocalTxnInterceptor:180 - [DETAILED ERROR] Rollback reason:
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))) AND (selected > 0))' at line 1
Error Code: 1064
Call: SELECT type_name, create_timestamp, cluster_id, selected, version_tag, user_name FROM clusterconfigmapping WHERE (((cluster_id = ?) AND (type_name IN ())) AND (selected > ?))
	bind => [2 parameters bound]
Query: ReadAllQuery(referenceClass=ClusterConfigMappingEntity sql="SELECT type_name, create_timestamp, cluster_id, selected, version_tag, user_name FROM clusterconfigmapping WHERE (((cluster_id = ?) AND (type_name IN ?)) AND (selected > ?))")
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
	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.ServerSession.executeCall(ServerSession.java:570)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691)
	at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
	at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)
	at org.apache.ambari.server.orm.dao.DaoUtils.selectList(DaoUtils.java:62)
	at org.apache.ambari.server.orm.dao.ClusterDAO.getSelectedConfigMappingByTypes(ClusterDAO.java:259)
	at org.apache.ambari.server.orm.AmbariLocalSessionInterceptor.invoke(AmbariLocalSessionInterceptor.java:53)
	at org.apache.ambari.server.state.ServiceImpl.deleteAllServiceConfigs(ServiceImpl.java:577)
	at org.apache.ambari.server.orm.AmbariJpaLocalTxnInterceptor.invoke(AmbariJpaLocalTxnInterceptor.java:118)
	at org.apache.ambari.server.state.ServiceImpl.delete(ServiceImpl.java:680)
	at org.apache.ambari.server.orm.AmbariJpaLocalTxnInterceptor.invoke(AmbariJpaLocalTxnInterceptor.java:128)
	at org.apache.ambari.server.state.cluster.ClusterImpl.deleteService(ClusterImpl.java:2081)
	at org.apache.ambari.server.state.cluster.ClusterImpl.deleteService(ClusterImpl.java:2060)
	at org.apache.ambari.server.controller.internal.ServiceResourceProvider.deleteServices(ServiceResourceProvider.java:886)
	at org.apache.ambari.server.controller.internal.ServiceResourceProvider$3.invoke(ServiceResourceProvider.java:247)
	at org.apache.ambari.server.controller.internal.ServiceResourceProvider$3.invoke(ServiceResourceProvider.java:244)
	at org.apache.ambari.server.controller.internal.AbstractResourceProvider.invokeWithRetry(AbstractResourceProvider.java:450)
	at org.apache.ambari.server.controller.internal.AbstractResourceProvider.modifyResources(AbstractResourceProvider.java:331)
	at org.apache.ambari.server.controller.internal.ServiceResourceProvider.deleteResources(ServiceResourceProvider.java:244)
	at org.apache.ambari.server.controller.internal.ClusterControllerImpl.deleteResources(ClusterControllerImpl.java:330)
	at org.apache.ambari.server.api.services.persistence.PersistenceManagerImpl.delete(PersistenceManagerImpl.java:111)

[#4 · Accepted solution]

@Anandha L Ranganathan

The "MySQLSyntaxErrorException" looks strange here looks buggy. Which version of ambari are you using?

.

- However as a quick workaround you might want to try the following approach to delete the "PRESTO" service completely.

1. Stop ambari-server

ambari-server stop

2. Take ambari Database backup for Safety.

mysqldump $dbname >/tmp/ambari_db_dump.sql

Exampel:
mysqldump ambari >/tmp/ambari_db_dump.sql

3. Now in the ambari Database run the following commands. This will clear the PRESTO service entry from you ambari DB.

    delete from hostcomponentstate where service_name = 'PRESTO';
    delete from hostcomponentdesiredstate where service_name = 'PRESTO';
    delete from servicecomponentdesiredstate where service_name = 'PRESTO';
    delete from servicedesiredstate where service_name = 'PRESTO';
    delete from serviceconfighosts where service_config_id in (select service_config_id from serviceconfig where service_name = 'PRESTO');
    delete from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'PRESTO');
    delete from serviceconfig where service_name = 'PRESTO';
    delete from requestresourcefilter where service_name = 'PRESTO';
    delete from requestoperationlevel where service_name = 'PRESTO';
    delete from clusterservices where service_name ='PRESTO';
    delete from clusterconfig where type_name like 'presto%';
    delete from clusterconfigmapping where type_name like 'presto%';

4. Now restart ambari-server

ambari-server start

.

[#5]

Thanks @Jay SenSharma.

Thanks and I am able to delete the service.