Repairing and Optimizing a Crashed VICIdial MariaDB Database

Agents get logged out at random. The realtime screen freezes. Reports return partial data. Then you check the Asterisk log and find:

Table './asterisk/vicidial_live_agents' is marked as crashed and should be repaired

This is a MyISAM table crash, and on a busy dialer it is a matter of when, not if. Here is how to fix it and how to reduce the frequency.

Why It Happens

VICIdial’s high-churn tables — vicidial_live_agents, vicidial_auto_calls, vicidial_log, call_log — are traditionally MyISAM. MyISAM has no crash recovery. If the server loses power, the OOM killer takes mysqld, or the disk fills mid-write, the table index is left inconsistent and MariaDB marks it crashed.

The four causes, roughly in order of how often I see them:

1. Disk full, almost always from recordings or Asterisk logs
2. Unclean shutdown — power loss, init 6 on a loaded box, a hung VM host
3. Out-of-memory kill under agent load beyond what the server sizing supports
4. Filesystem-level corruption on failing storage

Step 1: Check Disk Space First

Before repairing anything, make sure you are not about to repair into a full disk.

df -h

If / is at 100%, the two usual suspects:

du -sh /var/spool/asterisk/monitorDONE/*
du -sh /var/log/asterisk/*

Free space before proceeding. Truncating the Asterisk log is safe:

> /var/log/asterisk/messages

Step 2: Identify the Crashed Tables

mysqlcheck -u cron -p1234 --check asterisk

Substitute your own credentials if you have hardened the install. Output lists each table with OK or an error. To see only the problems:

mysqlcheck -u cron -p1234 --check asterisk | grep -v "OK$"

Step 3: Repair

Start with the standard repair:

mysqlcheck -u cron -p1234 --auto-repair --databases asterisk

For a single table, from the MySQL prompt:

USE asterisk;
REPAIR TABLE vicidial_live_agents;

If a table reports “Number of rows changed from X to Y”, you lost rows. For the live-state tables that is fine — they are transient. For vicidial_log it means lost call records.

When Standard Repair Fails

If REPAIR TABLE returns errors, escalate:

REPAIR TABLE vicidial_log USE_FRM;

USE_FRM rebuilds the index from the table definition. It is more aggressive and should only be used if the normal repair failed.

The heaviest option is offline repair with myisamchk. Stop MariaDB first — running myisamchk against a live table will corrupt it further.

systemctl stop mariadb
cd /var/lib/mysql/asterisk
myisamchk -r -q vicidial_log.MYI
myisamchk --safe-recover vicidial_log.MYI
systemctl start mariadb

-r -q is a quick repair that only rebuilds the index. --safe-recover reads the data file row by row and is slower but recovers more.

If myisamchk complains about sort buffer size on a large table:

myisamchk -r --sort_buffer_size=256M --key_buffer_size=256M vicidial_log.MYI

Step 4: Optimize

After a repair, reclaim the fragmented space:

mysqlcheck -u cron -p1234 --optimize --databases asterisk

On a large vicidial_log this locks the table for the duration. Run it outside calling hours.

Step 5: Restart VICIdial’s Processes

The perl daemons hold connections and will not recover cleanly on their own:

/usr/share/astguiclient/ADMIN_keepalive_ALL.pl --restart

Then confirm agents can log in before you walk away.

The .TMM File Problem

A specific failure mode worth knowing: if a repair or optimize is interrupted, MariaDB can leave temporary .TMM files behind, and subsequent operations on that table will fail in strange ways.

ls -la /var/lib/mysql/asterisk/*.TMM

If any exist and no repair is currently running, stop MariaDB, remove them, and start again:

systemctl stop mariadb
rm -f /var/lib/mysql/asterisk/*.TMM
systemctl start mariadb

Prevention

Keep the Database Trimmed

VICIdial ships a cleanup script. Check it is actually in your crontab:

crontab -l | grep AST_DB_optimize

A reasonable nightly entry:

30 2 * * * /usr/share/astguiclient/AST_DB_optimize.pl

For log tables that have grown to millions of rows, VICIdial has archive functionality under Admin → Admin Utilities → Database Archive. Moving records older than 90 days into the archive tables makes an enormous difference to both crash frequency and report speed.

Monitor Disk

A simple cron guard is better than nothing:

#!/bin/bash
USAGE=$(df / | awk 'NR==2 {print $5}' | tr -d '%')
if [ "$USAGE" -gt 85 ]; then
    echo "Dialer disk at ${USAGE}%" | mail -s "Disk warning" ops@example.com
fi

Shut Down Cleanly

Before any planned reboot:

1. Pause and log out all agents
2. Stop the dialer processes: /usr/share/astguiclient/ADMIN_keepalive_ALL.pl --stop
3. systemctl stop asterisk
4. systemctl stop mariadb
5. shutdown -h now

Consider InnoDB — Carefully

InnoDB is crash-safe and would eliminate this class of problem entirely. It is tempting. Be aware that VICIdial’s high-write live tables were designed against MyISAM behaviour, and converting everything blindly has caused performance regressions for people on busy clusters. If you want to explore it, convert the low-churn reporting tables first, test under real load, and keep a rollback.

Backups

None of the above substitutes for a backup you have actually restored from.

mysqldump -u root -p --single-transaction --routines asterisk \
  | gzip > /backup/asterisk-$(date +%F).sql.gz

Nightly, off the box, and test a restore once a quarter.