Recovering corrupted database tables: Difference between revisions

From VoIPmonitor.org
(Review: opravy formátování (hierarchie nadpisů), přidány kategorie, aktualizován AI Summary)
(Rewrite: consolidated from 617 to 237 lines, added quick reference table, streamlined procedures, improved structure)
 
Line 3: Line 3:
[[Category:Troubleshooting]]
[[Category:Troubleshooting]]


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.
Guide for recovering corrupted database tables in MariaDB/MySQL. Methods are ordered from least to most invasive.


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


== Overview of Table Corruption Symptoms ==
{| class="wikitable"
! Symptom !! Likely Cause !! Recommended Method
|-
| <code>Failed to read from .par file</code> || Metadata corruption || [[#Method 2: Transportable Tablespaces (InnoDB)|Method 2: Transportable Tablespaces]]
|-
| REPAIR TABLE fails || InnoDB metadata damage || [[#Method 2: Transportable Tablespaces (InnoDB)|Method 2: Transportable Tablespaces]]
|-
| MyISAM table errors || MyISAM corruption || [[#Method 1: REPAIR TABLE (MyISAM)|Method 1: REPAIR TABLE]]
|-
| <code>mysql.innodb_table_stats</code> errors || Disk space exhaustion || [[#Method 4: System Table Corruption|Method 4: System Tables]]
|-
| .ibd file corrupted/missing || Severe data corruption || [[#Method 3: Drop and Recreate (Destructive)|Method 3: Drop/Recreate]]
|}


Common symptoms of table corruption include:
{{Warning|1=Always backup your database before attempting recovery. Run <code>mysqldump -u root -p voipmonitor > backup.sql</code> first.}}


* Query errors referencing specific file types: <code>Failed to read from the .par file</code>
== Method 1: REPAIR TABLE (MyISAM) ==
* <code>REPAIR TABLE</code> 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.
For MyISAM tables only (not common in VoIPmonitor):
 
{{Warning|1=<strong>Data Loss Warning:</strong> 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 <code>REPAIR TABLE</code> command may resolve the corruption:


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
Line 28: Line 31:
</syntaxhighlight>
</syntaxhighlight>


This method works for MyISAM tables but typically fails for InnoDB tables with metadata corruption.
{{Note|1=Most VoIPmonitor tables use InnoDB. Use Method 2 for InnoDB tables.}}


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


This is the recommended method for InnoDB tables when the <code>.par</code> file is corrupted or empty but the <code>.ibd</code> data file is intact. This method preserves all existing data.
'''Recommended''' for InnoDB when <code>.par</code> file is corrupted but <code>.ibd</code> data file is intact. Preserves all data.


<kroki lang="mermaid">
<kroki lang="mermaid">
%%{init: {'flowchart': {'nodeSpacing': 10, 'rankSpacing': 25}}}%%
%%{init: {'flowchart': {'nodeSpacing': 10, 'rankSpacing': 25}}}%%
flowchart LR
flowchart LR
     subgraph SRC["Old DB"]
     A["1. Stop voipmonitor"] --> B["2. Create new DB"]
        A1["❌ .par corrupt"]
    B --> C["3. Start/stop voipmonitor<br/>(creates schema)"]
        A2["✓ .ibd intact"]
    C --> D["4. DISCARD tablespace"]
    end
    D --> E["5. Copy .ibd files"]
 
    E --> F["6. IMPORT tablespace"]
    subgraph STEPS["Recovery Steps"]
    F --> G["7. Verify & switch"]
        direction TB
        S1["1. Stop service"] --> S2["2. Create new DB"]
        S2 --> S3["3. Start sniffer"]
        S3 --> S4["4. Stop sniffer"]
        S4 --> S5["5. DISCARD"]
        S5 --> S6["6. Copy .ibd"]
        S6 --> S7["7. IMPORT"]
        S7 --> S8["8. Verify"]
    end
 
    subgraph DST["New DB"]
        B1["Fresh schema"]
        B2["Recovered data"]
    end
 
    A2 --> S6
    S8 --> B2
</kroki>
</kroki>


=== When to Use This Method ===
=== Step-by-Step Procedure ===
 
* Error: <code>Failed to read from the .par file</code>
* <code>REPAIR TABLE</code> fails
* The table uses InnoDB storage engine (most VoIPmonitor tables do)
* <code>.ibd</code> file exists in the MySQL data directory
 
=== Step 1: Stop VoIPmonitor Service ===
 
Before manipulating the database, stop the sensor to prevent write conflicts:


'''1. Stop service and create recovery database:'''
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
systemctl stop voipmonitor
systemctl stop voipmonitor
</syntaxhighlight>
=== Step 2: Create a New Empty Database ===


Create a fresh database that will serve as the recovery target:
mysql -u root -p -e "CREATE DATABASE voipmonitorBackup;"
 
<syntaxhighlight lang="sql">
CREATE DATABASE voipmonitorBackup;
</syntaxhighlight>
</syntaxhighlight>


=== Step 3: Point to the New Database in Configuration ===
'''2. Configure VoIPmonitor to use new database temporarily:'''
 
<syntaxhighlight lang="bash">
Modify <code>/etc/voipmonitor.conf</code> to temporarily use the new database:
# Edit /etc/voipmonitor.conf
 
<syntaxhighlight lang="ini">
mysqldb = voipmonitorBackup
mysqldb = voipmonitorBackup
</syntaxhighlight>
</syntaxhighlight>


=== Step 4: Start VoIPmonitor to Create Fresh Schema ===
'''3. Let VoIPmonitor create fresh schema:'''
 
Start the service to let it create the latest table structure, including partition definitions:
 
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
systemctl start voipmonitor
systemctl start voipmonitor
</syntaxhighlight>
sleep 60  # Wait for schema creation
 
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:
 
<syntaxhighlight lang="sql">
USE voipmonitorBackup;
SHOW TABLES LIKE 'cdr%';
</syntaxhighlight>
 
Check the partition structure:
 
<syntaxhighlight lang="sql">
SHOW CREATE TABLE cdr\G
</syntaxhighlight>
 
=== Step 5: Stop VoIPmonitor Service ===
 
Once schema creation is complete, stop the service again:
 
<syntaxhighlight lang="bash">
systemctl stop voipmonitor
systemctl stop voipmonitor
</syntaxhighlight>
</syntaxhighlight>


=== Step 6: Discard Tablespaces in the New Database ===
'''4. Discard tablespaces in new database:'''
 
For each table and partition you want to recover, discard the empty tablespace so we can import the old data. For the <code>cdr</code> table with partitions:
 
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
USE voipmonitorBackup;
USE voipmonitorBackup;


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


-- Discard all partitions
-- For partitioned tables, discard each partition:
ALTER TABLE cdr DISCARD TABLESPACE FOR PARTITION p20250101;
ALTER TABLE cdr DISCARD TABLESPACE FOR PARTITION p20250101;
ALTER TABLE cdr DISCARD TABLESPACE FOR PARTITION p20250102;
-- Repeat for all partitions (check with SHOW CREATE TABLE cdr\G)
 
-- Repeat for all partitions (use SHOW CREATE TABLE to see the list)
</syntaxhighlight>
 
You can generate the commands automatically for all partitions:
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
</syntaxhighlight>


=== Step 7: Copy Old .ibd Files to New Database Location ===
{{Tip|1=Generate DISCARD commands automatically:
 
<code>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;</code>}}
Locate the MySQL data directory. Common locations:
 
<code>/var/lib/mysql/</code> on Debian/Ubuntu
<code>/var/lib/mysql/</code> with a database subdirectory for each database
 
Copy the <code>.ibd</code> files from the old corrupted database to the new database directory:


'''5. Copy .ibd files from old database:'''
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Example: Copy cdr.ibd and partition files
# Copy data files
cp /var/lib/mysql/voipmonitor/cdr.ibd /var/lib/mysql/voipmonitorBackup/
cp /var/lib/mysql/voipmonitor/cdr.ibd /var/lib/mysql/voipmonitorBackup/
cp /var/lib/mysql/voipmonitor/cdr#p#*.ibd /var/lib/mysql/voipmonitorBackup/
cp /var/lib/mysql/voipmonitor/cdr#p#*.ibd /var/lib/mysql/voipmonitorBackup/


# Set correct ownership
# Set ownership
chown mysql:mysql /var/lib/mysql/voipmonitorBackup/cdr*.ibd
chown mysql:mysql /var/lib/mysql/voipmonitorBackup/cdr*.ibd
chmod 660 /var/lib/mysql/voipmonitorBackup/cdr*.ibd
</syntaxhighlight>
</syntaxhighlight>


{{Tip|1=<strong>Partition file naming:</strong> InnoDB partition files typically follow the pattern <code>table#p#partition_name.ibd</code>. Use <code>ls -la /var/lib/mysql/voipmonitor/</code> to see the exact file names.}}
'''6. Import tablespaces:'''
 
=== Step 8: Import Tablespaces in the New Database ===
 
Now import the tablespaces with the old data:
 
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
USE voipmonitorBackup;
USE voipmonitorBackup;


-- Import main table
ALTER TABLE cdr IMPORT TABLESPACE;
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 p20250101;
ALTER TABLE cdr IMPORT TABLESPACE FOR PARTITION p20250102;
-- Repeat for all partitions
 
-- Continue for all partitions
</syntaxhighlight>
</syntaxhighlight>


Repeat the import commands for all tables and partitions you recovered.
'''7. Verify and finalize:'''
 
=== Step 9: Check for Schema Mismatches ===
 
Check the syslog for any <code>ALTER</code> query warnings from VoIPmonitor:
 
<syntaxhighlight lang="bash">
journalctl -u voipmonitor | grep -i "ALTER\|schema"
</syntaxhighlight>
 
If VoIPmonitor logged schema differences, it means the old table structure differs from the current version. You may need to manually run the <code>ALTER TABLE</code> commands shown in the logs to ensure compatibility.
 
=== Step 10: Verify Data Integrity ===
 
Verify the data was imported correctly:
 
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Check if data exists
SELECT COUNT(*) FROM cdr;
SELECT COUNT(*) FROM cdr;
SELECT MIN(calldate), MAX(calldate) FROM cdr;
SELECT MIN(calldate), MAX(calldate) FROM cdr;
-- Sample random records
SELECT * FROM cdr ORDER BY RAND() LIMIT 10;
</syntaxhighlight>
</syntaxhighlight>


=== Step 11: Switch Back to Production Database ===
Update <code>/etc/voipmonitor.conf</code> to use <code>voipmonitorBackup</code> permanently, then start service:
 
Restore the original database configuration in <code>/etc/voipmonitor.conf</code>:
 
<syntaxhighlight lang="ini">
mysqldb = voipmonitor
</syntaxhighlight>
 
Then start VoIPmonitor:
 
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
systemctl start voipmonitor
systemctl start voipmonitor
</syntaxhighlight>
</syntaxhighlight>


If the migration was successful, you now have:
== Method 3: Drop and Recreate (Destructive) ==
* The original (corrupted) <code>voipmonitor</code> database (still corrupted, backup)
* The recovered <code>voipmonitorBackup</code> database (working, with data)
 
You can either:
1. Update <code>voipmonitor.conf</code> to use <code>voipmonitorBackup</code> permanently, OR
2. Drop and recreate the <code>voipmonitor</code> database, then use <code>mysqldump</code> to copy data from <code>voipmonitorBackup</code>
 
{{Note|1=<strong>Recommended approach:</strong> Use <code>voipmonitorBackup</code> as the production database by updating <code>mysqldb = voipmonitorBackup</code> in <code>/etc/voipmonitor.conf</code>. 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 <code>.ibd</code> 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.
{{Warning|1=Use ONLY when .ibd files are corrupted and you have a backup or data loss is acceptable.}}
 
=== Step 1: Stop VoIPmonitor ===


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
systemctl stop voipmonitor
systemctl stop voipmonitor
</syntaxhighlight>
</syntaxhighlight>
=== Step 2: Get Fresh Schema ===
Since the table is corrupted, <code>SHOW CREATE TABLE</code> 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 ===


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
USE voipmonitor;
USE voipmonitor;
DROP TABLE IF EXISTS cdr;
DROP TABLE IF EXISTS cdr;
-- Get fresh schema from GUI: Tools > System Status > Check MySQL Schema
CREATE TABLE `cdr` ( ... );
</syntaxhighlight>
</syntaxhighlight>
{{Warning|1=<strong>Severe Corruption:</strong> If DROP hangs, stop MariaDB, manually delete files from <code>/var/lib/mysql/voipmonitor/</code> (like <code>cdr.frm</code>, <code>cdr.ibd</code>, <code>cdr.par</code>), then restart MariaDB.}}
=== Step 4: Recreate Table with Fresh Schema ===
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
=== Step 5: Restart VoIPmonitor ===


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
Line 288: Line 134:
</syntaxhighlight>
</syntaxhighlight>


== Troubleshooting ==
== Method 4: System Table Corruption ==
 
=== Import Fails with Table Definition Mismatch ===
 
Error: <code>Tablespace storage engine version 123 is different from what the table expects</code>
 
This means the schema differs between the old table and the new table. Solutions:
 
1. <strong>Use mysqldump backup:</strong> If you have a recent SQL dump, restore it instead
2. <strong>Manual schema upgrade:</strong> Run the <code>ALTER TABLE</code> commands that VoIPmonitor logged in syslog
3. <strong>Recreate with matching schema:</strong> Use the exact same schema version as the original table
 
=== Cannot DISCARD TABLESPACE ===
 
Error: <code>ERROR 1478 (HY000): InnoDB: Tablespace not discarded</code>
 
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:
 
<syntaxhighlight lang="sql">
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'voipmonitorBackup' AND TABLE_NAME = 'cdr';
</syntaxhighlight>
 
For foreign key issues, temporarily disable foreign key checks:
 
<syntaxhighlight lang="sql">
SET FOREIGN_KEY_CHECKS=0;
-- Run DISCARD/IMPORT commands
SET FOREIGN_KEY_CHECKS=1;
</syntaxhighlight>
 
=== Table Missing After Recovery ===


If the table disappears after recovery steps:
Applies when MySQL logs errors about <code>mysql.innodb_table_stats</code>, <code>mysql.column_stats</code>, or similar system tables. Usually caused by disk space exhaustion.
 
1. Check if the table was imported with a different name
2. Verify you are in the correct database (<code>USE voipmonitorBackup</code>)
3. Check MySQL error logs for failed import messages
 
<syntaxhighlight lang="sql">
SHOW TABLES;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%cdr%';
</syntaxhighlight>
 
== Prevention ==
 
To prevent table corruption in the future:
 
{{Warning|1=<strong>Important:</strong> These recommendations improve reliability but do not guarantee protection against all corruption scenarios. Always maintain regular backups.}}
 
* <strong>Regular Backups:</strong> Use <code>mysqldump</code> to create regular backups. Consider automating with cron
* <strong>Monitor Disk Space:</strong> Avoid running MySQL/MariaDB with disk space near 100%
* <strong>Proper Shutdown:</strong> Always stop services cleanly with <code>systemctl stop</code> before maintenance
* <strong>Replication:</strong> Consider master-slave replication for redundancy. See [[Mysql_master-slave_replication_hints|Master-Slave Replication Hints]]
* <strong>Hardware Health:</strong> Monitor disk errors with <code>smartctl</code> and replace failing drives
* <strong>UPS/Battery Backup:</strong> Use uninterruptible power supplies to prevent sudden power loss
 
== Backup and Restore Examples ==
 
=== Creating Full Database Backup ===
 
<syntaxhighlight lang="bash">
# 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
</syntaxhighlight>
 
=== Restoring from Backup ===
 
<syntaxhighlight lang="bash">
# 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
</syntaxhighlight>
 
=== Automated Example Backup Script ===
 
<syntaxhighlight lang="bash">
#!/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"
</syntaxhighlight>
 
Add to crontab:
 
<syntaxhighlight lang="bash">
# Daily backup at 2:00 AM
0 2 * * * /usr/local/bin/backup_voipmonitor_db.sh >> /var/log/voipmonitor_backup.log 2>&1
</syntaxhighlight>
 
== Method 4: MySQL System Table Corruption (mysql.innodb_table_stats, mysql.column_stats) ==
 
If MySQL error logs report missing or corrupted system tables like <code>mysql.innodb_table_stats</code>, <code>mysql.column_stats</code>, or <code>mysql.innodb_index_stats</code>, InnoDB will fall back to transient statistics. This is often caused by running out of disk space on the MySQL data partition.
 
{{Warning|1=System tables (mysql.* schema) are managed by the database server. Do not manually drop or recreate them.}}


=== Diagnosis ===
=== Diagnosis ===
Check MySQL error logs for specific error messages:
<syntaxhighlight lang="bash">
# 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
</syntaxhighlight>
Look for errors like:
* <code>Table 'mysql.innodb_table_stats' doesn't exist</code>
* <code>Can't open file './mysql/innodb_table_stats.ibd'</code>
* <code>Unable to open table mysql.column_stats</code>
=== 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:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Check disk space on MySQL data directory
# Check disk space (common root cause)
df -h /var/lib/mysql
df -h /var/lib/mysql


# Show mount point and available space
# Check MySQL error log
df -h /
tail -100 /var/log/mysql/error.log
</syntaxhighlight>
</syntaxhighlight>


If the partition containing MySQL data is at or near 100% capacity:
=== Quick Workaround ===
 
<syntaxhighlight lang="bash">
# Create free space immediately (delete old logs, caches, etc.)
# Examples:
sudo journalctl --vacuum-time=7d
sudo apt clean  # or 'dnf clean' on RHEL/CentOS
</syntaxhighlight>
 
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:
 
<syntaxhighlight lang="bash">
# 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
</syntaxhighlight>
 
Add or modify in the <code>[mysqld]</code> section:


Add to <code>/etc/mysql/my.cnf</code> under <code>[mysqld]</code>:
<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
[mysqld]
# Disable persistent statistics (use transient statistics instead)
innodb_stats_persistent = 0
innodb_stats_persistent = 0
</syntaxhighlight>
</syntaxhighlight>
Restart MySQL/MariaDB:


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Debian/Ubuntu
systemctl restart mysql
sudo systemctl restart mysql
# Or: sudo systemctl restart mariadb
 
# CentOS/RHEL
sudo systemctl restart mysqld
</syntaxhighlight>
</syntaxhighlight>


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.
=== Permanent Fix ===
 
{{Note|1=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:


'''Option A: Run mysql_upgrade''' (after OS/MySQL version upgrades):
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# 1. Create a backup
mysql_upgrade -u root -p
mysqldump -u root -p --all-databases > mysql_backup_$(date +%Y%m%d).sql
systemctl restart mysql
 
# 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;"
</syntaxhighlight>
</syntaxhighlight>


=== Verification ===
'''Option B: Full database reinitialize''' (if Option A fails):
 
After applying any of the above fixes, verify that MySQL is using persistent statistics correctly:
 
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Check MySQL error logs - should no longer show system table errors
# 1. Backup everything
tail -50 /var/log/mysql/error.log
mysqldump -u root -p --all-databases > full_backup.sql


# Verify persistent statistics setting
# 2. Stop and reinitialize
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_stats_persistent';"
systemctl stop mysql
mv /var/lib/mysql /var/lib/mysql-corrupted
mysql_install_db --user=mysql  # or: mysqld --initialize --user=mysql


# Verify system tables are accessible
# 3. Start and restore
mysql -u root -p -e "SELECT COUNT(*) FROM mysql.innodb_table_stats;"
systemctl start mysql
mysql -u root -p -e "SELECT COUNT(*) FROM mysql.column_stats;"
mysql -u root -p < full_backup.sql
</syntaxhighlight>
</syntaxhighlight>


=== Scenario-Specific Solutions ===
== Troubleshooting ==


==== After OS Upgrade (Version Mismatch) ====
{| class="wikitable"
! Error !! Solution
|-
| Schema mismatch during IMPORT || Check <code>journalctl -u voipmonitor</code> for ALTER TABLE commands and run them manually
|-
| Cannot DISCARD TABLESPACE || Verify InnoDB engine: <code>SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='cdr';</code><br/>Try: <code>SET FOREIGN_KEY_CHECKS=0;</code> before DISCARD
|-
| DROP TABLE hangs || Stop MariaDB, delete files manually from <code>/var/lib/mysql/voipmonitor/</code>, restart
|}


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:
== Prevention ==
 
<syntaxhighlight lang="bash">
# 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
</syntaxhighlight>
 
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 [[GUI_troubleshooting#MariaDB_System_Table_Corruption_After_OS_Upgrade|MariaDB System Table Corruption After OS Upgrade]] for detailed OS upgrade scenario guidance.}}
 
=== Prevention ===


To prevent MySQL system table corruption in the future:
* '''Regular backups:''' <code>mysqldump -u root -p voipmonitor | gzip > backup_$(date +%F).sql.gz</code>
* '''Monitor disk space:''' Alert when MySQL partition > 80% full
* '''Clean shutdown:''' Always use <code>systemctl stop</code> before maintenance
* '''UPS:''' Prevent sudden power loss
* '''Replication:''' See [[Redundant_database|Redundant Database]]


* **Monitor disk space** on the MySQL data partition daily
=== Automated Backup Example ===
* 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


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Add to crontab for disk monitoring
#!/bin/bash
# Warn if /var/lib/mysql usage > 80%
# /usr/local/bin/backup_voipmonitor_db.sh
0 * * * * df /var/lib/mysql | awk '{if ($5+0 > 80) print "WARNING: MySQL disk at " $5}' | mail -s "MySQL disk warning" admin@example.com
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
</syntaxhighlight>
</syntaxhighlight>


== Related Documentation ==
Cron: <code>0 2 * * * /usr/local/bin/backup_voipmonitor_db.sh</code>
 
== See Also ==


* [[Database_structure|Database Structure Reference]] - Overview of VoIPmonitor database tables
* [[Database_structure|Database Structure Reference]]
* [[Mysql_master-slave_replication_hints|Master-Slave Replication Hints]] - Setting up database replication for redundancy
* [[Database_troubleshooting|Database Troubleshooting]]
* [[Redundant_database|Redundant Database]] - Real-time replication to secondary server
* [[Redundant_database|Redundant Database]]
* [[Emergency_procedures|Emergency Procedures]] - Recovering from system failures
* [[Emergency_procedures|Emergency Procedures]]


== AI Summary for RAG ==
== 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.
'''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, 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
'''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:'''
'''Key Questions:'''
* How do I fix a corrupted cdr table with .par file errors?
* How do I fix "Failed to read from .par file" error?
* How do I recover data from corrupted InnoDB table using DISCARD/IMPORT?
* How do I recover data from corrupted InnoDB table using transportable tablespaces?
* 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.innodb_table_stats missing or corrupted?
* How do I fix mysql database system tables after running out of disk space?
* What causes MySQL system table corruption?
* What is innodb_stats_persistent workaround for corrupted system tables?
* When should I use mysql_upgrade vs transportable tablespaces?
* 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?
* How do I prevent database corruption in VoIPmonitor?
* What backup strategy should I use for VoIPmonitor database?

Latest revision as of 16:48, 8 January 2026


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"