Fix cross-DB query in EnvironmentStatus.build_environments_status
EnvironmentStatus.build_environments_status generates a cross-DB query.
Example failure
/spec/models/environment_status_spec.rb
Failures:
1) EnvironmentStatus.for_merge_request is based on merge_request.diff_head_sha
Failure/Error:
raise CrossJoinAcrossUnsupportedTablesError,
"Unsupported cross-join across '#{tables.join(", ")}' modifying '#{schemas.to_a.join(", ")}' discovered " \
"when executing query '#{sql}'. Please refer to https://docs.gitlab.com/ee/development/database/multiple_databases.html#removing-joins-between-ci_-and-non-ci_-tables for details on how to resolve this exception."
Database::PreventCrossJoins::CrossJoinAcrossUnsupportedTablesError:
Unsupported cross-join across 'environments, ci_pipelines, ci_sources_pipelines, deployments, ci_builds' modifying 'gitlab_main, gitlab_ci' discovered when executing query 'SELECT "environments".* FROM "environments" WHERE "environments"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 30)
UNION
(SELECT "ci_pipelines".* FROM "ci_pipelines", "base_and_descendants", "ci_sources_pipelines" WHERE "ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id" AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id" AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id")) SELECT "deployments"."environment_id" FROM "base_and_descendants" AS "ci_pipelines" INNER JOIN "ci_builds" ON "ci_builds"."commit_id" = "ci_pipelines"."id" AND "ci_builds"."type" = 'Ci::Build' INNER JOIN "deployments" ON "deployments"."deployable_type" = 'CommitStatus' AND "deployments"."deployable_id" = "ci_builds"."id") AND ("environments"."state" IN ('available')) /*application:test,correlation_id:4cc9f30961b9aa5be2062a906506ebb0*/'. Please refer to https://docs.gitlab.com/ee/development/database/multiple_databases.html#removing-joins-between-ci_-and-non-ci_-tables for details on how to resolve this exception.
# ./spec/support/database/prevent_cross_joins.rb:48:in `validate_cross_joins!'
# ./spec/support/database/prevent_cross_joins.rb:57:in `block in with_cross_joins_prevented'
# ./app/models/environment_status.rb:104:in `map'
# ./app/models/environment_status.rb:104:in `build_environments_status'
# ./app/models/environment_status.rb:14:in `for_merge_request'
# ./spec/models/environment_status_spec.rb:77:in `block (3 levels) in <main>'
# ./spec/spec_helper.rb:399:in `block (3 levels) in <main>'
# ./spec/support/sidekiq_middleware.rb:9:in `with_sidekiq_server_middleware'
# ./spec/spec_helper.rb:390:in `block (2 levels) in <main>'
# ./spec/spec_helper.rb:386:in `block (3 levels) in <main>'
# ./lib/gitlab/application_context.rb:31:in `with_raw_context'
# ./spec/spec_helper.rb:386:in `block (2 levels) in <main>'
# ./spec/support/database/prevent_cross_joins.rb:95:in `block (3 levels) in <main>'
# ./spec/support/database/prevent_cross_joins.rb:62:in `with_cross_joins_prevented'
# ./spec/support/database/prevent_cross_joins.rb:95:in `block (2 levels) in <main>'
# -e:1:in `<main>'
Edited by Thong Kuah