Repair Mikrotik Dude database

If you run a Mikrotik Dude instance on CHR, have a lot of devices and years of activity, problems start to arise with errors like:

server status: db failure: disk image is malformed. stop
server status: db failure: database disk image is malformed. stop

The problem might temporarily be resolved by running

#!/bin/bash
dudehost="dude.your.hostname"
port="22"
dudeuser="backup"

ssh -p "$port" "$dudeuser"@"$dudehost" "/dude set enabled=no"
ssh -p "$port" "$dudeuser"@"$dudehost" "/dude vacuum-db"
ssh -p "$port" "$dudeuser"@"$dudehost" "/dude set enabled=no"

But it will crop up again. The solution on the old Mikrotik Wiki is NOT VALID and only works on older versions.

The following script is a smashed together version of a few small scripts, which will address the problem by removing old history and outages:

#!/bin/bash

# 2024-04-24 Bretton <[email protected]>
# This script will download a dude database, clean the history
# and reupload a trimmed version.
#
# Disclaimer
# This is hacked together from several scripts and untested in current form
#
# NB: Disks
# dude.db might be in 'disk1/dude-data' or 'sata1-part1/dude-data'

# dude host and credentials
dudehost="dude.your.hostname"
port="22"
dudeuser="backup"

date=$(date +%Y%m%d)

# Path to the original database
original_db="baddude.db"
# Path to the new database (copy)
new_db="newdude.db"

if [ -f "$original_db" ]; then
    rm -rf "$original_db"
fi

# stop dude
ssh -p "$port" "$dudeuser"@"$dudehost" "/dude set enabled=no"

# download the dude.db file locally
scp -P "$port" "$dudeuser"@"$dudehost":sata1-part1/dude-data/dude.db "$original_db"

# Copy the original database to create a new one
cp "$original_db" "$new_db"

# Execute SQLite commands on the new database
sqlite3 "$new_db" <<EOF
DELETE FROM outages;
DELETE FROM chart_values_raw;
DELETE FROM chart_values_10min;
DELETE FROM chart_values_2hour;
DELETE FROM chart_values_1day;
PRAGMA integrity_check;
VACUUM;
REINDEX;
.quit
EOF

echo "Database operations completed on $new_db"

if [ -f "$new_db" ]; then

    # delete dude files
    ssh -p "$port" "$dudeuser"@"$dudehost" "/file remove sata1-part1/dude-data/dude.db"
    ssh -p "$port" "$dudeuser"@"$dudehost" "/file remove sata1-part1/dude-data/dude.db-shm"
    ssh -p "$port" "$dudeuser"@"$dudehost" "/file remove sata1-part1/dude-data/dude.db-wal"

    # copy file to dude
    scp -P "$port" "$new_db" "$dudeuser"@"$dudehost":sata1-part1/dude-data/dude.db

    # start dude
    ssh -p "$port" "$dudeuser"@"$dudehost" "/dude set enabled=yes"

    echo "dude restored $date"
else
    echo "Error: There is no file to restore to dude."
fi

bretton/repair-dude.md