mariadb check table / galera locking

mariadb check table / galera locking

  • Written by
    Walter Doekes
  • Published on

After upgrading some database nodes from MariaDB 10.3 to 10.6 we encountered some issues with tables not being fully correct. We'd like to CHECK (and maybe REPAIR) TABLE the entire database. But the database must not block queries on other nodes of the cluster.

The corruption we saw might have been a small corruption that had crept in during even earlier upgrades, we don't know. But we do know that we'd like to get this sorted before the corruption gets worse after further upgrades.

The requirement that the “show must go on” is obvious. Because we always have three nodes in a cluster, we can safely take one out and do table checks and repairs on a single one.

But, it has to be back in the cluster in time to still get an IST. If it is so far out of sync that it has to do an SST, the whole (repair) exercise is pointless. (Okay — not entirely pointless — we might see the corruption, but we won't have fixed it.)

The following helper script makes repairing/checking easier by running a single CHECK TABLE and then waiting for the node to catch up before doing the next table.

The special spice in this script is wsrep_desync=ON. Without it the node would still accept wsrep updates and block. A DDL statement like ALTER TABLE does not require it to desync itself, but CHECK TABLE does.

With a small adaptation, you could use this script to ALTER TABLE FORCE instead. A lengthier operation that allows you to reclaim space as well. This optional change is left to the reader.

# 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;' \

# 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)
#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

Run this for each MariaDB node in your Galera cluster, one at a time, while they are not taking client connections. And sleep better, knowing that all corruption is gone.

Back to overview Newer post: mysql binlog replay / max_allowed_packet Older post: systemd-networkd-wait-online / stalling and failing