GlobalAdvisoryScanWorker: null value in vulnerability_occurrence_pipelines.occurrence_id violates not-null constraint

Summary

PackageMetadata::GlobalAdvisoryScanWorker (CVS GA) raises ActiveRecord::NotNullViolation errors:

PG::NotNullViolation: ERROR:  null value in column "occurrence_id" of relation "vulnerability_occurrence_pipelines" violates not-null constraint

See https://new-sentry.gitlab.net/organizations/gitlab/issues/463951/?alert_rule_id=18&alert_type=issue&project=3&referrer=slack

When this bug occurs, the AdvisoryScanner fails to process the batch of affected SBOM occurrences, and it moves on to the next batch. See &11474 (comment 1681354223)

Further details

This error is triggered by the Gitlab::Ingestion::BulkInsertableTask when calling Security::Ingestion::IngestSliceBaseService#execute.

Steps to reproduce

Example Project

This has occurred on gitlab.com.

What is the current bug behavior?

The worker fails with the aforementioned error.

What is the expected correct behavior?

The worker does not fail.

Relevant logs and/or screenshots

https://new-sentry.gitlab.net/organizations/gitlab/issues/463951/?alert_rule_id=18&alert_type=issue&project=3&referrer=slack

stack trace

PG::NotNullViolation: ERROR:  null value in column "occurrence_id" of relation "vulnerability_occurrence_pipelines" violates not-null constraint
DETAIL:  Failing row contains (2645444227, 2023-11-24 07:10:32.527458+00, 2023-11-24 07:10:32.527458+00, null, 1027789557).

  from activerecord (7.0.8) lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params'
  from activerecord (7.0.8) lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `block (2 levels) in exec_no_cache'
  from activesupport (7.0.8) lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
  from activesupport (7.0.8) lib/active_support/dependencies/interlock.rb:41:in `permit_concurrent_loads'
  from activerecord (7.0.8) lib/active_record/connection_adapters/postgresql_adapter.rb:767:in `block in exec_no_cache'
  from activesupport (7.0.8) lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
  from activesupport (7.0.8) lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
  from activesupport (7.0.8) lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
  from activesupport (7.0.8) lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
  from activerecord (7.0.8) lib/active_record/connection_adapters/abstract_adapter.rb:752:in `block in log'
  from activesupport (7.0.8) lib/active_support/notifications/instrumenter.rb:24:in `instrument'
  from activerecord (7.0.8) lib/active_record/connection_adapters/abstract_adapter.rb:743:in `log'
  from activerecord (7.0.8) lib/active_record/connection_adapters/postgresql_adapter.rb:766:in `exec_no_cache'
  from activerecord (7.0.8) lib/active_record/connection_adapters/postgresql_adapter.rb:745:in `execute_and_clear'
  from marginalia (1.11.1) lib/marginalia.rb:91:in `execute_and_clear_with_marginalia'
  from activerecord (7.0.8) lib/active_record/connection_adapters/postgresql/database_statements.rb:54:in `exec_query'
  from activerecord (7.0.8) lib/active_record/connection_adapters/abstract/database_statements.rb:150:in `exec_insert_all'
  from activerecord (7.0.8) lib/active_record/connection_adapters/abstract/query_cache.rb:22:in `exec_insert_all'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer'
  from lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
  from lib/gitlab/database/load_balancing/load_balancer.rb:235:in `retry_with_backoff'
  from lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:61:in `block (2 levels) in <class:ConnectionProxy>'
  from activerecord (7.0.8) lib/active_record/insert_all.rb:41:in `execute'
  from app/models/concerns/bulk_insert_safe.rb:163:in `block (2 levels) in _bulk_insert_all!'
  from app/models/concerns/bulk_insert_safe.rb:157:in `each'
  from app/models/concerns/bulk_insert_safe.rb:157:in `each_slice'
  from app/models/concerns/bulk_insert_safe.rb:157:in `each'
  from app/models/concerns/bulk_insert_safe.rb:157:in `flat_map'
  from app/models/concerns/bulk_insert_safe.rb:157:in `block in _bulk_insert_all!'
  from app/models/concerns/cross_database_modification.rb:92:in `block in transaction'
  from activerecord (7.0.8) lib/active_record/connection_adapters/abstract/database_statements.rb:314:in `transaction'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer'
  from lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
  from lib/gitlab/database/load_balancing/load_balancer.rb:235:in `retry_with_backoff'
  from lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:78:in `transaction'
  from activerecord (7.0.8) lib/active_record/transactions.rb:209:in `transaction'
  from lib/gitlab/database.rb:359:in `block in transaction'
  from activesupport (7.0.8) lib/active_support/notifications.rb:206:in `block in instrument'
  from activesupport (7.0.8) lib/active_support/notifications/instrumenter.rb:24:in `instrument'
  from activesupport (7.0.8) lib/active_support/notifications.rb:206:in `instrument'
  from lib/gitlab/database.rb:358:in `transaction'
  from app/models/concerns/cross_database_modification.rb:83:in `transaction'
  from app/models/concerns/bulk_insert_safe.rb:156:in `_bulk_insert_all!'
  from app/models/concerns/bulk_insert_safe.rb:91:in `bulk_insert!'
  from ee/lib/gitlab/ingestion/bulk_insertable_task.rb:92:in `bulk_insert'
  from ee/lib/gitlab/ingestion/bulk_insertable_task.rb:84:in `block in return_data'
  from gems/gitlab-utils/lib/gitlab/utils/strong_memoize.rb:34:in `strong_memoize'
  from ee/lib/gitlab/ingestion/bulk_insertable_task.rb:82:in `return_data'
  from ee/lib/gitlab/ingestion/bulk_insertable_task.rb:72:in `execute'
  from ee/app/services/security/ingestion/abstract_task.rb:7:in `execute'
  from ee/app/services/security/ingestion/ingest_slice_base_service.rb:26:in `execute_task'
  from ee/app/services/security/ingestion/ingest_slice_base_service.rb:17:in `block (2 levels) in execute'
  from ee/app/services/security/ingestion/ingest_slice_base_service.rb:17:in `each'
  from ee/app/services/security/ingestion/ingest_slice_base_service.rb:17:in `block in execute'
  from app/models/concerns/cross_database_modification.rb:92:in `block in transaction'
  from activerecord (7.0.8) lib/active_record/connection_adapters/abstract/transaction.rb:319:in `block in within_new_transaction'
  from activesupport (7.0.8) lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
  from activesupport (7.0.8) lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
  from activesupport (7.0.8) lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
  from activesupport (7.0.8) lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
  from activerecord (7.0.8) lib/active_record/connection_adapters/abstract/transaction.rb:317:in `within_new_transaction'
  from activerecord (7.0.8) lib/active_record/connection_adapters/abstract/database_statements.rb:316:in `transaction'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer'
  from lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
  from lib/gitlab/database/load_balancing/load_balancer.rb:235:in `retry_with_backoff'
  from lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:78:in `transaction'
  from activerecord (7.0.8) lib/active_record/transactions.rb:209:in `transaction'
  from lib/gitlab/database.rb:359:in `block in transaction'
  from activesupport (7.0.8) lib/active_support/notifications.rb:206:in `block in instrument'
  from activesupport (7.0.8) lib/active_support/notifications/instrumenter.rb:24:in `instrument'
  from activesupport (7.0.8) lib/active_support/notifications.rb:206:in `instrument'
  from lib/gitlab/database.rb:358:in `transaction'
  from app/models/concerns/cross_database_modification.rb:83:in `transaction'
  from ee/app/services/security/ingestion/ingest_slice_base_service.rb:16:in `execute'
  from ee/app/services/security/ingestion/ingest_slice_base_service.rb:7:in `execute'
  from ee/app/services/security/ingestion/ingest_cvs_slice_service.rb:22:in `execute'
  from ee/app/services/security/vulnerability_scanning/create_vulnerability_service.rb:33:in `execute'
  from ee/app/services/security/vulnerability_scanning/create_vulnerability_service.rb:22:in `execute'
  from ee/lib/gitlab/vulnerability_scanning/advisory_scanner.rb:109:in `create_vulnerabilities'
  from ee/lib/gitlab/vulnerability_scanning/advisory_scanner.rb:105:in `bulk_vulnerability_ingestion'
  from ee/lib/gitlab/vulnerability_scanning/advisory_scanner.rb:48:in `block (2 levels) in execute'
  from ee/app/finders/sbom/possibly_affected_occurrences_finder.rb:30:in `block in execute_in_batches'
  from app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
  from activerecord (7.0.8) lib/active_record/relation.rb:881:in `_scoping'
  from activerecord (7.0.8) lib/active_record/relation.rb:428:in `scoping'
  from activerecord (7.0.8) lib/active_record/scoping/default.rb:43:in `unscoped'
  from app/models/concerns/each_batch.rb:99:in `block in each_batch'
  from app/models/concerns/each_batch.rb:69:in `step'
  from app/models/concerns/each_batch.rb:69:in `each_batch'
  from activerecord (7.0.8) lib/active_record/relation/delegation.rb:108:in `public_send'
  from activerecord (7.0.8) lib/active_record/relation/delegation.rb:108:in `block in method_missing'
  from activerecord (7.0.8) lib/active_record/relation.rb:881:in `_scoping'
  from activerecord (7.0.8) lib/active_record/relation.rb:428:in `scoping'
  from activerecord (7.0.8) lib/active_record/relation/delegation.rb:108:in `method_missing'
  from ee/app/finders/sbom/possibly_affected_occurrences_finder.rb:24:in `execute_in_batches'
  from ee/lib/gitlab/vulnerability_scanning/advisory_scanner.rb:47:in `block in execute'
  from activerecord (7.0.8) lib/active_record/relation/delegation.rb:88:in `each'
  from activerecord (7.0.8) lib/active_record/relation/delegation.rb:88:in `each'
  from ee/lib/gitlab/vulnerability_scanning/advisory_scanner.rb:38:in `execute'
  from ee/lib/gitlab/vulnerability_scanning/advisory_scanner.rb:14:in `scan_projects_for'
  from ee/app/services/package_metadata/advisory_scan_service.rb:6:in `execute'
  from ee/app/workers/package_metadata/global_advisory_scan_worker.rb:20:in `handle_event'
  from lib/gitlab/event_store/subscriber.rb:36:in `perform'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:202:in `execute_job'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:170:in `block (2 levels) in process'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:177:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/skip_jobs.rb:49:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/database/load_balancing/sidekiq_server_middleware.rb:29:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/duplicate_jobs/strategies/until_executed.rb:17:in `perform'
  from lib/gitlab/sidekiq_middleware/duplicate_jobs/duplicate_job.rb:45:in `perform'
  from lib/gitlab/sidekiq_middleware/duplicate_jobs/server.rb:8:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/pause_control/strategies/base.rb:31:in `perform'
  from lib/gitlab/sidekiq_middleware/pause_control/strategy_handler.rb:22:in `perform'
  from lib/gitlab/sidekiq_middleware/pause_control/server.rb:8:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/worker_context.rb:9:in `wrap_in_optional_context'
  from lib/gitlab/sidekiq_middleware/worker_context/server.rb:19:in `block in call'
  from lib/gitlab/application_context.rb:130:in `block in use'
  from gitlab-labkit (0.34.0) lib/labkit/context.rb:35:in `with_context'
  from lib/gitlab/application_context.rb:130:in `use'
  from lib/gitlab/application_context.rb:64:in `with_context'
  from lib/gitlab/sidekiq_middleware/worker_context/server.rb:17:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_status/server_middleware.rb:7:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_versioning/middleware.rb:9:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/query_analyzer.rb:7:in `block in call'
  from lib/gitlab/database/query_analyzer.rb:37:in `within'
  from lib/gitlab/sidekiq_middleware/query_analyzer.rb:7:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/admin_mode/server.rb:14:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/instrumentation_logger.rb:9:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/batch_loader.rb:7:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/extra_done_log_metadata.rb:7:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/request_store_middleware.rb:8:in `block in call'
  from gems/gitlab-safe_request_store/lib/gitlab/safe_request_store.rb:66:in `enabling_request_store'
  from gems/gitlab-safe_request_store/lib/gitlab/safe_request_store.rb:59:in `ensure_request_store'
  from lib/gitlab/sidekiq_middleware/request_store_middleware.rb:7:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/server_metrics.rb:105:in `block in call'
  from lib/gitlab/sidekiq_middleware/server_metrics.rb:133:in `block in instrument'
  from lib/gitlab/metrics/background_transaction.rb:33:in `run'
  from lib/gitlab/sidekiq_middleware/server_metrics.rb:133:in `instrument'
  from lib/gitlab/sidekiq_middleware/server_metrics.rb:104:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from gitlab-labkit (0.34.0) lib/labkit/middleware/sidekiq/server.rb:21:in `block in call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:177:in `block in invoke'
  from gitlab-labkit (0.34.0) lib/labkit/middleware/sidekiq/context/server.rb:16:in `block in call'
  from gitlab-labkit (0.34.0) lib/labkit/context.rb:35:in `with_context'
  from gitlab-labkit (0.34.0) lib/labkit/middleware/sidekiq/context/server.rb:15:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:182:in `invoke'
  from gitlab-labkit (0.34.0) lib/labkit/middleware/sidekiq/server.rb:20:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/monitor.rb:10:in `block in call'
  from lib/gitlab/sidekiq_daemon/monitor.rb:46:in `within_job'
  from lib/gitlab/sidekiq_middleware/monitor.rb:9:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from lib/gitlab/sidekiq_middleware/size_limiter/server.rb:13:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from marginalia (1.11.1) lib/marginalia/sidekiq_instrumentation.rb:9:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from sentry-sidekiq (5.8.0) lib/sentry/sidekiq/sentry_context_middleware.rb:26:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from sentry-raven (3.1.2) lib/raven/integrations/sidekiq/cleanup_middleware.rb:7:in `call'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:179:in `block in invoke'
  from sidekiq (6.5.12) lib/sidekiq/middleware/chain.rb:182:in `invoke'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:169:in `block in process'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:136:in `block (6 levels) in dispatch'
  from sidekiq (6.5.12) lib/sidekiq/job_retry.rb:113:in `local'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:135:in `block (5 levels) in dispatch'
  from sidekiq (6.5.12) lib/sidekiq/rails.rb:14:in `block in call'
  from activesupport (7.0.8) lib/active_support/execution_wrapper.rb:92:in `wrap'
  from activesupport (7.0.8) lib/active_support/reloader.rb:72:in `block in wrap'
  from activesupport (7.0.8) lib/active_support/execution_wrapper.rb:92:in `wrap'
  from activesupport (7.0.8) lib/active_support/reloader.rb:71:in `wrap'
  from sidekiq (6.5.12) lib/sidekiq/rails.rb:13:in `call'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:131:in `block (4 levels) in dispatch'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:263:in `stats'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:126:in `block (3 levels) in dispatch'
  from lib/gitlab/sidekiq_logging/structured_logger.rb:21:in `call'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:125:in `block (2 levels) in dispatch'
  from sidekiq (6.5.12) lib/sidekiq/job_retry.rb:80:in `global'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:124:in `block in dispatch'
  from sidekiq (6.5.12) lib/sidekiq/job_logger.rb:39:in `prepare'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:123:in `dispatch'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:168:in `process'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:78:in `process_one'
  from sidekiq (6.5.12) lib/sidekiq/processor.rb:68:in `run'
  from sidekiq (6.5.12) lib/sidekiq/component.rb:8:in `watchdog'
  from sidekiq (6.5.12) lib/sidekiq/component.rb:17:in `block in safe_thread'

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info

(For installations with omnibus-gitlab package run and paste the output of:
`sudo gitlab-rake gitlab:env:info`)

(For installations from source run and paste the output of:
`sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)

Results of GitLab application Check

Expand for output related to the GitLab application check

(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true)
(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true)
(we will only investigate if the tests are passing)

Possible fixes