Recovering corrupted database tables

Guide for recovering corrupted database tables in MariaDB/MySQL. Methods are ordered from least to most invasive.

Quick Reference

Symptom	Likely Cause	Recommended Method
Failed to read from .par file	Metadata corruption	Method 2: Transportable Tablespaces
REPAIR TABLE fails	InnoDB metadata damage	Method 2: Transportable Tablespaces
MyISAM table errors	MyISAM corruption	Method 1: REPAIR TABLE
mysql.innodb_table_stats errors	Disk space exhaustion	Method 4: System Tables
.ibd file corrupted/missing	Severe data corruption	Method 3: Drop/Recreate

⚠️ Warning: Always backup your database before attempting recovery. Run mysqldump -u root -p voipmonitor > backup.sql first.

Method 1: REPAIR TABLE (MyISAM)

For MyISAM tables only (not common in VoIPmonitor):

REPAIR TABLE cdr;

ℹ️ Note: Most VoIPmonitor tables use InnoDB. Use Method 2 for InnoDB tables.

Method 2: Transportable Tablespaces (InnoDB)

Recommended for InnoDB when .par file is corrupted but .ibd data file is intact. Preserves all data.

Step-by-Step Procedure

1. Stop service and create recovery database:

systemctl stop voipmonitor

mysql -u root -p -e "CREATE DATABASE voipmonitorBackup;"

2. Configure VoIPmonitor to use new database temporarily:

# Edit /etc/voipmonitor.conf
mysqldb = voipmonitorBackup

3. Let VoIPmonitor create fresh schema:

systemctl start voipmonitor
sleep 60  # Wait for schema creation
systemctl stop voipmonitor

4. Discard tablespaces in new database:

USE voipmonitorBackup;

-- Discard main table and partitions
ALTER TABLE cdr DISCARD TABLESPACE;

-- For partitioned tables, discard each partition:
ALTER TABLE cdr DISCARD TABLESPACE FOR PARTITION p20250101;
-- Repeat for all partitions (check with SHOW CREATE TABLE cdr\G)

💡 Tip: Generate DISCARD commands automatically: SELECT CONCAT('ALTER TABLE cdr DISCARD TABLESPACE FOR PARTITION ', PARTITION_NAME, ';') FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='voipmonitorBackup' AND TABLE_NAME='cdr' AND PARTITION_NAME IS NOT NULL;

5. Copy .ibd files from old database:

# Copy data files
cp /var/lib/mysql/voipmonitor/cdr.ibd /var/lib/mysql/voipmonitorBackup/
cp /var/lib/mysql/voipmonitor/cdr#p#*.ibd /var/lib/mysql/voipmonitorBackup/

# Set ownership
chown mysql:mysql /var/lib/mysql/voipmonitorBackup/cdr*.ibd

6. Import tablespaces:

USE voipmonitorBackup;

ALTER TABLE cdr IMPORT TABLESPACE;
ALTER TABLE cdr IMPORT TABLESPACE FOR PARTITION p20250101;
-- Repeat for all partitions

7. Verify and finalize:

SELECT COUNT(*) FROM cdr;
SELECT MIN(calldate), MAX(calldate) FROM cdr;

Update /etc/voipmonitor.conf to use voipmonitorBackup permanently, then start service:

systemctl start voipmonitor

Method 3: Drop and Recreate (Destructive)

⚠️ Warning: Use ONLY when .ibd files are corrupted and you have a backup or data loss is acceptable.

systemctl stop voipmonitor

USE voipmonitor;
DROP TABLE IF EXISTS cdr;
-- Get fresh schema from GUI: Tools > System Status > Check MySQL Schema
CREATE TABLE `cdr` ( ... );

systemctl start voipmonitor

Method 4: System Table Corruption

Applies when MySQL logs errors about mysql.innodb_table_stats, mysql.column_stats, or similar system tables. Usually caused by disk space exhaustion.

Diagnosis

# Check disk space (common root cause)
df -h /var/lib/mysql

# Check MySQL error log
tail -100 /var/log/mysql/error.log

Quick Workaround

Add to /etc/mysql/my.cnf under [mysqld]:

innodb_stats_persistent = 0

systemctl restart mysql

Permanent Fix

Option A: Run mysql_upgrade (after OS/MySQL version upgrades):

mysql_upgrade -u root -p
systemctl restart mysql

Option B: Full database reinitialize (if Option A fails):

# 1. Backup everything
mysqldump -u root -p --all-databases > full_backup.sql

# 2. Stop and reinitialize
systemctl stop mysql
mv /var/lib/mysql /var/lib/mysql-corrupted
mysql_install_db --user=mysql  # or: mysqld --initialize --user=mysql

# 3. Start and restore
systemctl start mysql
mysql -u root -p < full_backup.sql

Severe Corruption Recovery (Last Resort)

Use this procedure when MySQL is too corrupted to start normally and you cannot run mysqldump (Option B above fails). This typically occurs after disk space exhaustion.

⚠️ Warning: This is a destructive procedure - all CDR data will be lost. Only GUI configuration is preserved.

Step 1: Force MySQL to Start

Add to /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf under [mysqld]:

innodb_force_recovery = 6

systemctl start mysql

Step 2: Backup GUI Configuration

This preserves users, sensors, alerts, dashboards. See full backup guide.

cd /var/www/html
php php/run.php backupGuiTables -t config -f /root/GUIbackup.zip

Step 3: Stop MySQL and Remove Corrupted Data

systemctl stop mysql
rm -rf /var/lib/mysql/

Step 4: Re-initialize MySQL Data Directory

# For MySQL 5.7 / MariaDB:
mysql_install_db --user=mysql

# For MySQL 8.0+:
mysqld --initialize --user=mysql

Step 5: Start MySQL and Restore GUI Configuration

systemctl start mysql

cd /var/www/html
php php/run.php restoreGuiTables -t config -f /root/GUIbackup.zip

Step 6: Remove Recovery Mode

Remove innodb_force_recovery line from config file:

# Edit /etc/mysql/my.cnf - remove innodb_force_recovery line
systemctl restart mysql

Step 7: Prevent Recurrence

Disk space exhaustion is often caused by misconfigured maxpoolsize. Set a value that fits within available partition space:

# /etc/voipmonitor.conf - value in MB
# For 500GB partition, use ~450GB:
maxpoolsize = 460000

ℹ️ Note: See Data Cleaning for complete retention configuration and High-Performance MySQL Setup for database tuning.

Troubleshooting

Error	Solution
Schema mismatch during IMPORT	Check journalctl -u voipmonitor for ALTER TABLE commands and run them manually
Cannot DISCARD TABLESPACE	Verify InnoDB engine: SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='cdr'; Try: SET FOREIGN_KEY_CHECKS=0; before DISCARD
DROP TABLE hangs	Stop MariaDB, delete files manually from /var/lib/mysql/voipmonitor/, restart

Prevention

• Regular backups: mysqldump -u root -p voipmonitor | gzip > backup_$(date +%F).sql.gz
• Monitor disk space: Alert when MySQL partition > 80% full
• Clean shutdown: Always use systemctl stop before maintenance
• UPS: Prevent sudden power loss
• Replication: See Redundant Database

Automated Backup Example

#!/bin/bash
# /usr/local/bin/backup_voipmonitor_db.sh
BACKUP_DIR="/var/backups/voipmonitor"
mkdir -p $BACKUP_DIR
mysqldump -u root -p"$MYSQL_PASSWORD" voipmonitor | gzip > "$BACKUP_DIR/voipmonitor_$(date +%F).sql.gz"
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

Cron: 0 2 * * * /usr/local/bin/backup_voipmonitor_db.sh

See Also

• Database Structure Reference
• Database Troubleshooting
• Redundant Database
• Emergency Procedures

AI Summary for RAG

Summary: Database table corruption recovery guide for VoIPmonitor MySQL/MariaDB. Four methods by invasiveness: (1) REPAIR TABLE for MyISAM only; (2) Transportable Tablespaces (RECOMMENDED for InnoDB .par corruption) - create fresh database, let VoIPmonitor generate schema, DISCARD tablespace, copy .ibd files, IMPORT tablespace; (3) Drop/recreate (destructive, last resort); (4) System table corruption (mysql.innodb_table_stats) - usually caused by disk space exhaustion, workaround with innodb_stats_persistent=0 or run mysql_upgrade. Key troubleshooting: schema mismatch requires manual ALTER TABLE from syslog; DISCARD fails check InnoDB engine and FOREIGN_KEY_CHECKS. Prevention: regular mysqldump, disk monitoring, proper shutdown, UPS, replication.

Keywords: database corruption, repair table, .par file, .ibd file, transportable tablespaces, discard tablespace, import tablespace, InnoDB recovery, MariaDB corruption, MySQL corruption, data recovery, cdr table, mysqldump, innodb_table_stats, innodb_stats_persistent, system tables, disk space, mysql_upgrade

Key Questions:

• How do I fix "Failed to read from .par file" error?
• How do I recover data from corrupted InnoDB table using transportable tablespaces?
• How do I fix mysql.innodb_table_stats missing or corrupted?
• What causes MySQL system table corruption?
• When should I use mysql_upgrade vs transportable tablespaces?
• How do I prevent database corruption in VoIPmonitor?
• What backup strategy should I use for VoIPmonitor database?