Recovering corrupted database tables: Difference between revisions

Revision as of 11:26, 6 January 2026

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:

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

AI Summary for RAG

Summary: Database table corruption recovery guide. Symptoms: "Failed to read from the .par file", REPAIR TABLE fails, query errors. Method 1: REPAIR TABLE for MyISAM tables. Method 2: Transportable Tablespaces (RECOMMENDED for InnoDB .par file corruption). STEPS for Method 2: 1) Stop voipmonitor, 2) Create new empty database (voipmonitorBackup), 3) Update /etc/voipmonitor.conf: mysqldb=voipmonitorBackup, 4) Start voipmonitor to create fresh schema with partitions, 5) Stop voipmonitor, 6) Run ALTER TABLE ... DISCARD TABLESPACE for table and all partitions, 7) Copy .ibd files from old /var/lib/mysql/voipmonitor/ to new database directory, 8) Run ALTER TABLE ... IMPORT TABLESPACE for table and all partitions, 9) Check syslog for ALTER commands indicating schema differences, 10) Verify data (SELECT COUNT(*)), 11) Switch production database to voipmonitorBackup in voipmonitor.conf and restart. Method 3: Drop and recreate (DESTRUCTIVE, last resort). TROUBLESHOOTING: Import fails with schema mismatch = run ALTER TABLE commands from syslog or use mysqldump backup; Cannot DISCARD TABLESPACE = check engine is InnoDB, set FOREIGN_KEY_CHECKS=0; Table missing = check correct database and error logs. PREVENTION: Regular mysqldump backups, monitor disk space, proper shutdown, replication, hardware health monitoring, UPS. BACKUP COMMANDS: mysqldump -u root -p voipmonitor > backup.sql; mysqldump | gzip; mysql -u root -p < backup.sql; Automate with cron daily.

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, database corruption recovery, partitions, cdr table, mysqldump backup, database backup, master-slave replication, redundant database

Key Questions:

How do I fix a corrupted cdr table in MariaDB?
What do I do when REPAIR TABLE fails with "Failed to read from the .par file"?
How do I recover data from a corrupted InnoDB table?
What is the transportable tablespaces method for database recovery?
How do I use ALTER TABLE DISCARD TABLESPACE and IMPORT TABLESPACE?
How do I recover .par file corruption without losing data?
How do I copy .ibd files to recover database tables?
How do I recover a corrupted VoIPmonitor database?
How do I fix corrupted cdr partitions?
How do I recreate cdr table schema from existing data?
How do I switch to a backup database in voipmonitor?
What is the recommended method for recovering MariaDB table corruption?
How do I preserve data when recovering from database corruption?
How do I create fresh table structure for recovering corrupted data?
How do I check syslog for ALTER TABLE schema differences after recovery?
How do I backup and restore VoIPmonitor MySQL database?
How do I automate MySQL backups with cron?
How do I import tablespaces for MySQL partition tables?
What do I do if ALTER TABLE IMPORT TABLESPACE fails with schema mismatch?
How do I troubleshoot MySQL table corruption errors?