Resolve cross DB issues in ee/app/services/vulnerabilities/security_finding/create_issue_service.rb

Summary

Decomposition of some vulnerability tables has resulted in cross join issues in the listed service which have been allowed to progress anyways. Likely these will be resolvable simply once all the related tables have been decomposed to the correct schema.

Further details

The service sequentially creates:

  1. a vulnerability
  2. an issue
  3. a vulnerability issue link

These models are created in a database transaction to ensure consistency. However, this causes a cross-database transaction b/c vulnerabilities and vulnerability links belong to the sec gitlab schema, whereas issues belong to the main schema.

See temporary_ignore_tables_in_transaction in Vulnerabilities::SecurityFinding::CreateIssueService#execute:

  Gitlab::Database::QueryAnalyzers::PreventCrossDatabaseModification.temporary_ignore_tables_in_transaction(
          %w[
            vulnerability_statistics
            internal_ids
            issues
            issue_user_mentions
            issue_metrics
            vulnerability_issue_links
          ],
          url: 'https://gitlab.com/gitlab-org/gitlab/-/issues/480894'
        ) do
          ApplicationRecord.transaction do
            vulnerability = create_vulnerability
            issue = create_issue(vulnerability)
            create_vulnerability_issue_link(vulnerability, issue)
          end
        end

For the specific queries and rspec failures, see notes collected #480894 (comment 2223882715).

Proposal

This is similar to what we need to do for the Vulnerabilities::SecurityFinding::CreateMergeRequestService. See Resolve cross join issues in ee/app/services/vu... (#480359 - closed)

NOTE: The issue might be blocked by cross-database transactions involving notes and vulnerability_user_mentions. To be checked. See #480894 (comment 2237610307)

Edited by Fabien Catteau