Fix query using wrong connection
What does this MR do and why?
Extracted from !78182 (merged).
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 by Thong Kuah