Fix broken ClickHouse projections on ci_finished_builds

What does this MR do and why?

Fixes a broken state on ci_finished_builds in ClickHouse, where all mutations (including DROP PROJECTION) fail with:

Missing columns: 'artifacts_size' 'when' 'retries_count' 'failure_reason' ...

  • The by_project_pipeline_finished_at_name projection used SELECT * and was materialized before 11 new columns were added, leaving its on-disk parts with missing columns.
  • This blocked all mutations on the table due to deduplicate_merge_projection_mode = 'rebuild'.
  • Recreate the projection as by_project_pipeline_finished_at_name_v2 with explicit columns, no-op the blocked migration, and re-add the build_stats projection in a new migration.

What this MR does

  1. No-ops 20260224114553 — This migration was stuck on staging/production because it runs mutations on the broken table. It is replaced by a later migration.
  2. New migration 20260302052651 — Drops the broken by_project_pipeline_finished_at_name projection and recreates it as by_project_pipeline_finished_at_name_v2 with explicit columns instead of SELECT *. Using a new name avoids race conditions between the async DROP and the ADD.
  3. New migration 20260302052652 — Re-does what the no-op'd migration originally did: adds build_stats_by_project_pipeline_finished_at_name_stage_name, materializes it, and drops the old build_stats_by_project_pipeline_name_stage_name.
  4. Updates db/click_house/main.sql — Replaces SELECT * with explicit column list in the projection definition.

Pre-deployment step for staging/production

  • Before deploying, stuck mutations must be killed manually:
KILL MUTATION WHERE table = 'ci_finished_builds' AND is_done = 0;

References

#591727 (closed)

How to set up and validate locally

  1. Run ClickHouse migrations:
 bundle exec rake gitlab:clickhouse:migrate
  1. Connect to the local ClickHouse console and verify projections:
SELECT name, type FROM system.projection_parts
   WHERE table = 'ci_finished_builds'
   GROUP BY name, type;
  1. Verify by_project_pipeline_finished_at_name no longer exists and by_project_pipeline_finished_at_name_v2 is present:
SHOW CREATE TABLE ci_finished_builds;
  1. Verify mutations are not stuck:
SELECT * FROM system.mutations WHERE table = 'ci_finished_builds' AND is_done = 0;

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Narendran

Merge request reports

Loading
Loading