Clear a desynced MariaDB Galera node with Kestra

id: mariadb-galera-desynced-node-resync-guard
namespace: company.team

description: |
  Find a MariaDB Galera node that is stuck out of sync, alert Slack, then optionally clear
  wsrep_desync behind an approval and fail the run if the node does not come back Synced.
  A node left at wsrep_desync=ON after a state snapshot transfer stops sending flow control to
  the rest of the cluster and stops counting toward the quorum math. A donor whose transfer died
  ends up the same way. It still accepts connections and still serves reads, so monitoring that
  checks
  a port or a SELECT sees a healthy node while cluster redundancy is already gone. The guard
  reads the node's own wsrep picture in one query, separates an operator desync from a state
  transfer in progress, waits out a grace window before calling it stuck, then clears the flag
  only when asked. It refuses outright while the node is donating or receiving a transfer,
  because clearing desync on a live donor aborts the transfer. Read-only by default.

inputs:
  - id: jdbc_url
    type: STRING
    displayName: MariaDB JDBC URL
    description: One Galera node. Point it at the node you want checked, not at a
      load balancer.
    defaults: "jdbc:mariadb://mariadb-galera-node:3306/mysql"

  - id: username
    type: STRING
    displayName: MariaDB user
    description: >-
      Needs SELECT on information_schema to detect. Clearing the flag also needs
      SUPER or SYSTEM_VARIABLES_ADMIN.
    defaults: root

  - id: password
    type: STRING
    displayName: MariaDB password
    description: Defaults to the MARIADB_PASSWORD secret. Override per run only for
      a local test node.
    defaults: "{{ secret('MARIADB_PASSWORD') }}"

  - id: grace_minutes
    type: INT
    displayName: Grace window in minutes
    description: >-
      How long the node may sit out of sync before it counts as stuck. The first
      run records when the desync was first seen. Nothing is reported until the
      window has passed.
    defaults: 10

  - id: resync_settle
    type: DURATION
    displayName: Settle before the re-read
    description: >-
      How long to wait after clearing the flag before reading the wsrep state
      again. A node with a write backlog goes through Joined before it reaches
      Synced.
    defaults: PT10S

  - id: auto_remediate
    type: BOOL
    displayName: Auto remediate
    description: Must be true, together with dry_run false, before anything is written.
    defaults: false

  - id: dry_run
    type: BOOL
    displayName: Dry run
    description: Must be false, together with auto_remediate true, for SET GLOBAL
      wsrep_desync to run.
    defaults: true

tasks:
  - id: read_wsrep
    type: io.kestra.plugin.jdbc.mariadb.Query
    description: >-
      Read-only. One query for the whole wsrep picture. wsrep_desync is the
      operator flag. WSREP_DESYNC_COUNT is how many desync holders are
      outstanding, which is what separates an operator desync from a state
      transfer Galera is holding itself.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH_ONE
    sql: |
      SELECT
        CAST(@@global.wsrep_on AS SIGNED) AS wsrep_on,
        CAST(@@global.wsrep_desync AS SIGNED) AS desync_var,
        CAST(COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
          WHERE VARIABLE_NAME = 'WSREP_DESYNC_COUNT'), '0') AS SIGNED) AS desync_count,
        CAST(COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
          WHERE VARIABLE_NAME = 'WSREP_LOCAL_STATE'), '0') AS SIGNED) AS local_state,
        COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
          WHERE VARIABLE_NAME = 'WSREP_LOCAL_STATE_COMMENT'), 'unknown') AS state_comment,
        COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
          WHERE VARIABLE_NAME = 'WSREP_CLUSTER_STATUS'), 'unknown') AS cluster_status,
        CAST(COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
          WHERE VARIABLE_NAME = 'WSREP_CLUSTER_SIZE'), '0') AS SIGNED) AS cluster_size,
        COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
          WHERE VARIABLE_NAME = 'WSREP_READY'), 'unknown') AS wsrep_ready,
        COALESCE(NULLIF(@@global.wsrep_node_name, ''), @@hostname) AS node_name,
        CAST(UNIX_TIMESTAMP() AS SIGNED) AS now_epoch

  - id: read_cursor
    type: io.kestra.plugin.core.kv.Get
    description: >-
      Read-only. The epoch second this node was first seen out of sync, written
      by a previous run. Missing means this run is the first sighting. No TTL,
      so the window is never lost halfway through.
    key: "galera_desync_first_seen_{{ inputs.jdbc_url | slugify }}"
    errorOnMissing: false

  - id: classify
    type: io.kestra.plugin.core.output.OutputValues
    description: >-
      One bucket for the node, read in order. A state transfer is checked before
      the operator flag, so a node Galera is using cannot fall through to the
      arm that writes.
    values:
      bucket: >-
        {% if outputs.read_wsrep.row.wsrep_on != 1 %}wsrep_off{% elseif
        outputs.read_wsrep.row.desync_count > outputs.read_wsrep.row.desync_var
        or outputs.read_wsrep.row.local_state == 1 or
        outputs.read_wsrep.row.local_state == 3 %}transfer{% elseif
        outputs.read_wsrep.row.cluster_status != 'Primary' or
        outputs.read_wsrep.row.wsrep_ready != 'ON' %}nonprimary{% elseif
        outputs.read_wsrep.row.desync_var == 1 or
        outputs.read_wsrep.row.state_comment != 'Synced' %}desynced{% else
        %}synced{% endif %}
      age_seconds: >-
        {{ outputs.read_wsrep.row.now_epoch - (((outputs.read_cursor.value ??
        outputs.read_wsrep.row.now_epoch) ~ '') | number) }}

  - id: observed
    type: io.kestra.plugin.core.log.Log
    message: >-
      Galera node {{ outputs.read_wsrep.row.node_name }} on {{ inputs.jdbc_url
      }} reads bucket={{ outputs.classify.values.bucket }} wsrep_desync={{
      outputs.read_wsrep.row.desync_var }} desync_count={{
      outputs.read_wsrep.row.desync_count }} local_state={{
      outputs.read_wsrep.row.local_state }} state_comment={{
      outputs.read_wsrep.row.state_comment }} cluster_status={{
      outputs.read_wsrep.row.cluster_status }} cluster_size={{
      outputs.read_wsrep.row.cluster_size }} wsrep_ready={{
      outputs.read_wsrep.row.wsrep_ready }} desync_age_seconds={{
      outputs.classify.values.age_seconds }} grace_seconds={{
      inputs.grace_minutes * 60 }}

  - id: route
    type: io.kestra.plugin.core.flow.Switch
    description: Routes on the bucket. Only the desynced arm can ever write.
    value: "{{ outputs.classify.values.bucket }}"
    cases:
      synced:
        - id: clear_cursor
          type: io.kestra.plugin.core.kv.Delete
          description: >-
            The node is Synced, so drop the first-seen marker. The next desync
            starts its own grace window instead of inheriting an old one.
          key: "galera_desync_first_seen_{{ inputs.jdbc_url | slugify }}"
          errorOnMissing: false

        - id: no_findings
          type: io.kestra.plugin.core.log.Log
          message: >-
            No findings. {{ outputs.read_wsrep.row.node_name }} is Synced with
            wsrep_desync off and no desync holders, in a {{
            outputs.read_wsrep.row.cluster_status }} component of {{
            outputs.read_wsrep.row.cluster_size }} node(s).

      transfer:
        - id: refuse_transfer
          type: io.kestra.plugin.core.log.Log
          level: WARN
          message: >-
            Refusing to touch wsrep_desync on {{
            outputs.read_wsrep.row.node_name }}. A state transfer is in
            progress. desync_count={{ outputs.read_wsrep.row.desync_count }}
            sits above the operator flag wsrep_desync={{
            outputs.read_wsrep.row.desync_var }}. The other way in is
            local_state={{ outputs.read_wsrep.row.local_state }} ({{
            outputs.read_wsrep.row.state_comment }}), a node still receiving a
            transfer or still applying the catch up queue. Galera holds its own
            desync for the length of an SST it donates. Clearing the flag under
            a live donor aborts the transfer part way through, which leaves the
            joiner with an unusable data directory. Let the transfer finish,
            then run this guard again.

        - id: alert_transfer
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: >-
            {{ {'text': 'Galera node ' ~ outputs.read_wsrep.row.node_name ~ ' on
            ' ~ inputs.jdbc_url ~ ' is out of sync with a state transfer in
            progress (state ' ~ outputs.read_wsrep.row.state_comment ~ ',
            desync_count ' ~ outputs.read_wsrep.row.desync_count ~ ',
            wsrep_desync ' ~ outputs.read_wsrep.row.desync_var ~ '). Refusing to
            clear the flag so the transfer is not aborted.'} | toJson }}

      nonprimary:
        - id: refuse_nonprimary
          type: io.kestra.plugin.core.log.Log
          level: WARN
          message: >-
            Refusing to touch wsrep_desync on {{
            outputs.read_wsrep.row.node_name }}. cluster_status={{
            outputs.read_wsrep.row.cluster_status }} and wsrep_ready={{
            outputs.read_wsrep.row.wsrep_ready }}, so the node is not in a
            primary component. Clearing desync cannot rejoin a node that has no
            quorum to rejoin. Fix the partition first.

        - id: alert_nonprimary
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: >-
            {{ {'text': 'Galera node ' ~ outputs.read_wsrep.row.node_name ~ ' on
            ' ~ inputs.jdbc_url ~ ' is not in a primary component
            (cluster_status ' ~ outputs.read_wsrep.row.cluster_status ~ ',
            wsrep_ready ' ~ outputs.read_wsrep.row.wsrep_ready ~ '). Quorum has
            to come back before a resync makes sense.'} | toJson }}

      wsrep_off:
        - id: not_clustered
          type: io.kestra.plugin.core.log.Log
          message: >-
            Nothing to check. wsrep_on is off on {{
            outputs.read_wsrep.row.node_name }}, so this server is a standalone
            MariaDB rather than a Galera node.

      desynced:
        - id: arm_cursor
          type: io.kestra.plugin.core.kv.Set
          description: >-
            Records the earliest epoch second this desync was seen. An existing
            value is kept, so the window measures the real age rather than
            restarting every run.
          key: "galera_desync_first_seen_{{ inputs.jdbc_url | slugify }}"
          value: "{{ outputs.read_cursor.value ?? outputs.read_wsrep.row.now_epoch }}"
          overwrite: true

        - id: grace_gate
          type: io.kestra.plugin.core.flow.If
          description: A desync younger than the grace window is normal operations, not a
            finding.
          condition: "{{ (outputs.classify.values.age_seconds | number) >=
            (inputs.grace_minutes * 60) }}"
          then:
            - id: finding
              type: io.kestra.plugin.core.log.Log
              level: WARN
              message: >-
                Finding. {{ outputs.read_wsrep.row.node_name }} has been out of
                sync for {{ outputs.classify.values.age_seconds }}s, past the {{
                inputs.grace_minutes * 60 }}s grace window. wsrep_desync is {{
                outputs.read_wsrep.row.desync_var }}, state is {{
                outputs.read_wsrep.row.state_comment }}. The node is still
                serving reads while it carries no flow control weight. Statement
                that would fix it on this node is SET GLOBAL wsrep_desync = OFF.

            - id: alert_finding
              type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
              url: "{{ secret('SLACK_WEBHOOK_URL') }}"
              payload: >-
                {{ {'text': 'Galera node ' ~ outputs.read_wsrep.row.node_name ~
                ' on ' ~ inputs.jdbc_url ~ ' has been out of sync for ' ~
                outputs.classify.values.age_seconds ~ 's (wsrep_desync ' ~
                outputs.read_wsrep.row.desync_var ~ ', state ' ~
                outputs.read_wsrep.row.state_comment ~ ', cluster size ' ~
                outputs.read_wsrep.row.cluster_size ~ '). It still answers reads
                while it carries no flow control weight.' ~
                (inputs.auto_remediate and not inputs.dry_run ? ' Asking for
                approval to clear wsrep_desync now.' : ' No change applied
                (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~
                inputs.dry_run ~ ').')} | toJson }}

            - id: remediate_gate
              type: io.kestra.plugin.core.flow.If
              description: >-
                The only arm that writes. Needs auto_remediate true, dry_run
                false, then a human on the Pause.
              condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
              then:
                - id: approve_resync
                  type: io.kestra.plugin.core.flow.Pause
                  description: >-
                    Answer true to clear wsrep_desync on this node. Check first
                    that no joiner is waiting on it for a transfer.
                  onResume:
                    - id: approved
                      type: BOOL
                      displayName: Clear wsrep_desync on this node
                      description: True clears the flag. Anything else leaves the node untouched.
                      defaults: false

                - id: approved_gate
                  type: io.kestra.plugin.core.flow.If
                  description: An unanswered or declined Pause reads as false, so nothing is
                    written.
                  condition: "{{ outputs.approve_resync.onResume.approved ?? false }}"
                  then:
                    - id: clear_desync
                      type: io.kestra.plugin.jdbc.mariadb.Query
                      description: >-
                        The write. Clears the operator desync flag on this node
                        only. allowFailure is on deliberately. A refused SET
                        GLOBAL has to reach the re-read rather than end the run,
                        because the re-read is the only thing that decides
                        whether the node is back in the cluster.
                      url: "{{ inputs.jdbc_url }}"
                      username: "{{ inputs.username }}"
                      password: "{{ inputs.password }}"
                      fetchType: NONE
                      allowFailure: true
                      sql: "SET GLOBAL wsrep_desync = OFF"

                    - id: settle
                      type: io.kestra.plugin.core.flow.Sleep
                      description: >-
                        A node with a write backlog passes through Joined on its
                        way to Synced. Give it the settle window before reading
                        again.
                      duration: "{{ inputs.resync_settle }}"

                    - id: verify_resync
                      type: io.kestra.plugin.jdbc.mariadb.Query
                      description: >-
                        Read-only re-read. The write task going green carries no
                        information, a privilege-refused SET GLOBAL still
                        reports success. This is the authority.
                      url: "{{ inputs.jdbc_url }}"
                      username: "{{ inputs.username }}"
                      password: "{{ inputs.password }}"
                      fetchType: FETCH_ONE
                      sql: |
                        SELECT
                          CAST(@@global.wsrep_desync AS SIGNED) AS desync_var,
                          CAST(COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
                            WHERE VARIABLE_NAME = 'WSREP_DESYNC_COUNT'), '0') AS SIGNED) AS desync_count,
                          CAST(COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
                            WHERE VARIABLE_NAME = 'WSREP_LOCAL_STATE'), '0') AS SIGNED) AS local_state,
                          COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
                            WHERE VARIABLE_NAME = 'WSREP_LOCAL_STATE_COMMENT'), 'unknown') AS state_comment,
                          COALESCE((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
                            WHERE VARIABLE_NAME = 'WSREP_READY'), 'unknown') AS wsrep_ready

                    - id: assert_resync
                      type: io.kestra.plugin.core.flow.If
                      description: Fails the run when the node did not come back Synced.
                      condition: >-
                        {{ outputs.verify_resync.row.desync_var == 0 and
                        outputs.verify_resync.row.local_state == 4 and
                        outputs.verify_resync.row.state_comment == 'Synced' and
                        outputs.verify_resync.row.wsrep_ready == 'ON' }}
                      then:
                        - id: resync_confirmed
                          type: io.kestra.plugin.core.kv.Delete
                          description: Back in sync, so drop the first-seen marker.
                          key: "galera_desync_first_seen_{{ inputs.jdbc_url | slugify }}"
                          errorOnMissing: false

                        - id: resync_logged
                          type: io.kestra.plugin.core.log.Log
                          message: >-
                            Resync confirmed on {{
                            outputs.read_wsrep.row.node_name }}. Before
                            wsrep_desync={{ outputs.read_wsrep.row.desync_var }}
                            desync_count={{ outputs.read_wsrep.row.desync_count
                            }} local_state={{ outputs.read_wsrep.row.local_state
                            }} state={{ outputs.read_wsrep.row.state_comment }}.
                            After wsrep_desync={{
                            outputs.verify_resync.row.desync_var }}
                            desync_count={{
                            outputs.verify_resync.row.desync_count }}
                            local_state={{ outputs.verify_resync.row.local_state
                            }} state={{ outputs.verify_resync.row.state_comment
                            }}.
                      else:
                        - id: resync_failed
                          type: io.kestra.plugin.core.execution.Fail
                          description: >-
                            The node is still out of sync after the settle
                            window. The usual cause is a user without SUPER or
                            SYSTEM_VARIABLES_ADMIN, so the SET GLOBAL was
                            refused. The other two are a second desync holder
                            still outstanding and a backlog larger than
                            resync_settle.
                          errorMessage: >-
                            Resync did not land on {{
                            outputs.read_wsrep.row.node_name }}. After the write
                            and the settle window the node still reads
                            wsrep_desync={{ outputs.verify_resync.row.desync_var
                            }} desync_count={{
                            outputs.verify_resync.row.desync_count }}
                            local_state={{ outputs.verify_resync.row.local_state
                            }} state={{ outputs.verify_resync.row.state_comment
                            }} wsrep_ready={{
                            outputs.verify_resync.row.wsrep_ready }}.
                  else:
                    - id: declined
                      type: io.kestra.plugin.core.log.Log
                      message: >-
                        Approval declined or left unanswered. wsrep_desync is
                        untouched on {{ outputs.read_wsrep.row.node_name }}.
              else:
                - id: report_only
                  type: io.kestra.plugin.core.log.Log
                  message: >-
                    Report only. auto_remediate={{ inputs.auto_remediate }}
                    dry_run={{ inputs.dry_run }}, so nothing was written. {{
                    outputs.read_wsrep.row.node_name }} still reads
                    wsrep_desync={{ outputs.read_wsrep.row.desync_var }}
                    state={{ outputs.read_wsrep.row.state_comment }}. Run with
                    auto_remediate true and dry_run false to be asked for
                    approval.
          else:
            - id: inside_grace
              type: io.kestra.plugin.core.log.Log
              message: >-
                {{ outputs.read_wsrep.row.node_name }} is out of sync but only
                for {{ outputs.classify.values.age_seconds }}s, inside the {{
                inputs.grace_minutes * 60 }}s grace window. First seen at epoch
                {{ outputs.read_cursor.value ?? outputs.read_wsrep.row.now_epoch
                }}. Nothing reported yet.
    defaults:
      - id: unknown_bucket
        type: io.kestra.plugin.core.log.Log
        level: WARN
        message: >-
          Unexpected bucket {{ outputs.classify.values.bucket }} for {{
          inputs.jdbc_url }}. No change applied.

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: >-
      {{ {'text': 'Galera desynced node resync guard failed on ' ~
      inputs.jdbc_url ~ ' (execution ' ~ execution.id ~ '). The node may still
      be out of sync. Check the logs.'} | toJson }}

triggers:
  - id: every_fifteen_minutes
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled. Always runs in safe mode (dry_run true,
      auto_remediate false).
    cron: "*/15 * * * *"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true

Findings

A node left at wsrep_desync=ON answers connections, answers SELECT 1 and passes most health checks. It is not in the cluster's flow control. The cluster can therefore outrun it without ever slowing down.
SHOW GLOBAL STATUS LIKE 'wsrep_desync' returns nothing. There is no such status variable. The flag is the system variable @@global.wsrep_desync. The status side of it is the counter WSREP_DESYNC_COUNT.
wsrep_local_state_comment reads Donor/Desynced for an operator desync as well as for a node donating a state transfer. The state comment on its own cannot tell the two apart, so a guard that keys on it alone will clear the flag on a live donor.
WSREP_DESYNC_COUNT is how many desync holders are outstanding. An operator desync is count 1 with the variable at 1. Galera takes its own desync for the length of a transfer it donates, without touching the variable, so a donation puts the count above the variable. MariaDB's own SST scripts never set the variable, which is what makes the comparison sound.
A node that already has wsrep_desync=ON is not eligible to donate. Galera logs No donor candidates temporarily available in suitable state and the joiner keeps asking. So the count can only exceed the variable through a desync Galera took itself, which is what the refusal keys on.
A node in a non-primary component cannot be desynced at all. SET GLOBAL wsrep_desync=ON there fails with ERROR 1396 Operation 'desync' failed, so the quorum refusal is not caution, it is the only correct answer.

Safety

Read-only unless auto_remediate is true, dry_run is false and a human answers the approve_resync Pause with true. An unanswered Pause reads as false.
The flow refuses to write while a state transfer is in progress, even when the operator flag is set. That check runs before the desync bucket, so a node Galera is using never reaches the arm that writes.
It also refuses when the node is not in a primary component. Clearing desync cannot rejoin a node that has no quorum.
The write touches one node and one variable. It never changes cluster membership, never restarts anything, never touches data.
SET GLOBAL wsrep_desync = OFF runs with allowFailure. That is on purpose. A refused write reports success on plenty of systems, so the re-read is the authority. Letting the write end the run would skip the only step that knows the truth.

How it works

read_wsrep reads the node's whole wsrep picture in one query through io.kestra.plugin.jdbc.mariadb.Query: @@global.wsrep_on, @@global.wsrep_desync, then WSREP_DESYNC_COUNT, WSREP_LOCAL_STATE, WSREP_LOCAL_STATE_COMMENT, WSREP_CLUSTER_STATUS, WSREP_CLUSTER_SIZE plus WSREP_READY from information_schema.GLOBAL_STATUS. It also returns the node's own UNIX_TIMESTAMP(), so the grace window is measured on the database clock.
read_cursor reads the KV marker holding the epoch second this node was first seen out of sync. Missing means this run is the first sighting.
classify puts the node in one bucket, in a fixed order: wsrep_off, transfer, nonprimary, desynced, synced. It also works out the desync age in seconds.
route is a Switch over that bucket. synced deletes the marker then reports no findings. transfer logs the refusal with the numbers behind it then alerts Slack. nonprimary does the same for a node with no quorum. wsrep_off reports a standalone server.
The desynced arm records the first-seen epoch, keeping any earlier value, then checks the age against grace_minutes. Inside the window it says so plainly. Past it, finding logs the statement that would fix the node, alert_finding posts to Slack.
remediate_gate needs auto_remediate true with dry_run false. Then approve_resync pauses for a human.
On approval clear_desync runs SET GLOBAL wsrep_desync = OFF, settle waits resync_settle, verify_resync reads the wsrep state again.
assert_resync passes only when wsrep_desync is 0, wsrep_local_state is 4, the state comment is Synced and wsrep_ready is ON. On success it drops the marker and logs the before and after numbers side by side. Otherwise resync_failed fails the run with both readings in the message.

What you get

A detector that catches the failure port checks miss, with the before and after wsrep numbers in the log.
A refusal that protects a live state transfer, built on a counter comparison rather than on a state string that cannot tell donation from an operator flag.
A grace window on a KV marker with no TTL, so a short desync during normal operations never pages anyone.
A write that only ever happens behind two inputs plus a human answer.
A re-read that fails the run when the node did not come back, which is the difference between a guard that works and a guard that reports success.
Slack on detection as well as on failure, so the refusals are visible too.

Who it's for

Teams running MariaDB Galera who have been bitten by a node that looked up while the cluster was already down to one writable copy.
Platform engineers who take backups with mariabackup against a desynced node, where the flag gets left behind when a backup dies.
Anyone who needs the resync to be an approved action with an audit trail rather than an ad hoc SET GLOBAL from a shell.

Why orchestrate this with Kestra

The grace window needs state between runs. Kestra KV holds the first-seen epoch without a sidecar database.
The refusal, the approval gate, the write, the settle then the re-read are five different decisions in one place, with the Switch and the If tasks making the routing readable.
Pause gives a real human gate with the numbers already in the log, so whoever approves can see what state the node is in.
A Schedule trigger in safe mode turns the detector into a standing check. The remediation stays a decision someone makes.
Failures land in Slack through the errors block rather than in a cron mailbox nobody reads.

Tested end to end

Run against MariaDB 11.8.9 with Galera 26.4.27, a single bootstrapped node on Kestra 2.0.5. Every state was read back with the mariadb client, not from the Kestra log.

Synced node, wsrep_desync 0, WSREP_DESYNC_COUNT 0, wsrep_local_state 4. Bucket synced, no findings.
SET GLOBAL wsrep_desync=ON applied by hand. Client then read wsrep_desync 1, WSREP_DESYNC_COUNT 1, wsrep_local_state 2, state comment Donor/Desynced. Bucket desynced, detection fired past the grace window.
dry_run=true reported the statement it would run. Client confirmed wsrep_desync still 1.
auto_remediate=true with dry_run=false plus an approved Pause cleared the flag. Client read wsrep_desync 0, WSREP_DESYNC_COUNT 0, wsrep_local_state 4, Synced.
A declined Pause left wsrep_desync at 1.
A real second node joining the cluster made the first node a donor. While the transfer ran the client read wsrep_desync 0 with WSREP_DESYNC_COUNT 1, wsrep_local_state 2 and cluster size 2, so the count sat above the variable with the same state comment an operator desync produces. Bucket transfer, the write refused with auto_remediate true and dry_run false.
Killing the joiner left the first node in a non-primary component, wsrep_ready OFF, wsrep_local_state 0. Bucket nonprimary, the write refused.
A user with SELECT but no SUPER reproduced the refused write. The SET GLOBAL task went green, the re-read still saw wsrep_desync 1 and resync_failed failed the run, which also fired the errors Slack block.

Prerequisites

MariaDB 10.4 or later with Galera, reachable over JDBC.
Kestra 2.0 or later with the plugin-jdbc-mariadb plugin.

Secrets

MARIADB_PASSWORD: password for the MariaDB user. The password input defaults to it.
SLACK_WEBHOOK_URL: used by the three alert tasks plus the errors block.
In Kestra OSS, provide each one as a base64-encoded environment variable prefixed SECRET_.

Inputs

Input	Default	Purpose
`jdbc_url`	`jdbc:mariadb://mariadb-galera-node:3306/mysql`	One Galera node, not a load balancer
`username`	`root`	MariaDB user
`password`	`{{ secret('MARIADB_PASSWORD') }}`	Password for that user
`grace_minutes`	`10`	How long a desync may last before it is a finding
`resync_settle`	`PT10S`	Wait between the write then the re-read
`auto_remediate`	`false`	Must be true before anything is written
`dry_run`	`true`	Must be false before anything is written

Quick start

Add the MARIADB_PASSWORD plus SLACK_WEBHOOK_URL secrets.
Import the flow and point jdbc_url at one Galera node.
Run it with the defaults. Read the observed log line for the node's wsrep numbers.
Set grace_minutes to how long your cluster normally takes to finish a transfer.
When a node is genuinely stuck, run with auto_remediate: true and dry_run: false, then answer the Pause.
Enable the every_fifteen_minutes trigger, which always runs in safe mode.

How to extend

Point one execution per node at the cluster by running the flow from a parent flow with a Subflow task per jdbc_url, so each node keeps its own KV marker.
Add WSREP_FLOW_CONTROL_PAUSED to read_wsrep to catch a node that is Synced yet pausing the cluster, which is the opposite failure.
Swap the Slack tasks for io.kestra.plugin.pagerduty or io.kestra.plugin.notifications.opsgenie where a stuck node is pageable.
Raise resync_settle on a busy cluster where the catch up after a desync takes minutes.
Replace the Pause with a LoopUntil on wsrep_local_state where you want the resync unattended on a node you know has no joiner.

Detect a desynced MariaDB Galera node and clear wsrep_desync behind an approval

Findings

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?

Detect a desynced MariaDB Galera node and clear wsrep_desync behind an approval

Findings

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 Infrastructure Blueprints

Environmental Disaster Early Warning System

Audit Every Agent Tool Call Against Policy Before It Goes Unnoticed

Apache ActiveMQ Artemis Dead Letter Address (DLA) Message Accumulation Sentinel

AI Incident Post-Mortem and Root Cause Generator

ACM Certificate Expiry Compliance Sentinel

Automatically Review GitHub Pull Requests with OpenAI GPT-4o and Post Comments

More Data Blueprints

Gate MLflow model promotion with statistical drift detection

ML Training Dataset Security Guard and Poisoning Prevention Gate

MongoDB Dead-Letter Mutation Reconciliation and Bulk Repair Gate

MongoDB Dangling Reference Orphan Cleanup Guard

Gate a classification model on subgroup error rates before it ships

MongoDB Document Validation Drift Repair Guard

New to Kestra?