Recovering corrupted database tables: Difference between revisions

From VoIPmonitor.org
(Review: opravy syntaxe nadpisů (= -> ==), překlepy, config formát, přidán diagram procesu obnovy)
(Rewrite: consolidated from 617 to 237 lines, added quick reference table, streamlined procedures, improved structure)
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{DISPLAYTITLE:Recovering Corrupted Database Tables}}
{{DISPLAYTITLE:Recovering Corrupted Database Tables}}
[[Category:Database]]
[[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|
<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 27: 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">
flowchart TB
%%{init: {'flowchart': {'nodeSpacing': 10, 'rankSpacing': 25}}}%%
    subgraph OLD["Corrupted Database (voipmonitor)"]
flowchart LR
        A1[".par file ❌ corrupted"]
     A["1. Stop voipmonitor"] --> B["2. Create new DB"]
        A2[".ibd file ✓ intact"]
    B --> C["3. Start/stop voipmonitor<br/>(creates schema)"]
    end
    C --> D["4. DISCARD tablespace"]
 
    D --> E["5. Copy .ibd files"]
    subgraph NEW["Recovery Database (voipmonitorBackup)"]
     E --> F["6. IMPORT tablespace"]
        B1["Fresh schema created<br/>by VoIPmonitor"]
     F --> G["7. Verify & switch"]
        B2["Empty .ibd files"]
    end
 
     subgraph PROCESS["Recovery Process"]
        P1["1. Stop VoIPmonitor"]
        P2["2. Create new DB"]
        P3["3. Start VoIPmonitor<br/>(creates schema)"]
        P4["4. Stop VoIPmonitor"]
        P5["5. DISCARD TABLESPACE"]
        P6["6. Copy .ibd files"]
        P7["7. IMPORT TABLESPACE"]
        P8["8. Verify & Switch"]
     end
 
    A2 -->|"Copy .ibd"| P6
     P1 --> P2 --> P3 --> P4 --> P5
    B2 -->|"Discard empty"| P5
    P5 --> P6 --> P7 --> P8
    P8 -->|"Production"| RESULT["✓ Recovered Data<br/>in voipmonitorBackup"]
</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>
</syntaxhighlight>


You can generate the commands automatically for all partitions:
{{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>}}
<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>
 
=== Step 7: Copy Old .ibd Files to New Database Location ===
 
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|
'''6. Import tablespaces:'''
<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.}}
 
=== 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>
 
Repeat the import commands for all tables and partitions you recovered.
 
=== 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>
</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.
'''7. Verify and finalize:'''
 
=== 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>
=== Step 11: Switch Back to Production Database ===
Restore the original database configuration in <code>/etc/voipmonitor.conf</code>:
<syntaxhighlight lang="ini">
mysqldb = voipmonitor
</syntaxhighlight>
</syntaxhighlight>


Then start VoIPmonitor:
Update <code>/etc/voipmonitor.conf</code> to use <code>voipmonitorBackup</code> permanently, then start service:
 
<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:
{{Warning|1=Use ONLY when .ibd files are corrupted and you have a backup or data loss is acceptable.}}
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|
<strong>Recommended approach:</strong> Use <code>voipmonitorBackup</code> as the production database by updating the <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.
 
=== 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|
<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 292: 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>
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.


This means the schema differs between the old table and the new table. Solutions:
=== Diagnosis ===
<syntaxhighlight lang="bash">
# Check disk space (common root cause)
df -h /var/lib/mysql


1. <strong>Use mysqldump backup:</strong> If you have a recent SQL dump, restore it instead
# Check MySQL error log
2. <strong>Manual schema upgrade:</strong> Run the <code>ALTER TABLE</code> commands that VoIPmonitor logged in syslog
tail -100 /var/log/mysql/error.log
3. <strong>Recreate with matching schema:</strong> Use the exact same schema version as the original table
</syntaxhighlight>


=== Cannot DISCARD TABLESPACE ===
=== Quick Workaround ===


Error: <code>ERROR 1478 (HY000): InnoDB: Tablespace not discarded</code>
Add to <code>/etc/mysql/my.cnf</code> under <code>[mysqld]</code>:
<syntaxhighlight lang="ini">
innodb_stats_persistent = 0
</syntaxhighlight>


Possible causes:
<syntaxhighlight lang="bash">
* Table is not InnoDB storage engine
systemctl restart mysql
* 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>
</syntaxhighlight>


For foreign key issues, temporarily disable foreign key checks:
=== Permanent Fix ===


<syntaxhighlight lang="sql">
'''Option A: Run mysql_upgrade''' (after OS/MySQL version upgrades):
SET FOREIGN_KEY_CHECKS=0;
<syntaxhighlight lang="bash">
-- Run DISCARD/IMPORT commands
mysql_upgrade -u root -p
SET FOREIGN_KEY_CHECKS=1;
systemctl restart mysql
</syntaxhighlight>
</syntaxhighlight>


=== Table Missing After Recovery ===
'''Option B: Full database reinitialize''' (if Option A fails):
 
<syntaxhighlight lang="bash">
If the table disappears after recovery steps:
# 1. Backup everything
mysqldump -u root -p --all-databases > full_backup.sql


1. Check if the table was imported with a different name
# 2. Stop and reinitialize
2. Verify you are in the correct database (<code>USE voipmonitorBackup</code>)
systemctl stop mysql
3. Check MySQL error logs for failed import messages
mv /var/lib/mysql /var/lib/mysql-corrupted
mysql_install_db --user=mysql  # or: mysqld --initialize --user=mysql


<syntaxhighlight lang="sql">
# 3. Start and restore
SHOW TABLES;
systemctl start mysql
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%cdr%';
mysql -u root -p < full_backup.sql
</syntaxhighlight>
</syntaxhighlight>


== Prevention ==
== Troubleshooting ==


To prevent table corruption in the future:
{| 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
|}


{{Warning|
== Prevention ==
<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 ==
* '''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]]


=== Creating Full Database Backup ===
=== Automated Backup Example ===
 
<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">
<syntaxhighlight lang="bash">
#!/bin/bash
#!/bin/bash
# /usr/local/bin/backup_voipmonitor_db.sh
# /usr/local/bin/backup_voipmonitor_db.sh
BACKUP_DIR="/var/backups/voipmonitor"
BACKUP_DIR="/var/backups/voipmonitor"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="root"
mkdir -p $BACKUP_DIR
mkdir -p $BACKUP_DIR
 
mysqldump -u root -p"$MYSQL_PASSWORD" voipmonitor | gzip > "$BACKUP_DIR/voipmonitor_$(date +%F).sql.gz"
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD voipmonitor | gzip > $BACKUP_DIR/voipmonitor_$DATE.sql.gz
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
 
# Keep only last 7 days
find $BACKUP_DIR -name "voipmonitor_*.sql.gz" -mtime +7 -delete
 
echo "Backup completed: voipmonitor_$DATE.sql.gz"
</syntaxhighlight>
</syntaxhighlight>


Add to crontab:
Cron: <code>0 2 * * * /usr/local/bin/backup_voipmonitor_db.sh</code>
 
<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>


== Related Documentation ==
== 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. 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.
'''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, database corruption recovery, partitions, cdr table, mysqldump backup, database backup, master-slave replication, redundant database
'''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 in MariaDB?
* How do I fix "Failed to read from .par file" error?
* What do I do when REPAIR TABLE fails with "Failed to read from the .par file"?
* How do I recover data from corrupted InnoDB table using transportable tablespaces?
* How do I recover data from a corrupted InnoDB table?
* How do I fix mysql.innodb_table_stats missing or corrupted?
* What is the transportable tablespaces method for database recovery?
* What causes MySQL system table corruption?
* How do I use ALTER TABLE DISCARD TABLESPACE and IMPORT TABLESPACE?
* When should I use mysql_upgrade vs transportable tablespaces?
* How do I recover .par file corruption without losing data?
* How do I prevent database corruption in VoIPmonitor?
* How do I copy .ibd files to recover database tables?
* What backup strategy should I use for VoIPmonitor database?
* 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?

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"