ClickHouse Stuck Mutation Killer Guard with Kestra

id: clickhouse-stuck-mutation-killer-guard
namespace: company.team

inputs:
  - id: clickhouse_url
    type: STRING
    defaults: jdbc:clickhouse://localhost:8123/default
    description: ClickHouse JDBC URL for the server that holds the mutations.
      Example jdbc:clickhouse://clickhouse:8123/default.

  - id: use_secret_auth
    type: BOOL
    defaults: true
    description: Leave true in production so the connection reads
      CLICKHOUSE_USERNAME and CLICKHOUSE_PASSWORD from secrets. Set false only
      to pass credentials inline in a disposable test environment.

  - id: clickhouse_username
    type: STRING
    defaults: default
    description: Username used only when use_secret_auth is false. Ignored in production.

  - id: clickhouse_password
    type: STRING
    defaults: ""
    description: Password used only when use_secret_auth is false. Ignored in production.

  - id: database
    type: STRING
    defaults: default
    description: Database whose mutations are checked.

  - id: table
    type: STRING
    defaults: ""
    description: Optional single table to restrict the check to. Leave empty to
      check every table in the database.

  - id: stuck_minutes
    type: INT
    defaults: 30
    description: Age threshold in minutes for a mutation that has no fail reason.
      Only used when include_stuck_without_failure is true.

  - id: include_stuck_without_failure
    type: BOOL
    defaults: false
    description: When false, only failing mutations (a non empty latest_fail_reason)
      are targeted. When true, also target mutations with no fail reason that
      are older than stuck_minutes. Failing mutations are the safe default
      because a slow but progressing mutation would lose work if killed.

  - id: dry_run
    type: BOOL
    defaults: true
    description: When true, report the stuck or failing mutations without killing anything.

  - id: auto_remediate
    type: BOOL
    defaults: false
    description: When true and dry_run is false, kill the targeted mutations behind
      an approval gate, then re-read system.mutations.

variables:
  detect_sql: |
    SELECT
      database AS database,
      table AS table,
      mutation_id AS mutation_id,
      command AS command,
      toString(create_time) AS create_time,
      parts_to_do AS parts_to_do,
      latest_fail_reason AS latest_fail_reason
    FROM system.mutations
    WHERE database = '{{ inputs.database }}'
      AND is_done = 0
      AND ('{{ inputs.table }}' = '' OR table = '{{ inputs.table }}')
      AND (
        latest_fail_reason != ''
        OR ({{ inputs.include_stuck_without_failure ? 1 : 0 }} = 1 AND create_time < now() - INTERVAL {{ inputs.stuck_minutes }} MINUTE)
      )
    ORDER BY create_time

triggers:
  - id: hourly_mutation_check
    type: io.kestra.plugin.core.trigger.Schedule
    disabled: true
    cron: "0 * * * *"
    description: Hourly stuck mutation sweep. Disabled by default and pinned to safe
      mode so it reports without killing until an operator enables it.
    inputs:
      dry_run: true
      auto_remediate: false

tasks:
  - id: detect
    type: io.kestra.plugin.jdbc.clickhouse.Query
    description: Read system.mutations for the database, keep rows with is_done = 0
      that are failing (a non empty latest_fail_reason) or, when opted in, stuck
      past the age threshold. A failing mutation retries forever and blocks
      later mutations on the same table.
    url: "{{ inputs.clickhouse_url }}"
    username: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_USERNAME') :
      inputs.clickhouse_username }}"
    password: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_PASSWORD') :
      inputs.clickhouse_password }}"
    fetchType: FETCH
    sql: "{{ render(vars.detect_sql) }}"

  - id: summarize
    type: io.kestra.plugin.core.output.OutputValues
    description: Materialize the targeted mutation list once so the logs, the Slack
      alert and the failure message all read the same value.
    values:
      mutation_list: >-
        {{ outputs.detect.rows | jq('map(.table + "/" + .mutation_id) | join(",
        ")') | first }}
      reason_list: >-
        {{ outputs.detect.rows | jq('map(.table + "/" + .mutation_id + ": " +
        (if .latest_fail_reason == "" then "stuck without a fail reason" else
        .latest_fail_reason end)) | join(" || ")') | first }}

  - id: log_detect
    type: io.kestra.plugin.core.log.Log
    message: |
      ClickHouse {{ inputs.database }}: {{ outputs.detect.size }} stuck or failing mutation(s) with is_done = 0.
      Targeted: {{ outputs.summarize.values.mutation_list }}
      Reasons: {{ outputs.summarize.values.reason_list }}
      Mode: dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }} include_stuck_without_failure={{ inputs.include_stuck_without_failure }}

  - id: guard
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.detect.size > 0 }}"
    then:
      - id: findings_alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Alert on every detection, in dry run and in remediate mode, so a
          human sees the wedged mutations even when nothing is killed.
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {"text": "ClickHouse stuck mutation guard: {{ outputs.detect.size }} mutation(s) in {{ inputs.database }} with is_done=0. Targeted {{ outputs.summarize.values.mutation_list }}. dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }}."}

      - id: mode
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
        then:
          - id: approval_gate
            type: io.kestra.plugin.core.flow.Pause
            description: Hold the run until a human approves the kill. The KILL MUTATION
              step never starts on its own.
            pauseDuration: PT2H
            behavior: FAIL
            onResume:
              - id: approved
                type: BOOL
                defaults: false
                description: Set true to kill the listed mutations. Anything else leaves them in
                  place.

          - id: apply
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
            then:
              - id: remediation_kill_loop
                type: io.kestra.plugin.core.flow.Loop
                description: Remediation loop. One KILL MUTATION per targeted mutation,
                  concurrencyLimit 1 so the statements run in turn and never
                  race on the same table.
                concurrencyLimit: 1
                values: "{{ outputs.detect.rows | jq('map({table: .table, mutation_id:
                  .mutation_id})') | first | toJson }}"
                tasks:
                  - id: kill_one
                    type: io.kestra.plugin.jdbc.clickhouse.Query
                    description: Kill one targeted mutation, scoped by database, table and
                      mutation_id. allowFailure so one blocked kill does not
                      stop the pass and the re-read stays the real gate.
                    allowFailure: true
                    url: "{{ inputs.clickhouse_url }}"
                    username: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_USERNAME') :
                      inputs.clickhouse_username }}"
                    password: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_PASSWORD') :
                      inputs.clickhouse_password }}"
                    fetchType: FETCH
                    sql: "KILL MUTATION WHERE database = '{{ inputs.database }}' AND table = '{{
                      fromJson(item.value).table }}' AND mutation_id = '{{
                      fromJson(item.value).mutation_id }}'"

                  - id: kill_log
                    type: io.kestra.plugin.core.log.Log
                    message: "Issued KILL MUTATION for {{ fromJson(item.value).table }}/{{
                      fromJson(item.value).mutation_id }} on {{ inputs.database
                      }}."

              - id: reverify
                type: io.kestra.plugin.jdbc.clickhouse.Query
                description: Re-read system.mutations with the same filter. Any row here means a
                  targeted mutation is still is_done = 0 and the kill did not
                  land.
                url: "{{ inputs.clickhouse_url }}"
                username: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_USERNAME') :
                  inputs.clickhouse_username }}"
                password: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_PASSWORD') :
                  inputs.clickhouse_password }}"
                fetchType: FETCH
                sql: "{{ render(vars.detect_sql) }}"

              - id: verify_result
                type: io.kestra.plugin.core.flow.If
                condition: "{{ outputs.reverify.size > 0 }}"
                then:
                  - id: remediation_failed
                    type: io.kestra.plugin.core.execution.Fail
                    errorMessage: >-
                      {{ outputs.reverify.size }} targeted mutation(s) still
                      is_done = 0 after the kill pass in {{ inputs.database }}:
                      {{ outputs.reverify.rows | jq('map(.table + "/" +
                      .mutation_id) | join(", ")') | first }}. The connecting
                      user may lack the ALTER privilege or a new failing
                      mutation was submitted during the run.
                else:
                  - id: remediated
                    type: io.kestra.plugin.core.log.Log
                    message: "Killed and verified. {{ outputs.detect.size }} targeted mutation(s)
                      before, {{ outputs.reverify.size }} after."
            else:
              - id: not_approved
                type: io.kestra.plugin.core.log.Log
                message: "Approval not granted, so nothing was killed. {{ outputs.detect.size }}
                  mutation(s) still wedged in {{ inputs.database }}."
        else:
          - id: report_only
            type: io.kestra.plugin.core.log.Log
            message: "Report only (dry_run={{ inputs.dry_run }}, auto_remediate={{
              inputs.auto_remediate }}). {{ outputs.detect.size }} mutation(s)
              would be killed: {{ outputs.summarize.values.mutation_list }}. No
              change made."
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: "No stuck or failing mutations in {{ inputs.database }} (is_done = 0
          with a fail reason or past the age threshold when opted in). Nothing
          to kill."

errors:
  - id: alert_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Post to Slack on any failure, including a connection error or
      targeted mutations still present after a kill pass.
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {"text": "ClickHouse stuck mutation guard FAILED for {{ inputs.database }} (execution {{ execution.id }}). A connection error, a KILL error or mutations still is_done=0 after a kill pass. Check the run."}

An async ClickHouse mutation (ALTER TABLE ... UPDATE or DELETE) can wedge. A bad expression makes it throw on every retry, so it sits in system.mutations with is_done = 0 and a latest_fail_reason, blocking later mutations on the same table and holding merge resources. This guard finds those mutations and kills the failing ones under a gate, then proves none are still pending.

This blueprint was created by zkasuran.

Safety

Read-only by default. dry_run is true and auto_remediate is false, so a plain run reports and never kills.
A kill happens only when auto_remediate is true and dry_run is false. It then waits for a human to resume the Pause with approved set true. An unanswered gate reads as not approved.
Failing mutations are the default target. A mutation that is merely slow but still progressing is left alone unless you opt in with include_stuck_without_failure, because killing it would discard the work it has already done.
Each kill is scoped by database, table and mutation_id and runs one statement at a time. The re-read of system.mutations is the real gate, so a kill the user is not allowed to run surfaces as a failed run rather than a false success.

How it works

detect (io.kestra.plugin.jdbc.clickhouse.Query) reads system.mutations for the database and keeps rows with is_done = 0 that either have a non empty latest_fail_reason or, when include_stuck_without_failure is true, were created more than stuck_minutes ago.
summarize (io.kestra.plugin.core.output.OutputValues) records the targeted mutation list and the fail reasons once, so the log, the alert and the failure message all read the same value.
log_detect (io.kestra.plugin.core.log.Log) prints the count, the mutation ids and the fail reasons.
guard (io.kestra.plugin.core.flow.If) branches on the targeted count. With none found, clean logs and the run ends.
findings_alert (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) posts the wedged mutations on every detection, in dry run and in remediate mode.
mode (io.kestra.plugin.core.flow.If) enters the remediation branch only when auto_remediate is true and dry_run is false. Otherwise report_only lists what would be killed and stops.
approval_gate (io.kestra.plugin.core.flow.Pause) holds the run until a human resumes it with approved. apply runs the kill only when approved is true.
remediation_kill_loop (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs KILL MUTATION WHERE database = ... AND table = ... AND mutation_id = ... once per targeted mutation.
reverify re-reads system.mutations with the same filter. verify_result then fails the run through io.kestra.plugin.core.execution.Fail if any targeted mutation is still is_done = 0.

What you get

A check that reads the real mutation state from system.mutations, so it catches the exact mutations that retry forever and block the queue.
A kill that is safe to leave scheduled. It reports until an operator turns on remediation and approves each run.
A real verification step. The run passes only when the second read of system.mutations shows zero targeted mutations.
Slack alerts on the findings and on any failure.

Who it's for

Data platform engineers who run ClickHouse and hit mutations wedged by a bad ALTER expression.
DBAs who need to clear a failing mutation that is holding up every later mutation on a table.
Teams evaluating the ClickHouse plugin who want an example that reads a system table and runs gated, verified remediation.

Why orchestrate this with Kestra

ClickHouse has no scheduler, no approval step and no way to prove a KILL MUTATION took effect. Kestra adds the missing pieces. It schedules the sweep, gates the kill behind dry-run and approval, re-reads system.mutations to turn a hopeful kill into a verified one, applies the same connection settings to every task and posts a Slack alert the moment a step fails.

Tested end to end

Run on Kestra 2.0.5 against a ClickHouse 24.8 server. A MergeTree table of five rows was wedged with a real failing mutation (ALTER TABLE ... UPDATE amount = toUInt64(raw) where raw holds non numeric strings), confirmed stuck in system.mutations with is_done = 0 and a Cannot parse fail reason.

Detector can fail: a healthy table returned 0 targeted mutations, the same read after the wedge returned 1 (confirmed with clickhouse-client against system.mutations).
Dry run detected the failing mutation, alerted and left it in place (still is_done = 0 afterwards).
Remediate with approval ran KILL MUTATION and the re-read reported 0 remaining (1 before, 0 after in system.mutations).
The approval gate held. A run resumed with approved false killed nothing.
A read-only user turned the kill into a no-op, the re-read found the mutation still pending and the run failed and alerted.
An unreachable host failed the detect step and fired the Slack alert.

Prerequisites

A ClickHouse server reachable over the JDBC HTTP interface and a user that can read system.mutations and run KILL MUTATION on the target tables.

Secrets

CLICKHOUSE_USERNAME: ClickHouse user, read when use_secret_auth is true.
CLICKHOUSE_PASSWORD: password for that user.
SLACK_WEBHOOK_URL: used by findings_alert and the errors block.
In Kestra OSS, provide each secret as a base64-encoded environment variable prefixed SECRET_.

Inputs

Name	Type	Default	Description
`clickhouse_url`	STRING	`jdbc:clickhouse://localhost:8123/default`	ClickHouse JDBC URL.
`use_secret_auth`	BOOL	`true`	Read credentials from secrets. Set false only for inline test credentials.
`database`	STRING	`default`	Database whose mutations are checked.
`table`	STRING	`""`	Optional single table filter. Empty checks every table.
`stuck_minutes`	INT	`30`	Age threshold for a mutation with no fail reason.
`include_stuck_without_failure`	BOOL	`false`	Also target slow mutations past the age threshold.
`dry_run`	BOOL	`true`	Report without killing.
`auto_remediate`	BOOL	`false`	Kill behind the approval gate when dry_run is false.

Quick start

Set CLICKHOUSE_USERNAME, CLICKHOUSE_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.
Import this flow.
Run it with database pointed at a ClickHouse database. The default dry run reports the stuck or failing mutations.
To kill, run again with dry_run false and auto_remediate true, then resume the paused execution with approved true.

How to extend

Set include_stuck_without_failure true to also clear mutations that have no fail reason but have not finished within stuck_minutes.
Fail the run as soon as detection finds anything, for a read-only drift check with no remediation.
Add a second window that only alerts, so a human sees a wedged mutation before it is killed.
Pair this with a check on the ALTER that submitted the mutation, so the bad expression is fixed and the mutation does not come back.

ClickHouse Stuck Mutation Killer Guard

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

ClickHouse Stuck Mutation Killer Guard

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

Build a Chat-With-Your-Data System Using Elasticsearch and OpenAI (RAG)

Score change requests on risk and build the CAB agenda, pre-approving the low-risk ones

Watch Cited Papers for Retractions, Expressions of Concern and Corrections via Crossref

Chroma Collection Distance Metric Validator

ChromaDB Vector Collection Snapshot and AWS S3 Disaster Recovery Pipeline

Incremental Docs-to-Chroma RAG Sync with Deletions and a Retrieval Smoke Test

More Infrastructure Blueprints

Kafka Consumer Lag Sentinel

Kafka Client Quota Guard

Self-heal Kafka Connect connectors with a restart budget and escalation

Kafka ACL Baseline Restore Guard

Kafka Connect Paused Connector Resume Guard

Kafka Consumer Group Offset Checkpoint and Rewind

New to Kestra?