How to delete corrupt data on the Spring Cloud Service 2.x service instances dashboard

Products

Support Only for Spring

Issue/Introduction

This article covers how to clean the bad service instance entries displayed on the Spring Cloud Service (SCS) 2.x Service Instances dashboard. The bad entries are showing the GUID for Org, Space, and Instance-Name columns because SCS is unable to resolve them as shown in the example below. The displayed GUID indicates that the SCS service no longer exists.

Environment

Product Version: 2.1

Resolution

The following steps cover how to remove bad data from the database.

The example below will illustrate deletion of a record having Instance Name = '83158adc-d11e-4e5e-bb5a-2120ad745780'. This was taken from the <SCS_BROKER_URL>/admin/serviceInstances page (see above). The Instance Name is the same as the service_instance_id field, which is the GUID of the SCS service.

1. Login to SCS mysql db broker and list the SI for bad Instance Name GUID with this command:

mysql> select service_instance_id, status, organization_guid, space_guid from service_instances where service_instance_id = <Instance-Name-GUID>

For example:

mysql>  select service_instance_id, status, organization_guid, space_guid from service_instances where service_instance_id='83158adc-d11e-4e5e-bb5a-2120ad745780';

+--------------------------------------+--------+--------------------------------------+--------------------------------------+
| service_instance_id                  | status | organization_guid                    | space_guid                           |
+--------------------------------------+--------+--------------------------------------+--------------------------------------+
| 83158adc-d11e-4e5e-bb5a-2120ad745780 | READY  | 8cb7a591-5570-40bf-b641-1bc6cc29adc1 | 9bda869a-4f6a-46c2-8fc6-26f6608b1555 |
+--------------------------------------+--------+--------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

2. Verify the corresponding service_instance_id GUID if known by Cloud Controller (CC) with this command:

$ cf curl /v2/service_instances/<service_instance_id-GUID>

For example:

$  cf curl /v2/service_instances/83158adc-d11e-4e5e-bb5a-2120ad745780

{
   "description": "The service instance could not be found: 83158adc-d11e-4e5e-bb5a-2120ad745780",
   "error_code": "CF-ServiceInstanceNotFound",
   "code": 60004
}

It is expected the service instance GUID will NOT be found. If found please contact VMware support.

3. Verify the service bindings with this command:

mysql> select * from service_bindings where service_instance_id = <Instance-Name-GUID>

If empty set, proceed to step 6.

mysql> select * from service_bindings where service_instance_id = '83158adc-d11e-4e5e-bb5a-2120ad745780';
Empty set (0.00 sec)

If NOT empty, proceed to step 4.

mysql> select * from service_bindings where service_instance_id = '83158adc-d11e-4e5e-bb5a-2120ad745780';
+--------------------------------------+--------------------------------------+---------------------+---------------------+
| id                                   | service_instance_id                  | created_timestamp   | updated_timestamp   |
+--------------------------------------+--------------------------------------+---------------------+---------------------+
| 922d9180-929f-4530-83bd-3eec92b83799 | 83158adc-d11e-4e5e-bb5a-2120ad745780 | 2022-01-15 14:38:40 | 2022-01-15 14:38:40 |
+--------------------------------------+--------------------------------------+---------------------+---------------------+
1 row in set (0.00 sec)

4. For all id(s) listed in step 3, verify each service_bindings id if known by Cloud Controller (CC):

  $ cf curl /v2/service_bindings/<id>

For example:

$ cf curl /v2/service_bindings/922d9180-929f-4530-83bd-3eec92b83799

{
   "description": "The service binding could not be found: 922d9180-929f-4530-83bd-3eec92b83799",
   "error_code": "CF-ServiceBindingNotFound",
   "code": 90004
}

It is expected the service binding will NOT be found. If found please contact VMware support.

5. After verifying that all service bindings DO NOT EXIST, delete all service_bindings records for the service_instance_id:

mysql> delete from service_bindings where service_instance_id = <Instance-Name-GUID>

For example:

mysql> delete from service_bindings where service_instance_id = '83158adc-d11e-4e5e-bb5a-2120ad745780'
Query OK, 1 row affected (0.02 sec)

6. Delete the service_instances record with this command:

  mysql> delete from service_instances where service_instance_id = <Instance-Name-GUID>

For example:

mysql> delete from service_instances where service_instance_id = '83158adc-d11e-4e5e-bb5a-2120ad745780';
Query OK, 1 row affected (0.02 sec)

7. Commit changes with this command:

    mysql> commit;

For example:

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

8. Verify the count from UI matches the number of records in service_instances.

mysql> select count(*) from service_instances;

For example:

mysql>  select count(*) from service_instances;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

Refresh dashboard UI - the number of entries should match count. Repeat steps 1-8 as needed.

Upon completion on cleaning the SCS 2.x broker database, we suggest to Check for Orphaned SCS backend apps.

Related articles