question how to remove old registered hosts from DB in Archives of Support Questions (Read Only)

how to remove old registered hosts from DB

mike_bronson7 — Tue, 05 Dec 2017 23:12:44 GMT

I am trying to remove old worker from the DB ( from ambari-server machine ) as the following

* reference - https://community.hortonworks.com/questions/52910/how-can-i-remove-registered-host-but-do-not-added.html

psql -U ambari ambari

Password for user ambari:

ambari-> select host_id from hosts where host_name='worker05.sys59.com';

host_id 

---------

255 

(1 row)

so I delete the host:

ambari=> Delete from hoststate where host_id=255
ambari-> Delete from hosts where host_id=255

ambari=> select host_id from hosts where host_name='worker05.sys59.com';
host_id ---------
255 (1 row)

this worker still exists in DB , ( host_id=255 ) , even I restart the ambari-server !!

why?

and how to total remove this worker include all worker properties from postgreSQL ?

Re: how to remove old registered hosts from DB

asirna — Tue, 05 Dec 2017 23:35:56 GMT

@Michael Bronson,

Add a semicolon at the end of the statement

ambari=> Delete from hoststate where host_id=255;
ambari=> Delete from hosts where host_id=255;

Note: Do the above with caution. Before removing the node from cluster it is advised to move all the services to other nodes and make sure that the node is clean before deleting the node.

Below is the API to delete the node

curl -u {ambari-user}:{ambari-password} -H "X-Requested-By: ambari"-X DELETE  http://{ambari-host}:{ambari-port}/api/v1/clusters/{clustername}/hosts/{hostname}

Thanks,

Aditya

Re: how to remove old registered hosts from DB

mike_bronson7 — Tue, 05 Dec 2017 23:46:42 GMT

I am usually remove it by the amabri GUI ( dlete node ) , but I guess the API do the same ?

Re: how to remove old registered hosts from DB

asirna — Tue, 05 Dec 2017 23:48:57 GMT

@Michael Bronson,

Yes. Ambari calls the same API internally. If you want to run it from command line instead of Ambari GUI , you can use the API.

That should take care of clearing the entries from DB. You need not delete the entries from DB manually

Re: how to remove old registered hosts from DB

mike_bronson7 — Sun, 18 Aug 2019 02:57:51 GMT

now after we delete the worler06 now we want to add it to the cluster but we get from the ambari GUI:

we waiting more then 20min - is it logical ?

Please wait while the hosts are being checked for potential problems...

Re: how to remove old registered hosts from DB

asirna — Wed, 06 Dec 2017 00:08:07 GMT

@Michael Bronson,

It shouldn't take so long. Please check the ambari log to see what is going wrong.

Check this file /var/log/ambari-server/ambari-server.log in ambari server node

Re: how to remove old registered hosts from DB

mike_bronson7 — Wed, 06 Dec 2017 00:15:51 GMT

the deleted host - worker 06 only deleted and we not removed the worker component , so now when we add this worker again mayby this is the conflict because worker is already installed ?|

Re: how to remove old registered hosts from DB

mike_bronson7 — Wed, 06 Dec 2017 00:34:20 GMT

from the ambari-server log we see that

 configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:37,189  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:37,189  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:38,222  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:38,222  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:38,222  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:39,247  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:39,247  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:39,247  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:40,272  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:40,273  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
05 Dec 2017 16:33:40,273  WARN [ambari-action-scheduler] ExecutionCommandWrapper:185 - Unable to lookup the cluster by ID; assuming that there is no cluster and therefore no configs for this execution command: Cluster not found, clusterName=clusterID=-1
^C

Re: how to remove old registered hosts from DB

asirna — Wed, 06 Dec 2017 00:40:26 GMT

@Michael Bronson,

If the host deletion was not proper then there can be conflicts. The ambari log below doesn't show any errors. Try restarting ambari server and see if you can proceed further.

ambari-server restart

Re: how to remove old registered hosts from DB

mike_bronson7 — Wed, 06 Dec 2017 00:54:30 GMT

another issue I have about :

ambari=> Delete from hosts where host_id=351;
ERROR:  update or delete on table "hosts" violates foreign key constraint "fk_host_role_command_host_id" on table "host_role_command"
DETAIL:  Key (host_id)=(351) is still referenced from table "host_role_command".

Re: how to remove old registered hosts from DB

asirna — Wed, 06 Dec 2017 01:04:17 GMT

I strongly suggest not to delete/edit entries directly in database. However if you still want to do it at your own risk

delete from host_role_command where host_id=351;
Delete from hosts where host_id=351;

Re: how to remove old registered hosts from DB

mike_bronson7 — Wed, 06 Dec 2017 01:07:05 GMT

we still get the same errors about

ambari=> delete from host_role_command where host_id=351;
ERROR:  update or delete on table "host_role_command" violates foreign key constraint "fk_execution_command_task_id" on table "execution_command"
DETAIL:  Key (task_id)=(5156) is still referenced from table "execution_command".
ambari=> Delete from hosts where host_id=351;
ERROR:  update or delete on table "hosts" violates foreign key constraint "fk_host_role_command_host_id" on table "host_role_command"
DETAIL:  Key (host_id)=(351) is still referenced from table "host_role_command".

Re: how to remove old registered hosts from DB

asirna — Wed, 06 Dec 2017 02:13:21 GMT

@Michael Bronson,

You have to delete all the rows which have FK constraint in different tables. Then you will be able to delete the host entry

Re: how to remove old registered hosts from DB

mike_bronson7 — Wed, 06 Dec 2017 02:18:38 GMT

can you share with me please how to rows which have FK constraint in different tables

Re: how to remove old registered hosts from DB

asirna — Wed, 06 Dec 2017 02:24:11 GMT

@Michael Bronson,

I'm sorry. I do not have a setup where I can try this currently. But you can see the error message and delete the rows of a table on which it complains the FK constraint

for ex: ERROR: update or delete on table "host_role_command" violates foreign key constraint "fk_execution_command_task_id" on table "execution_command" DETAIL: Key (task_id)=(5156) is still referenced from table "execution_command".

For this error you can run

delete from execution_command where task_id=5156;

Re: how to remove old registered hosts from DB

jsensharma — Wed, 06 Dec 2017 04:21:22 GMT

@Michael Bronson

You can try the following approach to completely remove the unwanted host from your ambari Database.

0. Stop ambari-server.

# ambari-server stop

1. Please take a fresh ambari DB dump for safety and backup.

2. Now run the following SQL queries inside your ambari DB to delete the unwanted host. Please replace the "unwanted1.host.com" with your unwanted hostname and similarly the "351" with the "host_id" that you want to remove from your Database.

delete from execution_command where task_id in (select task_id from host_role_command where host_id in (351));
delete from host_version where host_id in (351);
delete from host_role_command where host_id in (351);
delete from serviceconfighosts where host_id in (351);
delete from hoststate where host_id in (351);
delete from kerberos_principal_host WHERE host_id='unwanted1.host.com';  ----> For kerberized Env
delete from hosts where host_name in ('unwanted1.host.com');
delete from alert_current where history_id in ( select alert_id from alert_history where host_name in ('unwanted1.host.com'));

3. Now restart ambari-server.

# ambari-server start