Common MySQL Database corruption errors and their recommended solutions!File Repair Tool Blog

MySQL is a relational database management system that is based on SQL. This is considered as reliable, performance-driven database which is used by most of the organization to develop cost effective database applications. Database corruption is rare with MySQL and you can find some cases where your database fails to respond or becomes inaccessible. Mentioned below are some of the cases and their solutions.

MyISAM Table corruption in MySQL Database

MyISAM uses a table format which is less susceptible to corruption. Corruption can occur in MyISAM table due to some reason like hardware failures, system shutdown, and abrupt termination of MySQL or due to bugs in MySQL or MyISAM code. The corrupt or damage MyISAM tables show some sudden behavior which manifest itself while running queries against these tables. Further you can come across some errors which indicate corruption in your MyISAM tables. Below are the common errors that are generated because of MyISAM table corruption. They are:

• Record-file is crashed
• Record was already deleted
• Old database file
• Table is crashed and last repair failed
• Duplicate unique key or constraint on write or update

All the above mentioned errors require ‘myisamchk’- a table-maintenance utility that is built in MyISAM. By using this, you can fix corruption in MyISAM tables. You should first switch to the database directory and then verify the permissions of the table files. Given below is the positive move toward MyISAM to repair the tables.

Checking the MyISAM Tables:

By running myisamchk, you can check the tables. MYI or myisamchk –e. If you have to skip all the unnecessary information, then you can use ‘-s’ option with command. If the server stopped, then you can inform the database engine to mark the table as corrupt by using ‘-update-state’ option. But if there are any errors, then you can proceed to step 2. Here also if engine crashes or you get unexpected error, then move to step 3.

Repairing Tables by using ‘myisamchk –r –q’

When you come to this step, simply run the ‘myisamchk’ utility with ‘-r’ and ‘-q-‘options. With the help of this, you can repair in ‘quick recovery mode’ which fixes corruption in the index file without modifying the data file. This method of repair is ideal for situations in which your data file is intact and the delete links in the data file are consistent. But if all this fails to work, then you can try the following procedures:

• Backup your data file to a different location
• Run ‘myisamchk –r table name’ to protect repair in ‘recovery mode’. Such type of repair attempts to reconstruct the index and delete all the incorrect rows from the data file.
• If all the above steps fail to make the repair, run ‘myisamchk –safe-recover table_name’. ‘-safe-recovery’ means ‘safe-recovery’ mode which can deal with some more cases of corruption other those handled by regular recovery mode.

Performing difficult repair

This method is applicable to cases where your first block in the index file is corrupt or the index file goes missing. Therefore to fix, you need to create new index file using the following steps:

• Copy your data file to some another location
• Create the new data and index files through the table description file

shell> mysql db_name

mysql> SET autocommit=1;

mysql> TRUNCATE TABLE tbl_name;

mysql> quit

• After that copy the data file that you have copied earlier into the new data file.

InnoDB Table corruption in MySQL Database

InnoDB is the default database engine for MySQL since v 5.5.5. InnoDB tables are less likely to fall prey to corruption as they are transactional and reliable. When you shut down the database disgracefully, its internal recovery mechanism does good job of restricting your database to a consistent state by running the log file. However, there is always a risk of corruption when you are running hardware.

But if you still find some issues with your MySQL database, then you can use software to fix MySQL database corruption.  You can use MySQL Repair Tool as this repair tool is powerful data recovery software for corrupted MySQL server database files. This software is equipped to deal with all types of database corruption issues and also help you to recover every single object in your MySQL database. This software repair corrupt MySQL database and it is easy to use and does not require any technical knowledge. Even it recovers MySQL database that is created on Linux computer and also provides you to preview all the items which you wish to recover.

Steps to repair MySQL Database corruption

Step 1: Download, install and launch the MySQl Database repair tool.

Step 2: Click ‘Select’ button to find and add corrupt MySQL database to the application and then select OK. Here you have the option of ‘Repair corrupt database which is stored at default location of MySQL’ and ‘Manual option to selct your database from default location’.

Step 3: After the scanning process you can now see the preview of the file in the left panel of the software.

Step 4: Now you can start the reairing process, just click on Repair button. A dialoguie box will appear. Click Yes.

Step 5: After successful completion of the process, a message box is displayed as ‘repair process finished successfully’. Click ‘OK’ to close the message box.