#!/bin/sh # gn-indexer-lag โ€” the lag SLA check, on the DB clock (no host clock). # # Reads `versioning_marker` from .sidecar.json, then one mysql # round-trip with the two design ยง3 quantities: # lag_seconds = TIMESTAMPDIFF(SECOND, marker, NOW(6)) # newest_change = MAX(ROW_START) over the seven versioned tables # # Alert (exit 1 + ERROR) only when lag_seconds > N # (GN4_INDEXER_LAG_SLA_MINUTES, default 120 = worst-case 99 + tick 10 + # slack 10), or when `newest_change > marker` has PERSISTED past N # (a single fresh gap is ordinary live write load and never pages). # # usage: gn-indexer-lag [--db DB] # * mysql client: $GN4_INDEXER_LAG_MYSQL (else mysql -u USER -h HOST) # * user/host: GN4_INDEXER_LAG_USER (webqtlout) / _HOST (127.0.0.1) # * database: --db DB or GN4_INDEXER_LAG_DB # * table prefix: GN4_INDEXER_DELTA_TABLE_PREFIX (honoured only with # GN4_INDEXER_DELTA_TEST=1 โ€” the test seam test seam) # * state file: $GN4_INDEXER_LAG_STATE else .lag.state set -u usage() { echo "usage: gn-indexer-lag [--db DB]" >&2 exit 2 } [ "$#" -ge 1 ] || usage index=$1 shift db=${GN4_INDEXER_LAG_DB:-} while [ "$#" -ge 1 ]; do case "$1" in --db) db=${2:-} ; shift 2 ;; *) usage ;; esac done sla_minutes=${GN4_INDEXER_LAG_SLA_MINUTES:-120} sla_seconds=$(( sla_minutes * 60 )) state=${GN4_INDEXER_LAG_STATE:-${index}.lag.state} sidecar=${index}.sidecar.json log() { echo "$(date -u +%Y-%m-%dT%H:%M:%SZ) gn-indexer-lag: $*" } # advisory A3(a): the table-prefix test seam is honoured only with the test seam # second opt-in; otherwise it is ignored (and noted) so a stray env var in # release cannot redirect the lag query. prefix= if [ "${GN4_INDEXER_DELTA_TEST:-0}" = "1" ]; then prefix=${GN4_INDEXER_DELTA_TABLE_PREFIX:-} elif [ -n "${GN4_INDEXER_DELTA_TABLE_PREFIX:-}" ]; then log "WARNING: ${GN4_INDEXER_DELTA_TABLE_PREFIX} set but ignored (test seam requires GN4_INDEXER_DELTA_TEST=1)" fi mysql_cmd=${GN4_INDEXER_LAG_MYSQL:-"mysql -u ${GN4_INDEXER_LAG_USER:-webqtlout} -h ${GN4_INDEXER_LAG_HOST:-127.0.0.1} --batch --skip-column-names"} if [ -n "$db" ]; then mysql_q() { $mysql_cmd "$db" -e "$1"; } else mysql_q() { $mysql_cmd -e "$1"; } fi [ -f "$sidecar" ] || { log "ERROR: sidecar not found: $sidecar" exit 1 } marker=$(sed -n 's/.*"versioning_marker"[[:space:]]*:[[:space:]]*"\([^"]*\)".*/\1/p' "$sidecar" | head -n 1) if [ -z "$marker" ]; then log "INFO: no versioning marker in $sidecar (versioning absent or not yet adopted); lag SLA not applicable" exit 0 fi union= for t in PublishXRef PublishFreeze ProbeSetXRef ProbeSetFreeze ProbeSet InbredSet Species; do part="SELECT MAX(ROW_START) AS ROW_START FROM ${prefix}${t} FOR SYSTEM_TIME ALL" if [ -z "$union" ]; then union=$part else union="$union UNION ALL $part" fi done newest_change=$(mysql_q "SELECT MAX(ROW_START) FROM ( $union ) u" 2>/dev/null) || { log "ERROR: lag query failed (mysql client or privileges?)" exit 1 } # advisory A3(b): a literal NULL / empty MAX is "no change", not a gap. if [ "$newest_change" = "NULL" ]; then newest_change= fi lag_seconds=$(mysql_q "SELECT TIMESTAMPDIFF(SECOND, '$marker', NOW(6))" 2>/dev/null) || { log "ERROR: lag arithmetic query failed" exit 1 } # advisory A3(c): guard an empty / non-numeric lag before comparing. case "$lag_seconds" in ''|*[!0-9-]*) log "ERROR: lag_seconds not numeric ('${lag_seconds}')" exit 1 ;; esac log "marker=$marker newest_change=${newest_change:-NULL} lag_seconds=$lag_seconds sla_seconds=$sla_seconds" if [ "$lag_seconds" -gt "$sla_seconds" ]; then log "ERROR: lag ${lag_seconds}s > SLA ${sla_seconds}s" exit 1 fi # Freshness gap: versioned data newer than the marker. Page only when it # persists past the SLA โ€” track the first observation in the state file. if [ -n "$newest_change" ] && [ "$newest_change" \> "$marker" ]; then now=$(date -u +%s) first_seen= [ -f "$state" ] && first_seen=$(cat "$state" 2>/dev/null || true) if [ -z "$first_seen" ]; then printf '%s\n' "$now" >"$state" log "INFO: freshness gap (newest_change=$newest_change > marker=$marker) first observed; not paging yet" exit 0 fi gap_seconds=$(( now - first_seen )) if [ "$gap_seconds" -gt "$sla_seconds" ]; then log "ERROR: freshness gap persisted ${gap_seconds}s > SLA ${sla_seconds}s (newest_change=$newest_change > marker=$marker)" exit 1 fi log "INFO: freshness gap persisted ${gap_seconds}s < SLA ${sla_seconds}s" exit 0 fi rm -f "$state" log "INFO: within SLA" exit 0