Loading
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_nameprojection usedSELECT *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_v2with explicit columns, no-op the blocked migration, and re-add the build_stats projection in a new migration.
What this MR does
- 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. - New migration
20260302052651— Drops the brokenby_project_pipeline_finished_at_nameprojection and recreates it asby_project_pipeline_finished_at_name_v2with explicit columns instead ofSELECT *. Using a new name avoids race conditions between the asyncDROPand theADD. - New migration
20260302052652— Re-does what the no-op'd migration originally did: addsbuild_stats_by_project_pipeline_finished_at_name_stage_name, materializes it, and drops the oldbuild_stats_by_project_pipeline_name_stage_name. - Updates
db/click_house/main.sql— ReplacesSELECT *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
How to set up and validate locally
- Run ClickHouse migrations:
bundle exec rake gitlab:clickhouse:migrate- 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;- Verify
by_project_pipeline_finished_at_nameno longer exists andby_project_pipeline_finished_at_name_v2is present:
SHOW CREATE TABLE ci_finished_builds;- 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