Not able to delete 1 Role from Inactive host

[#1]

Hi,

One of our VMs crashed and we are trying to remove it from the Cluster. We have been able to remove all the assigned Roles except 1 i.e. HDFS NFS Gateway. 

While removing this role, we are getting below mentioned error :

Caused by: org.postgresql.util.PSQLException:ERROR: update or delete on table "roles" violates foreign key constraint "fk_config_role" on table "configs" Detail: Key (role_id)=(110) is still referenced from table "configs".
at QueryExecutorImpl.java line 2102
in org.postgresql.core.v3.QueryExecutorImpl receiveErrorResponse()

Kindly suggest.

[#2 · Accepted solution]

I was able to find the solution. Below are steps -

1. Run this command on SCM and retrieve the password- cat /etc/cloudera-scm-server/db.properties 

    Output - 

    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=PASSWORD

2. Run another command - psql -U scm -p 7432 -h localhost -d scm

    This will open the interface to run Sql query

3. Run sql query specific to your needs e.g. delete from configs where role_id = 110

Hope this helps.

[#3]

Thank you for sharing the solution. Hopefully it will assist others as well. 🙂