How to determine if a service instance deployment is known to Cloud Controller (CC) given its GUID and how to determine its org, space and name?

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

Sometimes the "upgrade-all-service-instances" or "recreate-all-service-instances" errands fail, showing a service instance GUID that was unable to be updated or recreated. To diagnose this, it's helpful to know if the service instance is known to Cloud Controller (CC), or if the GUID is for an orphaned deployment.

If it is known to Cloud Controller, then determining the org, space, and name of the service instance can be helpful.

How can I get this service instance information given its GUID?

Environment

Product Version: 2.9

Resolution

Given the service instance GUID, a few queries can be run in the Tanzu Application Service (TAS) MySQL database to get the service instance name, org, and space.

If the GUID isn't found in the ccdb.service_instances table, the deployment is unknown to Cloud Controller and associated with an orphaned deployment. The schema of interest in the database is used by the Cloud Controller - the "ccdb".

1. To start, get the TAS deployment name, then SSH into any of the mysql VMs:

bosh deployments --column=name
[...]
bosh -d cf-... ssh mysql/0

2. Connect to the database:

sudo mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf

3. Finally, query a few Cloud Controller database (ccdb) tables to get the information needed.

Note: You'll need to update these queries for your service instance GUID and query results.

mysql> select name,space_id from ccdb.service_instances where guid = '4e48c232-fac0-4b1c-8eb9-c2b3329a3ffc';
+---------+----------+
| name    | space_id |
+---------+----------+
| philsLF |       88 |
+---------+----------+
1 row in set (0.00 sec)
 
mysql> select name, organization_id from ccdb.spaces where id = 88;
+-------+-----------------+
| name  | organization_id |
+-------+-----------------+
| mysql |               7 |
+-------+-----------------+
1 row in set (0.00 sec)
 
mysql> select name from ccdb.organizations where id = 7;
+----------+
| name     |
+----------+
| ppickett |
+----------+
1 row in set (0.00 sec)
 
mysql>