bugcy013
12/18/2015 - 9:26 AM
CM_tip.txt
Delete Unexpected 127.0.0.1 from Cloudera Manager
I have cloudera manager 5.0.0 installed in my small cluster, tried to delete some nodes and then found cloudera manager not working, exceptions thrown in the landing page and 500 in the hosts page, almost null pointer exception everywhere.
Next time when I restart cm and log into CM, agent upgrading guide begins again. Here I found the 127.0.0.1 appeared in the host list which is not delete-able.
So I try to delete data from PostgreSQL.
1 read database config
password can be found from Cloudera Manager Drop Database/User on Embedded Postgresql
# cat /etc/cloudera-scm-server/db.properties
# Auto-generated by initialize_embedded_db.sh
#
# 20140213-223647
#
# These are database settings for CM Manager
#
com.cloudera.cmf.db.type=postgresql
com.cloudera.cmf.db.host=localhost:7432
com.cloudera.cmf.db.name=scm
com.cloudera.cmf.db.user=scm
com.cloudera.cmf.db.password=XXXXXX
2 check table
# psql -U scm -p 7432
Password for user scm:
psql (8.4.20)
Type "help" for help.
scm=> \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------------------+-------+-------
public | audits | table | scm
public | client_configs | table | scm
public | cluster_activated_releases | table | scm
public | cluster_activated_releases_aud | table | scm
public | cluster_managed_releases | table | scm
public | cluster_undistributed_releases | table | scm
public | clusters | table | scm
public | clusters_aud | table | scm
public | cm_peers | table | scm
public | cm_version | table | scm
public | command_schedules | table | scm
public | commands | table | scm
public | config_containers | table | scm
public | configs | table | scm
public | configs_aud | table | scm
public | credentials | table | scm
public | global_settings | table | scm
public | host_template_to_role_conf_grp | table | scm
public | host_templates | table | scm
public | hosts | table | scm
public | hosts_aud | table | scm
public | metrics | table | scm
public | parcel_components | table | scm
public | parcels | table | scm
public | process_active_releases | table | scm
public | processes | table | scm
public | releases | table | scm
public | releases_aud | table | scm
public | revisions | table | scm
public | role_config_groups | table | scm
public | role_config_groups_aud | table | scm
public | roles | table | scm
public | roles_aud | table | scm
public | schema_version | table | scm
public | services | table | scm
public | services_aud | table | scm
public | snapshot_policies | table | scm
public | user_roles | table | scm
public | user_settings | table | scm
public | users | table | scm
3 find data
scm=> select * from hosts;
host_id | optimistic_lock_version | host_identifier | name | ip_address | rack_id | status | config_container_id | maintenance_count | decommission_count | cluster_id | num_cores | total_phys_mem_bytes | public_name | public_ip_address | cloud_provider
---------+-------------------------+--------------------------------------+---------------------+--------------+----------+--------+---------------------+-------------------+--------------------+------------+-----------+----------------------+-------------+-------------------+----------------
...
13 | 1 | c2ef3b61-f11e-4570-8f2b-ce77989ed336 | | | /default | NA | 1 | 0 | 0 | | | 67539087360 | | |
...
(9 rows)
The row host_id=13 has an empty cluster id, no ip address nor name filled.
4 delete data
scm=> delete from hosts where host_id=13;
ERROR: update or delete on table "hosts" violates foreign key constraint "fk_command_host" on table "commands"
DETAIL: Key (host_id)=(13) is still referenced from table "commands".
Try to find out deletes on foreign keys.
scm=> SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='commands';
constraint_name | table_name | column_name | foreign_table_name | foreign_column_name
---------------------+------------+-------------+--------------------+---------------------
fk_command_parent | commands | parent_id | commands | command_id
fk_command_host | commands | host_id | hosts | host_id
fk_command_role | commands | role_id | roles | role_id
fk_command_service | commands | service_id | services | service_id
fk_command_cluster | commands | cluster_id | clusters | cluster_id
fk_command_schedule | commands | schedule_id | command_schedules | spec_id
(6 rows)
scm=> SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='hosts';
constraint_name | table_name | column_name | foreign_table_name | foreign_column_name
--------------------------+------------+---------------------+--------------------+---------------------
fk_host_cluster | hosts | cluster_id | clusters | cluster_id
fk_host_config_container | hosts | config_container_id | config_containers | config_container_id
(2 rows)
So Delete like
delete from hosts_aud where host_id=13;
delete from commands where host_id=13;
delete from hosts where host_id=13;
5 restart
service cloudera-scm-server restart