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