Recovering corrupted database tables

From VoIPmonitor.org


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

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

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?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=Recovering_corrupted_database_tables&oldid=10057"