How to Fix Corrupted Foreign Keys in MariaDB?

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.

Was this article helpful?

YES1

NO

About The Author

Monika Dadool

Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing. She is a tech enthusiast and expert who specializes in writing about SQL Server, MySQL Server, MariaDB Server, Microsoft Access, Active Directory, email recovery, Microsoft 365, pattern recognition, machine learning, data recovery, file repair, and operating systems like Linux, Windows, and Mac. She also writes about accounting software such as QuickBooks and Sage 50, as well as web-scripting languages like HTML, JavaScript, Python, PHP, Visual Basic, ASP.NET, and AJAX. Monika is passionate about researching and exploring new technologies, and she enjoys developing engaging technical blogs that help organizations and database administrators resolve various issues. When she's not creating content, you can find her on social media, watching web series, reading books, or exploring new food recipes.