Upgrading to Tanzu Application Service 4.x fails with ForeignKeyConstraintViolation

Products

• VMware Tanzu Application Service

Issue/Introduction

When attempting to upgrade to TAS 4.x, the Cloud Controller pre-start script fails with the following error:

[2024-05-04 09:57:48+0000] Sequel::ForeignKeyConstraintViolation: Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails (`ccdb`.`buildpack_lifecycle_buildpacks`, CONSTRAINT `fk_blbuildpack_bldata_guid` FOREIGN KEY (`buildpack_lifecycle_data_guid`) REFERENCES `buildpack_lifecycle_data` (`guid`) ON DELETE RESTRICT) (Sequel::ForeignKeyConstraintViolation)

Environment

Tanzu Application Services 4.x

Cause

This is caused by orphaned entries in the buildpack_lifecycle_data table in the cloud controller database. The migration that attempts to delete these entries is here:

https://github.com/cloudfoundry/cloud_controller_ng/blob/ad9b5a1f29526df1307edf4644a0e733bbbb8bcb/db/migrations/20240115163000_add_delete_cascade_to_foreign_keys.rb#L11-L12

Resolution

Note: Before performing these steps, please ensure you take a backup of the cloud controller database using the Backup and Restore procedure

First, connect to the Cloud Controller database:

1. If you are using internal mysql cluster, you can bosh ssh to a mysql instance with bosh -d <cf_deployment> ssh mysql/0. If you are using an external cluster, you will need to connect to it manually, and skip to step 4.
2. sudo -i
3. mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf
4. use ccdb;

Once connected, we need to confirm the entries to be deleted from the buildpack_lifecycle_data table:

SELECT * FROM buildpack_lifecycle_data WHERE (build_guid NOT IN (SELECT guid FROM builds));

These are orphaned entries that the migration was attempting to delete, but was failing due to the foreign key constraint.

Next, we need to check on the entries in the buildpack_lifecycle_buildpacks, which is the other side of the foreign key constraint.

SELECT * FROM buildpack_lifecycle_buildpacks WHERE buildpack_lifecycle_data_guid in (SELECT guid FROM buildpack_lifecycle_data WHERE (build_guid NOT IN (SELECT guid FROM builds)));

Once you have verified these entries, we need run the delete queries. First, we will attempt the same DELETE used by the migration. We should see the same error as in the cloud controller logs at this stage:

DELETE FROM buildpack_lifecycle_data WHERE (build_guid NOT IN (SELECT guid FROM builds));

Next is the DELETE to remove the orphaned entries in the buildpack_lifecycle_buildpacks table. This should unblock the migration attempt:

DELETE FROM buildpack_lifecycle_buildpacks where buildpack_lifecycle_data_guid in (SELECT guid FROM buildpack_lifecycle_data WHERE (build_guid NOT IN (SELECT guid FROM builds)));

We can then re-run the migration DELETE. This should now succeed.

DELETE FROM buildpack_lifecycle_data WHERE (build_guid NOT IN (SELECT guid FROM builds));

Finally, confirm that there are no longer any orphaned entries:
SELECT * FROM buildpack_lifecycle_data WHERE (build_guid NOT IN (SELECT guid FROM builds));
(should be zero rows)

The upgrade should now succeed on the next attempt.