Remove 4 cross-database foreign keys referencing `ci_builds`
ci_builds is referenced as a foreign key from other 4 other tables in a different database
The intention of this issue is to determine any risks when changing all of the below foreign keys referencing ci_builds to become Loose foreign keys. Loose foreign keys are cleaned up asynchronously which means when we perform a DELETE FROM ci_builds
we will queue a cleanup task that will be executed in ~5 mins to cleanup all of the rows that reference this record. This can mean there are sometimes strange UX bugs where they may see a link to something that no longer exists and clicking it may give an error or it may mean there is a background job that runs and errors due to an invalid foreign key or it may mean a 500 when rendering a certain page containing any of the below tables. Some more details about risks and mitigations are being documented in !76626 (merged) .
The groupsharding will be responsible for actually doing the work to convert the foreign keys to loose foreign keys but will likely need help from impacted teams to asses the risk and potentially determine or implement appropriate mitigation for any risks. Sometimes mitigation may mean refactoring code slightly to avoid errors or explicitly handling certain riskier cases by checking if there is a pending deletion for a record. It is also possible that we can optimize the 5 minute latency for cleanup in certain cases determined to be urgent.
-
dast_scanner_profiles_builds.ci_build_id
->ci_builds.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): groupdynamic analysis devopssecure -
MR: !77908 (merged) !77910 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: @philipcunningham DAST scanner profiles will remain after an associated CI build is deleted (only the join table row will be removed). Moving to eventual consistency will have no user-facing impact when interacting with DAST scanner profiles but if a user deletes a DAST scanner profile and attempts to re-run an historic job during the cleanup interleave, it will not be populated with the necessary environment variables and may fail. This seems acceptable. -
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
requirements_management_test_reports.build_id
->ci_builds.id
-ON DELETE SET NULL
-
Best team to review (check off when reviewed): ~"group:certify" devopsplan -
MR: !77908 (merged) !77912 (merged) -
No way for user to access once parent is deleted. Please explain: Test reports do not have related UI. We don't validate build_id presence at model or database level. Test reports are proxy objects to access a requirement status(passed|failed). Requirements UI will still work even when it has test reports with null build id.
-
Possible to access once parent deleted but low user impact. Please explain: There should be no direct user impact, requirements page will still behave the same.
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: I don't see any workers that needs test reports objects to run.
ProcessTestReportsServicecreates a new test report when a build finishes, if there is no build no job should be scheduled. I think if a build gets deleted after the job is scheduled an exception could happen with no impact for user.
-
Possible user impact to be evaluated or mitigated. Please explain: None
-
-
dast_site_profiles_builds.ci_build_id
->ci_builds.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): groupdynamic analysis devopssecure -
MR: !77908 (merged) !77916 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: @philipcunningham DAST site profiles will remain after an associated CI build is deleted (only the join table row will be removed). Moving to eventual consistency will have no user-facing impact when interacting with DAST site profiles but if a user deletes a DAST site profile and attempts to re-run an historic job during the cleanup interleave, it will not be populated with the necessary environment variables and may fail. This seems acceptable. -
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
security_scans.build_id
->ci_builds.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): groupthreat insights devopssecure -
MR: !77908 (merged) !77919 (merged) -
No way for user to access once parent is deleted. We are using this column to join security_scans
table so if the parent record is deleted, there is no way for users to access the child records which is not a problem. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-