Recovering corrupted database tables

This guide explains how to recover from corrupted database tables in MariaDB/MySQL, specifically when you encounter errors like "Failed to read from the .par file" and standard REPAIR TABLE commands fail.

The methods below are ordered from least invasive to most invasive, with data preservation as the primary goal.

Overview of Table Corruption Symptoms

Common symptoms of table corruption include:

• Query errors referencing specific file types: Failed to read from the .par file
• REPAIR TABLE command fails or hangs
• Queries returning incorrect or no data for known records
• Error messages indicating corrupted indexes or table structures

InnoDB table corruption typically affects metadata files (.frm, .par) rather than the actual data files (.ibd), which often allows data recovery using transportable tablespaces.

⚠️ Warning: Data Loss Warning: Always backup your database before attempting any recovery procedures. The methods below attempt to preserve data, but errors during recovery can potentially cause data loss.

Method 1: Standard REPAIR TABLE (For MyISAM Tables)

If the affected table uses the MyISAM storage engine, the REPAIR TABLE command may resolve the corruption:

REPAIR TABLE cdr;

This method works for MyISAM tables but typically fails for InnoDB tables with metadata corruption.

Method 2: Transportable Tablespaces for InnoDB (Recommended for .par File Corruption)

This is the recommended method for InnoDB tables when the .par file is corrupted or empty but the .ibd data file is intact. This method preserves all existing data.

When to Use This Method

• Error: Failed to read from the .par file
• REPAIR TABLE fails
• The table uses InnoDB storage engine (most VoIPmonitor tables do)
• .ibd file exists in the MySQL data directory

Step 1: Stop VoIPmonitor Service

Before manipulating the database, stop the sensor to prevent write conflicts:

systemctl stop voipmonitor

Step 2: Create a New Empty Database

Create a fresh database that will serve as the recovery target:

CREATE DATABASE voipmonitorBackup;

Step 3: Point to the New Database in Configuration

Modify /etc/voipmonitor.conf to temporarily use the new database:

mysqldb = voipmonitorBackup

Step 4: Start VoIPmonitor to Create Fresh Schema

Start the service to let it create the latest table structure, including partition definitions:

systemctl start voipmonitor

Wait for the service to fully initialize and create all tables and partitions. This typically takes 30-60 seconds.

Verify the tables were created correctly:

USE voipmonitorBackup;
SHOW TABLES LIKE 'cdr%';

Check the partition structure:

SHOW CREATE TABLE cdr\G

Step 5: Stop VoIPmonitor Service

Once schema creation is complete, stop the service again:

systemctl stop voipmonitor

Step 6: Discard Tablespaces in the New Database

For each table and partition you want to recover, discard the empty tablespace so we can import the old data. For the cdr table with partitions:

USE voipmonitorBackup;

-- Discard main table
ALTER TABLE cdr DISCARD TABLESPACE;

-- Discard all partitions
ALTER TABLE cdr DISCARD TABLESPACE FOR PARTITION p20250101;
ALTER TABLE cdr DISCARD TABLESPACE FOR PARTITION p20250102;

-- Repeat for all partitions (use SHOW CREATE TABLE to see the list)

You can generate the commands automatically for all partitions:

-- Generate ALTER statements to discard all cdr partitions
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;

Step 7: Copy Old .ibd Files to New Database Location

Locate the MySQL data directory. Common locations:

/var/lib/mysql/ on Debian/Ubuntu /var/lib/mysql/ with a database subdirectory for each database

Copy the .ibd files from the old corrupted database to the new database directory:

# Example: Copy cdr.ibd and partition 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 correct ownership
chown mysql:mysql /var/lib/mysql/voipmonitorBackup/cdr*.ibd
chmod 660 /var/lib/mysql/voipmonitorBackup/cdr*.ibd

💡 Tip: Partition file naming: InnoDB partition files typically follow the pattern table#p#partition_name.ibd. Use ls -la /var/lib/mysql/voipmonitor/ to see the exact file names.

Step 8: Import Tablespaces in the New Database

Now import the tablespaces with the old data:

USE voipmonitorBackup;

-- Import main table
ALTER TABLE cdr IMPORT TABLESPACE;

-- Import all partitions (matches the DISCARD commands)
ALTER TABLE cdr IMPORT TABLESPACE FOR PARTITION p20250101;
ALTER TABLE cdr IMPORT TABLESPACE FOR PARTITION p20250102;

-- Continue for all partitions

Repeat the import commands for all tables and partitions you recovered.

Step 9: Check for Schema Mismatches

Check the syslog for any ALTER query warnings from VoIPmonitor:

journalctl -u voipmonitor | grep -i "ALTER\|schema"

If VoIPmonitor logged schema differences, it means the old table structure differs from the current version. You may need to manually run the ALTER TABLE commands shown in the logs to ensure compatibility.

Step 10: Verify Data Integrity

Verify the data was imported correctly:

-- Check if data exists
SELECT COUNT(*) FROM cdr;
SELECT MIN(calldate), MAX(calldate) FROM cdr;

-- Sample random records
SELECT * FROM cdr ORDER BY RAND() LIMIT 10;

Step 11: Switch Back to Production Database

Restore the original database configuration in /etc/voipmonitor.conf:

mysqldb = voipmonitor

Then start VoIPmonitor:

systemctl start voipmonitor

If the migration was successful, you now have:

• The original (corrupted) voipmonitor database (still corrupted, backup)
• The recovered voipmonitorBackup database (working, with data)

You can either: 1. Update voipmonitor.conf to use voipmonitorBackup permanently, OR 2. Drop and recreate the voipmonitor database, then use mysqldump to copy data from voipmonitorBackup

ℹ️ Note: Recommended approach: Use voipmonitorBackup as the production database by updating mysqldb = voipmonitorBackup in /etc/voipmonitor.conf. The corrupted old database can be kept as a reference or deleted after confirming recovery is successful.

Method 3: Drop and Recreate Table (Destructive - Last Resort)

Use this method ONLY when:

• The .ibd files are also corrupted (not just metadata)
• You have a recent SQL dump backup
• Data loss is acceptable

This method will delete all data in the table.

Step 1: Stop VoIPmonitor

systemctl stop voipmonitor

Step 2: Get Fresh Schema

Since the table is corrupted, SHOW CREATE TABLE may fail. Obtain the schema from:

• A fresh VoIPmonitor installation of the same version
• The GUI: Tools → System Status → Check MySQL Schema
• VoIPmonitor installation documentation

Step 3: Drop Corrupted Table

USE voipmonitor;
DROP TABLE IF EXISTS cdr;

⚠️ Warning: Severe Corruption: If DROP hangs, stop MariaDB, manually delete files from /var/lib/mysql/voipmonitor/ (like cdr.frm, cdr.ibd, cdr.par), then restart MariaDB.

Step 4: Recreate Table with Fresh Schema

-- Paste the CREATE TABLE statement obtained in Step 2
CREATE TABLE `cdr` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `calldate` datetime NOT NULL,
  -- ... rest of schema from the GUI or fresh installation ...
  PRIMARY KEY (`ID`),
  KEY `calldate` (`calldate`),
  -- ... remaining indexes ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Step 5: Restart VoIPmonitor

systemctl start voipmonitor

Troubleshooting

Import Fails with Table Definition Mismatch

Error: Tablespace storage engine version 123 is different from what the table expects

This means the schema differs between the old table and the new table. Solutions:

1. Use mysqldump backup: If you have a recent SQL dump, restore it instead 2. Manual schema upgrade: Run the ALTER TABLE commands that VoIPmonitor logged in syslog 3. Recreate with matching schema: Use the exact same schema version as the original table

Cannot DISCARD TABLESPACE

Error: ERROR 1478 (HY000): InnoDB: Tablespace not discarded

Possible causes:

• Table is not InnoDB storage engine
• Table has no tablespace (file-per-table not enabled)
• Foreign key constraints prevent discard

Check storage engine:

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'voipmonitorBackup' AND TABLE_NAME = 'cdr';

For foreign key issues, temporarily disable foreign key checks:

SET FOREIGN_KEY_CHECKS=0;
-- Run DISCARD/IMPORT commands
SET FOREIGN_KEY_CHECKS=1;

Table Missing After Recovery

If the table disappears after recovery steps:

1. Check if the table was imported with a different name 2. Verify you are in the correct database (USE voipmonitorBackup) 3. Check MySQL error logs for failed import messages

SHOW TABLES;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%cdr%';

Prevention

To prevent table corruption in the future:

⚠️ Warning: Important: These recommendations improve reliability but do not guarantee protection against all corruption scenarios. Always maintain regular backups.

• Regular Backups: Use mysqldump to create regular backups. Consider automating with cron
• Monitor Disk Space: Avoid running MySQL/MariaDB with disk space near 100%
• Proper Shutdown: Always stop services cleanly with systemctl stop before maintenance
• Replication: Consider master-slave replication for redundancy. See Master-Slave Replication Hints
• Hardware Health: Monitor disk errors with smartctl and replace failing drives
• UPS/Battery Backup: Use uninterruptible power supplies to prevent sudden power loss

Backup and Restore Examples

Creating Full Database Backup

# Backup to file
mysqldump -u root -p voipmonitor > voipmonitor_backup_$(date +%Y%m%d).sql

# Compressed backup
mysqldump -u root -p voipmonitor | gzip > voipmonitor_backup_$(date +%Y%m%d).sql.gz

Restoring from Backup

# Restore from SQL file
mysql -u root -p voipmonitor < voipmonitor_backup_20250101.sql

# Restore from gzipped backup
gunzip < voipmonitor_backup_20250101.sql.gz | mysql -u root -p voipmonitor

Automated Example Backup Script

#!/bin/bash
# /usr/local/bin/backup_voipmonitor_db.sh

BACKUP_DIR="/var/backups/voipmonitor"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="root"

mkdir -p $BACKUP_DIR

mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD voipmonitor | gzip > $BACKUP_DIR/voipmonitor_$DATE.sql.gz

# Keep only last 7 days
find $BACKUP_DIR -name "voipmonitor_*.sql.gz" -mtime +7 -delete

echo "Backup completed: voipmonitor_$DATE.sql.gz"

Add to crontab:

# Daily backup at 2:00 AM
0 2 * * * /usr/local/bin/backup_voipmonitor_db.sh >> /var/log/voipmonitor_backup.log 2>&1

Method 4: MySQL System Table Corruption (mysql.innodb_table_stats, mysql.column_stats)

If MySQL error logs report missing or corrupted system tables like mysql.innodb_table_stats, mysql.column_stats, or mysql.innodb_index_stats, InnoDB will fall back to transient statistics. This is often caused by running out of disk space on the MySQL data partition.

⚠️ Warning: System tables (mysql.* schema) are managed by the database server. Do not manually drop or recreate them.

Diagnosis

Check MySQL error logs for specific error messages:

# Check MySQL error log for system table errors
tail -100 /var/log/mysql/error.log
# Or for MariaDB:
tail -100 /var/log/mariadb/error.log

Look for errors like:

• Table 'mysql.innodb_table_stats' doesn't exist
• Can't open file './mysql/innodb_table_stats.ibd'
• Unable to open table mysql.column_stats

Step 1: Check Disk Space (Root Cause)

System table corruption is often caused by running out of disk space on the MySQL data directory partition. Check disk usage:

# Check disk space on MySQL data directory
df -h /var/lib/mysql

# Show mount point and available space
df -h /

If the partition containing MySQL data is at or near 100% capacity:

# Create free space immediately (delete old logs, caches, etc.)
# Examples:
sudo journalctl --vacuum-time=7d
sudo apt clean  # or 'dnf clean' on RHEL/CentOS

The root cause (lack of disk space) must be addressed first, or the corruption will recur.

Step 2: Workaround - Disable Persistent Statistics

If you cannot immediately perform a full database migration, use a configuration workaround to bypass the corrupted system tables:

Edit MySQL configuration:

# Debian/Ubuntu
sudo nano /etc/mysql/my.cnf
# Or: sudo nano /etc/mysql/mysql.conf.d/50-server.cnf

# CentOS/RHEL
sudo nano /etc/my.cnf

Add or modify in the [mysqld] section:

[mysqld]
# Disable persistent statistics (use transient statistics instead)
innodb_stats_persistent = 0

Restart MySQL/MariaDB:

# Debian/Ubuntu
sudo systemctl restart mysql
# Or: sudo systemctl restart mariadb

# CentOS/RHEL
sudo systemctl restart mysqld

This allows MySQL to operate without the corrupted system tables by using transient statistics. InnoDB will recalculate statistics on the fly instead of storing them persistently.

ℹ️ Note: This is a workaround, not a permanent fix. Persistent statistics provide better query performance and stability. Implement Step 3 for a comprehensive solution.

Step 3: Permanent Fix - Database Migration

For a clean, permanent solution, migrate the database to a new instance with sufficient disk space:

Option A: New Server Instance

Provision a new server/database instance with adequate disk space and migrate VoIPmonitor to it. This ensures:

• Clean system tables
• Sufficient disk capacity
• No data loss (migrate both MySQL data and PCAP files)

Option B: mysqldump and Restore

If migrating to a new instance is not feasible, perform a full database dump and restore:

# 1. Create a backup
mysqldump -u root -p --all-databases > mysql_backup_$(date +%Y%m%d).sql

# 2. Ensure there is free space on the partition
# (delete old backups, logs, etc. as needed)

# 3. Stop MySQL
sudo systemctl stop mysql
# Or: sudo systemctl stop mariadb

# 4. Move the corrupted data directory to backup
sudo mv /var/lib/mysql /var/lib/mysql-corrupted-$(date +%Y%m%d)

# 5. Reinitialize MySQL data directory
sudo mysql_install_db --user=mysql
# On newer systems:
sudo mysqld --initialize --user=mysql

# 6. Start MySQL in recovery/rebuild mode
sudo systemctl start mysql

# 7. Set root password (if using --initialize)
# Find temporary password in logs:
grep "temporary password" /var/log/mysql/error.log
# Then log in and set new password:
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';
FLUSH PRIVILEGES;
EXIT;

# 8. Restore your databases
mysql -u root -p < mysql_backup_YYYYMMDD.sql

# 9. Verify system tables are intact
mysql -u root -p -e "SELECT * FROM mysql.innodb_table_stats LIMIT 5;"

Verification

After applying any of the above fixes, verify that MySQL is using persistent statistics correctly:

# Check MySQL error logs - should no longer show system table errors
tail -50 /var/log/mysql/error.log

# Verify persistent statistics setting
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_stats_persistent';"

# Verify system tables are accessible
mysql -u root -p -e "SELECT COUNT(*) FROM mysql.innodb_table_stats;"
mysql -u root -p -e "SELECT COUNT(*) FROM mysql.column_stats;"

Scenario-Specific Solutions

After OS Upgrade (Version Mismatch)

If system table corruption occurred after an OS upgrade or MySQL/MariaDB version upgrade, the tables may be missing because they were deleted during the upgrade process and not recreated:

# Run MySQL/MariaDB upgrade to recreate system tables
mysql_upgrade -u root -p

# For newer MariaDB versions:
mariadb-upgrade -u root -p

# Restart database
sudo systemctl restart mysql
# Or: sudo systemctl restart mariadb

Use this method when:

• Corruption occurred immediately after OS upgrade (Debian 11→13, etc.)
• Error messages mention incorrect table definitions
• System shows upgraded MySQL version with old system table structures

💡 Tip: See MariaDB System Table Corruption After OS Upgrade for detailed OS upgrade scenario guidance.

Prevention

To prevent MySQL system table corruption in the future:

• **Monitor disk space** on the MySQL data partition daily
• Set up alerts for disk usage > 80%
• Configure automatic log rotation and cleanup
• Ensure the MySQL data partition has at least 20% free space
• For migration scenarios, always plan for sufficient disk capacity
• Keep system backups before major upgrades

# Add to crontab for disk monitoring
# Warn if /var/lib/mysql usage > 80%
0 * * * * df /var/lib/mysql | awk '{if ($5+0 > 80) print "WARNING: MySQL disk at " $5}' | mail -s "MySQL disk warning" admin@example.com

Related Documentation

• Database Structure Reference - Overview of VoIPmonitor database tables
• Master-Slave Replication Hints - Setting up database replication for redundancy
• Redundant Database - Real-time replication to secondary server
• Emergency Procedures - Recovering from system failures

AI Summary for RAG

Summary: Database table corruption recovery guide for VoIPmonitor. Four methods ordered by invasiveness: (1) REPAIR TABLE for MyISAM tables only; (2) Transportable Tablespaces (RECOMMENDED for InnoDB .par file corruption) - creates fresh database, lets VoIPmonitor generate schema, then DISCARD/copy .ibd/IMPORT to recover data without loss; (3) Drop and recreate (DESTRUCTIVE, last resort when .ibd files are also corrupted); (4) System table corruption (mysql.innodb_table_stats, mysql.column_stats) - often caused by disk space exhaustion, workaround with innodb_stats_persistent=0 or full database migration. Common symptoms: "Failed to read from the .par file", REPAIR TABLE fails, query errors, system table errors after disk full. Key troubleshooting: schema mismatch during import requires running ALTER TABLE commands from syslog; Cannot DISCARD = check InnoDB engine and set FOREIGN_KEY_CHECKS=0. Prevention: regular mysqldump backups, disk monitoring, proper shutdown, replication, UPS.

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

Key Questions:

• How do I fix a corrupted cdr table with .par file errors?
• How do I recover data from corrupted InnoDB table using DISCARD/IMPORT?
• What is the transportable tablespaces method for database recovery?
• How do I fix mysql.innodb_table_stats missing or corrupted?
• How do I fix mysql database system tables after running out of disk space?
• What is innodb_stats_persistent workaround for corrupted system tables?
• When to use mysql_upgrade vs transportable tablespaces?
• How to migrate VoIPmonitor database to new instance to fix system tables?
• How do I prevent database corruption in VoIPmonitor?