Fix query using wrong connection (!80116) · Merge requests · GitLab.org / GitLab

Thong Kuah requested to merge fix_devops_adoption_snapshot_calculator_wrong_connection into master Feb 07, 2022

What does this MR do and why?

Fixes a query which uses the wrong connection (ci-decompositionphase6). This query will have failed as we want to query data relating to the ci_job_artifacts table but it was using the Project connection.

To reproduce, run:

export GITLAB_USE_MODEL_LOAD_BALANCING=true
export GITLAB_LOAD_BALANCING_REUSE_PRIMARY_ci=ci
bin/rspec ./ee/spec/lib/analytics/devops_adoption/snapshot_calculator_spec.rb

Before:

   (0.2ms)  SELECT COUNT(id) FROM (VALUES (28), (29)) project_ids (id) WHERE EXISTS (SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."file_type" = 5 AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999' AND "ci_job_artifacts"."project_id" = project_ids.id) /*application:test,correlation_id:eb9614247e1ca576c18958fffd6bce2f,db_config_name:main*/
  ↳ ee/lib/analytics/devops_adoption/snapshot_calculator.rb:131:in `block in projects_count_with_artifact'
   (0.2ms)  SELECT COUNT(id) FROM (VALUES (28), (29)) project_ids (id) WHERE EXISTS (SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."file_type" = 8 AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999' AND "ci_job_artifacts"."project_id" = project_ids.id) /*application:test,correlation_id:eb9614247e1ca576c18958fffd6bce2f,db_config_name:main*/
  ↳ ee/lib/analytics/devops_adoption/snapshot_calculator.rb:131:in `block in projects_count_with_artifact'
   (0.2ms)  SELECT COUNT(id) FROM (VALUES (28), (29)) project_ids (id) WHERE EXISTS (SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."file_type" = 6 AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999' AND "ci_job_artifacts"."project_id" = project_ids.id) /*application:test,correlation_id:eb9614247e1ca576c18958fffd6bce2f,db_config_name:main*/
  ↳ ee/lib/analytics/devops_adoption/snapshot_calculator.rb:131:in `block in projects_count_with_artifact'
   (0.1ms)  SELECT COUNT(id) FROM (VALUES (28), (29)) project_ids (id) WHERE EXISTS (SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."file_type" = 23 AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999' AND "ci_job_artifacts"."project_id" = project_ids.id) /*application:test,correlation_id:eb9614247e1ca576c18958fffd6bce2f,db_config_name:main*/
  ↳ ee/lib/analytics/devops_adoption/snapshot_calculator.rb:131:in `block in projects_count_with_artifact'

After :

   (0.2ms)  SELECT COUNT(*) FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."file_type" = 5 AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999' AND "ci_job_artifacts"."project_id" IN (1, 2) /*application:test,correlation_id:f31dea323aac3295399d740f52b4fddb,db_config_name:main*/
  ↳ ee/lib/analytics/devops_adoption/snapshot_calculator.rb:122:in `block in projects_count_with_artifact'
   (0.1ms)  SELECT COUNT(*) FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."file_type" = 8 AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999' AND "ci_job_artifacts"."project_id" IN (1, 2) /*application:test,correlation_id:f31dea323aac3295399d740f52b4fddb,db_config_name:main*/
  ↳ ee/lib/analytics/devops_adoption/snapshot_calculator.rb:122:in `block in projects_count_with_artifact'
   (0.1ms)  SELECT COUNT(*) FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."file_type" = 6 AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999' AND "ci_job_artifacts"."project_id" IN (1, 2) /*application:test,correlation_id:f31dea323aac3295399d740f52b4fddb,db_config_name:main*/
  ↳ ee/lib/analytics/devops_adoption/snapshot_calculator.rb:122:in `block in projects_count_with_artifact'
   (0.2ms)  SELECT COUNT(*) FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."file_type" = 23 AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999' AND "ci_job_artifacts"."project_id" IN (1, 2) /*application:test,correlation_id:f31dea323aac3295399d740f52b4fddb,db_config_name:main*/
  ↳ ee/lib/analytics/devops_adoption/snapshot_calculator.rb:122:in `block in projects_count_with_artifact'

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

Old query:

SELECT COUNT(id)
FROM (
      VALUES (28), (29)) project_ids (id)
WHERE EXISTS
    (SELECT "ci_job_artifacts"."project_id",
            "ci_job_artifacts"."file_type",
            "ci_job_artifacts"."size",
            "ci_job_artifacts"."created_at",
            "ci_job_artifacts"."updated_at",
            "ci_job_artifacts"."expire_at",
            "ci_job_artifacts"."file",
            "ci_job_artifacts"."file_store",
            "ci_job_artifacts"."file_sha256",
            "ci_job_artifacts"."file_format",
            "ci_job_artifacts"."file_location",
            "ci_job_artifacts"."id",
            "ci_job_artifacts"."job_id",
            "ci_job_artifacts"."locked"
     FROM "ci_job_artifacts"
     WHERE "ci_job_artifacts"."file_type" = 5
       AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999'
       AND "ci_job_artifacts"."project_id" = project_ids.id)

New QUERY:

SELECT COUNT(*)
FROM "ci_job_artifacts"
WHERE "ci_job_artifacts"."file_type" = 5
  AND "ci_job_artifacts"."created_at" BETWEEN '2020-12-01 00:00:00' AND '2020-12-31 23:59:59.999999'
  AND "ci_job_artifacts"."project_id" IN (1,
                                          2)

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

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

I have evaluated the MR acceptance checklist for this MR.

Edited Feb 08, 2022 by Thong Kuah

Fix query using wrong connection

What does this MR do and why?

Screenshots or screen recordings

How to set up and validate locally

MR acceptance checklist

Merge request reports