Remove 8 cross-database foreign keys referencing `ci_pipelines`
ci_pipelines is referenced as a foreign key from other 8 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_pipelines to become Loose foreign keys. Loose foreign keys are cleaned up asynchronously which means when we perform a DELETE FROM ci_pipelines
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.
-
merge_requests.head_pipeline_id
->ci_pipelines.id
-ON DELETE SET NULL
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !78566 (merged) -
No way for user to access once parent is deleted. Please explain: @fabiopitino: MergeRequest#actual_head_pipeline
is used primarily rather thanhead_pipeline
directly which uses a more accurate finder. In both cases it would returnnil
if the record is not found. -
Possible to access once parent deleted but low user impact. Please explain: @fabiopitino: We expose head_pipeline_id
in some places like REST API or webhook payload but this has low impact since requests to pipeline ID would return 404. -
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: there is a minor UX impact where we show "Checking pipeline status" indefinitely if they loaded the MR when the head_pipeline_id
is not null and refers to a pipeline that is now deleted. Could be mitigated by changing the loading state indicator code. There weren't any errors though. We should investigate closer though as this is a critical part of the application. We should see what happens if you try to merge an MR in this state.
-
-
dast_site_profiles_pipelines.ci_pipeline_id
->ci_pipelines.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): groupdynamic analysis devopssecure -
MR: !77640 (merged), !79103 (merged) -
No way for user to access once parent is deleted. Please explain: @philipcunningham FK is unused and is safe to remove because DAST table referenced by the join table is no longer used by DAST on-demand scans. Context is that the FK was added to support another scan policies but the approach changed. -
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>
-
-
merge_request_metrics.pipeline_id
->ci_pipelines.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): groupoptimize devopsmanage (reviewed by ahegyi
) -
MR: !76648 (merged) -
No way for user to access once parent is deleted. Please explain:
- In the MR API, we call
metrics.pipeline
which will return nil if thepipeline
record is already deleted.
-
Possible to access once parent deleted but low user impact. Please explain:
- N/A (Old version of value stream analytics was preloading pipelines based on this column but we've removed the feature because of cross-joins.)
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: Unlikely -
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
vulnerability_feedback.pipeline_id
->ci_pipelines.id
-ON DELETE SET NULL
-
Best team to review (check off when reviewed): groupthreat insights devopssecure -
MR: !77640 (merged), !79105 (merged) -
No way for user to access once parent is deleted. Please explain: We are using the pipeline_id
column to show on which pipeline the vulnerability has been dismissed. We are already checking if thepipeline.present?
to render the information and frontend can gracefully handle the response if thepipeline
isnull
so there is no 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>
-
-
dast_profiles_pipelines.ci_pipeline_id
->ci_pipelines.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): groupdynamic analysis devopssecure -
MR: !78597 (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 FK is used primarily to track which DAST profile triggered a CI pipeline and is it not leveraged by any user-facing functionality. If the CI pipeline is deleted, the DAST profile may still be accessed but the user-experience will not be affected. -
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>
-
-
vulnerability_statistics.latest_pipeline_id
->ci_pipelines.id
-ON DELETE SET NULL
-
Best team to review (check off when reviewed): groupthreat insights devopssecure -
MR: !78599 (merged) -
No way for user to access once parent is deleted. Please explain: We are using the pipeline
object and not thelatest_pipeline_id
column directly so if the record is missing we don't show anything to users. -
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>
-
-
vulnerability_occurrence_pipelines.pipeline_id
->ci_pipelines.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): groupthreat insights devopssecure -
MR: !78601 (merged) -
No way for user to access once parent is deleted. Please explain: We are using this information to show "when the vulnerability has been introduced" and "where is the vulnerability located at". In both cases, we are working with the pipeline
objects so if the column is set but the record is not found, we are handling those cases. -
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>
-
-
merge_trains.pipeline_id
->ci_pipelines.id
-ON DELETE SET NULL
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify @fabiopitino -
MR: !78568 (merged) -
No way for user to access once parent is deleted. Please explain: Since we use merge_train.pipeline
, it will returnnil
if not found by ID. If pipeline is deleted in the meantime. The merge train will recreate one for the same MR. -
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>
-