Summary: There are various factors that can lead to corruption in foreign keys in MariaDB. This post discusses the possible causes of corruption in foreign keys and the solutions to repair and recover them. It also mentions an advanced MariaDB database repair tool that can help recover foreign keys and other data from corrupted database quickly.
Foreign key is one of the components of the MariaDB database. Also known as the referencing key, it matches the primary key field of another table to maintain referential integrity. The table with a foreign key is called the child table and the table with the primary key is known as the parent table. If the foreign key gets corrupted, it can impact enforcing data integrity rules and consistency of the database. In such case, you may face issues while adding or modifying tables that involve foreign keys. But how does a foreign key get corrupted and how can you fix it? That’s what we are going to address in this post.
What causes Corruption in Foreign Keys in MariaDB?
There are several reasons that can lead to corruption in foreign keys and other components of MariaDB. Some of them are:
- Bugs in the MariaDB Server.
- Abrupt system shutdown during the read/write operation.
- Sudden restart of MariaDB Server instance.
- Sudden power failure or server shutdown.
- Faulty operating system.
- Hard drive where MariaDB database is stored has bad sectors.
- Unexpected (abrupt) system shutdown or crash when the database file is open.
- Hardware or software issues.
- Lack of storage space on the hard drive where the database file is stored.
- Virus or malware infection.
How to Detect Corruption in the MariaDB Database?
Before proceeding to the solutions, you need to check the MariaDB database for corruption. Here are some ways you can use to check corruption in the MariaDB database.
Method 1: Use SHOW ENGINE INNODB STATUS Statement
The SHOW ENGINE INNODB STATUS statement provides information related to foreign keys that can be used to diagnose issues. Foreign key constraints mainly cause deadlock or locking issues. You can check the LATEST FOREIGN KEY ERROR section and LATEST DETECTED DEADLOCK section to verify foreign key errors and locking issues.
Method 2: Check Error Logs
The error logs contain information about all the issues in the MariaDB tables. If there is any issue with foreign keys, the error logs record error messages indicating issues with the foreign key constraints. You can check the error log file to find any references to foreign key violations or corruption.
Method 3: Use CHECK TABLE Command
The CHECK TABLE command checks the MariaDB tables for any errors and issues. This command supports tables created in Archive, Aria, CSV, InnoDB, and MyISAM storage engines. Here’s the syntax of the CHECK TABLE command:
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
If the CHECK TABLE command finds any issue in the tables, InnoDB engine will purposely cause MariaDB to crash to stop corruption from further damage.
Ways to Repair Corrupt Foreign Keys in MariaDB
Here are the methods you can use to repair and recover corrupt foreign keys in MariaDB.
Method 1: Restore Backup using Mariabackup Utility
If you have an updated backup file, you can restore the MariaDB database from backup using Mariabackup – an inbuilt tool in MariaDB. You can use either the –copy-back or the –move-backup option. Here’s how to use the –copy-back option in the Mariabackup utility to restore the backup:
Step 1: Empty the Data Directory
The –copy-back command restores the backup to the data directory, known as –datadir. So before running the command, you need to empty the data directory. To check and clear the data directory, go to the configuration file, which is usually located in /etc/mysql/my.cnf.
Step 2: Disable MariaDB Server Service
Run the below command to disable the MariaDB Server service:
mysql.server stop
Step 3: Run the Mariabackup with –copy-back Option
Now, run the Mariabackup with the –copy-back option:
$ mariabackup --copy-back \
--target-dir=/var/mariadb/backup/
Step 4: Change the File Permissions
Next, change the file permissions of the data directory to change the ownership of data directory file. Run the below command:
$ chown -R mysql:mysql /var/lib/mysql/
Step 5: Restart the MariaDB Service
Now, restart the MariaDB service using the below command:
mysql.server stop
Method 2: Use Dump and Reload Method
If the backup is obsolete or you need to restore specific tables from MariaDB database, then you can use the Dump and Reload method. This can help in rebuilding the tables in MariaDB database and also the foreign keys within the tables. Let’s see how to use this method.
First, you need to restart the MariaDB using the –innodb-force-recovery=# option. You can set it to a lower value other than zero.If MariaDB fails to start, then retry setting a higher value, until it starts. Once the crash recovery mode is enabled, try restoring the table data. Here are the steps:
- Dump data from the table with corrupt foreign keys using the SELECT command.
- Then, create the table with the same structure as a corrupt table. To do this, you can execute the SHOW CREATE TABLE command as given below:
SHOW CREATE TABLE table_name
- Now, save the data in the created table.
- Restart MariaDB using the following command:
mysqld start
- Next, drop the table using the DROP TABLE command.
DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/]
table_name [, table_name] ...
[WAIT n|NOWAIT]
[RESTRICT | CASCADE]
The DROP TABLE command will remove all the data in the table, including table definitions and triggers.
- Now, recreate the table and restore the dump.
Note: You can restore data from one table at a time by using the Dump and Reload method.
Method 3: Use a Professional MariaDB Repair Tool
To quickly and easily repair the MariaDB database, you can use a professional MariaDB database repair tool, like Stellar Repair for MySQL. It is an easy-to-use tool that can recover all the data, such as foreign keys, primary keys, views, triggers, etc. from corrupted or damaged MariaDB database. The tool features an interactive user interface that helps you to perform the repair and recovery operation quickly in just a few simple steps.
Here are some key capabilities of Stellar Repair for MySQL:
- Supports all MariaDB versions up to 11.3.2
- Restores all the data from MySQL databases, including deleted records and partition tables
- Repairs MariaDB tables created in InnoDB and MyISAM engines with complete precision
- Provides an enhanced preview of repaired data before saving
- Allows repairing of multiple databases at once
- Allows to save repaired MariaDB database in multiple formats, like MySQL, MariaDB, SQL Script, CSV, HTML, and XLS
- Compatible with both Windows and Linux operating systems
Conclusion
Foreign keys in MariaDB can get corrupted due to several reasons. You can follow the methods mentioned above to repair the MariaDB database with corrupt foreign keys. However, the best option to repair the corrupted database is by using a specialized MariaDB repair tool, like Stellar Repair for MySQL. This database repair tool can repair the corrupted database and restore all the data, including primary keys and foreign keys, with complete precision. To evaluate the functionality of Stellar Repair for MySQL, download its demo version for free. The tool supports all versions of MariaDB Server.
