mariadb check table / galera locking

#!/bin/sh
#
# This script does a CHECK TABLE on all tables of a MariaDB server,
# but it waits for the node to catch up (Sync) after each table.
#
# When a MariaDB server is part of a Galera cluster, it is allowed
# to go out-of-sync -- assuming you ensure that regular clients are
# disallowed access during this time.
#
# But if you run a CHECK TABLE, it LOCKs by default, instead of going
# out-of-sync. The wsrep_OSU_method=RSU + wsrep_desync=ON settings
# here make sure that the other nodes in the Galera cluster can
# continue operating.
#
# Usage:
#
#   mariadb-galera-check-all-tables 2>&1 | tee output.log
#
# It will write a mariadb-galera-check-all-tables.table_cache file,
# iterate over it, CHECK TABLE for each table, and wait for it to
# go back to synced state.
#
# Afterwards you can check the log for issues found and repairs done.
#
# (ossobv/2024-04-17)

# Statements to allow us to go out of sync.
BEGIN_WORK='SET SESSION wsrep_OSU_method=RSU; SET GLOBAL wsrep_desync=ON'
END_WORK='SET SESSION wsrep_OSU_method=TOI; SET GLOBAL wsrep_desync=OFF'

sql() {
    # If needed, you can add appropriate auth flags here.
    mysql -NBe "$1"
}

when() {
    date --rfc-3339=seconds
}

# Get tables into a file, use get_tables() to get them.
make_tables() {
    # Repairing appears to work fine on:
    # - InnoDB, MyISAM, Aria, CSV
    # Not fine on:
    # - PERFORMANCE_SCHEMA (storage engine doesn't support check)
    sql '
        SELECT CONCAT("`", table_schema, "`.`", table_name, "`") as tbl
        FROM information_schema.tables
        WHERE table_type = '\''BASE TABLE'\''
          AND engine NOT IN ('\''PERFORMANCE_SCHEMA'\'')
        ORDER BY table_schema, table_name;' \
      >"$0.table_cache"
}

# Use intermediate function, in case you want to alter the tables
# or manually set them.
get_tables() {
    cat "$0.table_cache"
}

check_table() {
    local table="$1"
    echo -n "$(when): CHECK TABLE $table ... "
    # Do a CHECK TABLE + ANALYZE TABLE for good measure.
    local work="CHECK TABLE $table; ANALYZE TABLE $table"
    # Wrapped in $() to collapse whitespace/linefeeds.
    echo $(sql "$BEGIN_WORK; $work; $END_WORK;")
}

# Check galera Write Set REPlication status;
# return OK when in Sync or ERROR if not.
galera_is_in_sync() {
    sql "SHOW STATUS LIKE 'wsrep_local_state_comment';" |
      grep -q '[[:blank:]]Synced$'
}


# Make tables (comment if you're editing the tables file)
make_tables
#exit  # uncomment to just produce the tables file

# Make sure the server can go back to Synced state
# (might be invoked twice, but it's idempotent)
trap 'sql "$END_WORK;"; echo; echo "$(when): $END_WORK"' EXIT INT

# Loop over tables from the cache file
get_tables | while read -r table; do
    check_table "$table" || break
    while ! galera_is_in_sync; do
        echo "$(when): (not in sync yet)"
        sleep 10 || break
    done
done