Skip to content

Fix corrupted `security_scans` statuses

Mehmet Emin INAC requested to merge 383836_fix_corrupted_security_scan_statuses into master

What does this MR do and why?

This MR introduces a background migration which will be running only for GitLab.com to fix the state of security_scans which are updated incorrectly by a regression introduced in this milestone.

We can recover succeeded, job_failed, and report_error cases but preparation_failed can not be recovered(though it's really a minor case).

Related to Pipeline security report tab is saying report h... (#383836 - closed).

Database review

As of writing, there are 7_083_791 records to be iterated by this background migration. So with the batch size of 10_000, it's expected to take almost a day to complete((7_083_791 / 10_000) * 2 / 60 / 24).

rake command outputs
rake db:migrate:up 
main: == 20221128120634 ScheduleFixingSecurityScanStatuses: migrating ===============
main: == 20221128120634 ScheduleFixingSecurityScanStatuses: migrated (0.0180s) ======
rake db:migrate:down 
main: == 20221128120634 ScheduleFixingSecurityScanStatuses: reverting ===============
main: == 20221128120634 ScheduleFixingSecurityScanStatuses: reverted (0.0197s) ======
Queries
Queries used in scheduler migration
Query to calculate the lower bound of the migration 
SELECT
    "security_scans".*
FROM
    "security_scans"
WHERE (date(timezone('UTC'::text, created_at)) > '2022-09-03 10:55:31.070699')
ORDER BY
    date(timezone('UTC'::text, created_at)) ASC,
    id ASC
LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13642/commands/47892

Queries used by batched migration
Query to find the lower bound of the batch 
SELECT
    "security_scans"."id"
FROM
    "security_scans"
WHERE
    "security_scans"."id" BETWEEN 30719697 AND 38115726
ORDER BY
    "security_scans"."id" ASC
LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13691/commands/48051

Query to find the upper bound of the batch 
SELECT
    "security_scans"."id"
FROM
    "security_scans"
WHERE
    "security_scans"."id" BETWEEN 30719697 AND 38115726
    AND "security_scans"."id" >= 30719697
ORDER BY
    "security_scans"."id" ASC
LIMIT 1 OFFSET 100

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13691/commands/48052

Query to load the batch relation 
SELECT
    "security_scans".*
FROM
    "security_scans"
WHERE
    "security_scans"."id" BETWEEN 30719697 AND 38115726
    AND "security_scans"."id" >= 30719697
    AND "security_scans"."id" < 30719797

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13691/commands/48053

Query to load the related CI Builds 
SELECT
    "ci_builds".*
FROM
    "ci_builds"
WHERE
    "ci_builds"."type" = 'Ci::Build'
    AND "ci_builds"."id" IN (2976601557, 2976574387, 2976574386, 2976574385, 2976604045, 2976604052, 2976604058, 2976604061, 2976604055, 2976604047, 2976601409, 2976606251, 2976602425, 2976602416, 2976602420, 2976600931, 2976600930, 2976600932, 2976600928, 2976608286, 2976608282, 2976608284, 2976609184, 2976609186, 2976522696, 2976602399, 2976602402, 2976602405, 2976602390, 2976607760, 2976607755, 2976607755, 2976611150, 2976612174, 2976612178, 2976612174, 2976612178, 2976612306, 2976612300, 2976612306, 2976612300, 2976612861, 2976612858, 2976612861, 2976612858, 2976613117, 2976613114, 2976613117, 2976613114, 2976610331, 2976610321, 2976610344, 2976604451, 2976616352, 2976616387, 2976616375, 2976601135, 2976601134, 2976601133, 2976601131, 2976616403, 2976612818, 2976612807, 2976612818, 2976612807, 2976615704, 2976615696, 2976615704, 2976615669, 2976615666, 2976615696, 2976615669, 2976615666, 2976612370, 2976612366, 2976612369, 2976612370, 2976612366, 2976612369, 2976612804, 2976612809, 2976612804, 2976612809, 2976615955, 2976615980, 2976615955, 2976615980, 2976612722, 2976611983, 2976611981, 2976611985, 2976611979, 2976611987, 2976611977, 2976611991, 2976611983, 2976611981, 2976611985, 2976611979, 2976611987)

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13691/commands/48054

Query to update the records 
UPDATE
    "security_scans"
SET
    "status" = 6
WHERE
    "security_scans"."id" IN (30719697)

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13691/commands/48055

The rest of the queries can be seen in the database testing pipeline output.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Mehmet Emin INAC

Merge request reports