"add_delete_cascade_to_foreign_keys.rb" Can fail due to missing Builds

[sethboyles]

Issue

In this commit: ad9b5a1#diff-214a865c0b7fb0f3d7d40880ce1e6b38ebf38dd2cb104703663844da306dddc3R11, when adding a foreign key constraint to build_guid onbuildpack_lifecycle_data, the migration also attempts to delete all buildpack_lifecycle_data that has a build_guid no longer present in the builds table.

They query it runs is this:

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

The issue is that when trying to delete orphaned buildpack_lifecycle_data, there are still buildpack_lifecycle_buildpacks with buildpack_lifecycle_data_guids. These buildpack_lifecycle_buildpacks have their own foreign key constraint, and this causes the following error:

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)

Steps to Reproduce

1. Spin up an environment that predates the migration (CAPI 1.172.0) or otherwise revert the migration.
2. Push an app. This should create a build, buildpack_lifecycle_data, and buildpack_lifecycle_buildpack
3. Manually delete the build from the DB <--it's unclear yet how this happens in real envs
4. Run the migration (or execute SELECT * FROM buildpack_lifecycle_data WHERE (build_guid NOT IN (SELECT guid FROM builds))
5. Observe the error

Expected result

The migration successfully deletes the orphaned buildpack_lifecycle_data AND buildpack_lifecycle_buildpack(s)

Current result

error as described above

Possible Fix

I'm not sure, but I think possibly swapping Lines 11 and 12 of the migration would fix the migration.

cloud_controller_ng/db/migrations/20240115163000_add_delete_cascade_to_foreign_keys.rb

Lines 11 to 12 in ad9b5a1

	ForeignKey.new(:buildpack_lifecycle_data, :fk_buildpack_lifecycle_build_guid, :build_guid, :builds, :guid, new_constraint: true),
	ForeignKey.new(:buildpack_lifecycle_buildpacks, :fk_blbuildpack_bldata_guid, :buildpack_lifecycle_data_guid, :buildpack_lifecycle_data, :guid),

This way the delete cascade is applied to the buildpack_lifecycle_data_guid on buildpack_lifecycle_buildpacks BEFORE attempting to delete orphaned buildpack_lifecycle_data

cloud_controller_ng/db/migrations/20240115163000_add_delete_cascade_to_foreign_keys.rb

Line 29 in ad9b5a1

transaction { recreate_foreign_key_with_delete_cascade(db, fk) }

@philippthun

[philippthun]

@sethboyles - I was able to reproduce the issue by enhancing the (already existing) unit test for this migration. Changing the order of migration statements (as you suggested) fixes the problem (e.g. makes the unit test pass).