Analyzing the root causes of Index file corruption in MySQL and methods to perform database recovery

MySQL is an open source database that is freely available under GNU General public license. It offers developers a range of storage engines, including Berkeley DB, InnoDB, Heap, and MyISAM. It incorporates adequate security measures and also provides regular security patches to keep the software up-to-date. When it comes to performance, it clearly dominates over the other databases. However, MySQL databases may get corrupt in the event of a virus infection, hardware malfunction, or an unexpected system shutdown. In such circumstances, you lose access to the vital information stored in the database. To overcome this problem, you need to go for MySQL recovery through a reliable third-party utility.

For instance, you might receive the following error message on your screen while trying to access a MySQL table:

“No more room in index file”

Cause: The above error occurs when you try to access a damaged or corrupt MySQL table. This would render all valuable records in the table inaccessible. At this stage, you may use an updated database backup to restore the table. However, if the backup is unavailable, you should follow the below mentioned resolution steps.

Resolution: For resolving the issue, follow the below given steps:

• Firstly, ensure that you have full administrative rights on the database that you are trying to access.
• Use the following commands to verify the integrity of the database table:

CHECK TABLE tablename;

myisamchk *.MYI

mysqlcheck *.MYI

• Use the 'REPAIR' command with appropriate options to repair corruption:

REPAIR TABLE table_name

• Now, use the 'EXIT' command to quit the database. Restart the MySQL server.

If the problem still persists, you should take help of an efficient MySQL repair utility. These software embed a range of advanced and highly robust mechanisms to efficiently scan corrupt MySQL databases and restore valuable objects, such as tables, primary keys, indexes, constraints, etc.

These third-party tools provide preview of all recoverable objects and allow saving log of the recovery process in a separate text file. They support all the latest MySQL versions, including MySQL 3.x, 4.x, 5.x, and 6.x. Furthermore, they are compatible with Windows 7, Vista, Server 2003, XP, and 2000.

How to check & verify InnoDB TableSpace File Corruption

MySQL database offers an offline InnoDB File Checksum Utility (innochecksum) to check the checksums for innodb files. When you run innochecksum utility on the mysql database then it will read and print checksums for innodb tablespace files. After calculating checksum it will compare the calculated checksum with stored checksum, if you found checksum mismatch between calculated checksum and stored checksum that means pages are corrupt.

Process of innochecksum Utility:

Read innoDB tablespace files-->Calculate checksum-->Compare checksum-->Report checksum mismatch

Why developed this tool: There are two main motives to develop this utility that are as:

• To speed up verifying the integrity of tablespace files after power outages.
• To verify the integrity after file copies.

Use as soon as Possible: As we know that checksum mismatch will shutdown the running mysql server so use this utility to as soon as possible rather than waiting production database will encounter the damaged pages.

Note: innochecksum does not work on tablespace files that the server already has open. For those types of files you can use CHECK Table command to check tables within the tablespace.

Recover Tablespace: When innochecksum utility finds checksum mismatch for the innodb tablespace files then it is recommended you to restore the tablespace from backup or start mysql server and use mysqldump to make a backup of table’s within the tablespace.

How to restore without Backup: If you don't have backup of corrupt tablespace then you should try any third party MySQL repair utility that will restore corrupt tablespace of mysql database.

Attention: Always maintain a backup for any database to avoid data loss when disaster will strike to the database.

Summary: innochecksum is an offline innodb file checksum utility that calculates, compares and reports checksum mismatch for innodb database. You can restore corrupt tablespace from backup or using any third party MySQL repair utility.

Incorrect Information in .FRM file of MyISAM storage engine

MySQL database uses tables to store data and all these tables are defined by .FRM file. FRM is an internal file in MySQL database that contains the formating information or structure data for the database. It defines fields and structure of each MySQL's table. Regardless of the storage engine that you choose, every MySQL table is represented by .frm file. File bears the same name as table name with extension .FRM.

Example: suppose you have create a employee table in mysql database then frm file name will be employee.frm that will define the employee table.

Corruption in MySQL database table will resist you to access the table that can be happen due to several reasons like kernel bugs, unexpected power failure, hardware failure, virus attack, and many more. A corrupted table will placed the incorrect information in the frm file and you may got below error message:

“Incorrect information in file: './able-name.frm'”

Resolution: you have three solutions to repair MySQL table after getting above error message:

1. Repair corrupt table by REPAIR TABLE statement
2. Restore from Backup
3. Stop and copy method

Stop and copy method details
Note: if you are using MyISAM storage engine then follow the below steps to repair corrupt table:

• Stop MySQL database
• Take backup of your .frm, .myd, myi files.
• Delete these files from mysql data directory.
• Now start MySQL Database
• Rereate table again by CREATE Table statement.
• Stop MySQL database.
• Copy .myd & .myi files back into the data directory, replace the ones there.
• Start MySQL database again.

If all three methods fail to fix the corruption from table then at last you should try Stellar Phoenix MySQL repair software. MySQL repair software repairs corrupted table created using MyISAM as well as Innodb. This software has around 99 percent success rate. Its very safe and offers easy-to-use interface.

REPAIR TABLE statement to Repair corrupt MySQL Table

MySQL database offers seven syntaxes for proper maintaining of mysql table's records. Those syntaxes are as follows:

1. Repair table syntax 
2. Analyze table syntax
3. Backup table syntax
4. Check  table syntax
5. Checksum  table syntax
6. Optimize  table syntax
7. Restore  table syntax

In this article, we will discuss briefly about first syntax that is Repair Table syntax.

REPAIR Table Syntax: It repairs corrupted mysql database tables. A table gets corrupt due to several reasons like virus attack, system failure, power failure, hardware problem, power failure and so on. Corrupt mysql database tables can be repair by this syntax.

Systax for Repair Table:
“REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]”

Above syntax has three options like quick, extended, and use_frm.
When you select “quick” option then repair table syntax will try to repair index file only. This kind of repair is done by myisamchk.

When you select “extended” option then mysql database creates row by row index in place of one row at a time.  This kind of repair is done by myisamchk.

Use_frm option is available only when header file is corrupted or index file missing.  This kind of repair is not done by myisamchk.

Caution: It is the best method to take a backup of corrupt mysql database table before trying “Repair Table” syntax on it.

Sometimes above repair syntax also fails to fix corruption in mysql database at this situation most of the database administrators suggest to try any third party mysql database repair software. It totally depends upon you to choose the best mysql repair tool that meets your requirement and budget.

How to Repair MySQL Table using phpMyAdmin?

MySQL is a relational database and uses table to store the data. Table has column and row to store records. An Individual column must be created to store each type of data like employee name, employee code, employee address, age, sex, and many more. On the other side row contains the actual value that specified with the column of table.

Example: Lets consider an example, we have created an employee details table. Employee details table has five columns employee name, employee code, employee address, employee age, and employee sex. We can insert values into the employee details table through the insert query.

We have inserted John, s0300, New Jersey, 28, and M. Wherein

John= Employee name

s0300=Employee code

New Jersey=Employee adress

28=Employee age

M=Employee Sex.

Corruption in Employee Details Table: Corruption in the above table can happen due to severely reasons like operating system failure, power failure, hardware problem, metadata structure corruption, virus attack, and many more.

How to Repair?: When MySQL database tables get corrupted or damaged, you can repair and restore corrupted MySQL table by carrying out the below mentioned methods.

PhpMyAdmin Method

Third party repair software method

Repair using phpMyAdmin: Follow the below steps to repair corrupt MySQL database table through phpmyadmin.

• Login to the phpmyadmin.
• Select the database.
• Note: if you have only one database then it will be selected by default.
• Goto to the main panel and choose database.
• Select the corrupted MySQL database table.
• Goto to the bottom of the window and click on the drop down menu.
• Select Repair Table from the menu.
• And phpmyadmin automatically fix the corruption in the table.

Third party repair software: If you are unable to repair corrupt MySQL table through above mentioned method then you should try any third party MySQL repair software. MySQL repair software is very helpful in most of the corruption cases and repair table up to ninety nine percent. These software are safe in nature and don't harm your database.

Top three MySQL database repair software: Top three MySQL database repair software are stellar phoenix database recovery for MySQL, recovery for MySQL, recovery toolbox for MySQL.

How to Restore Corrupt MySQL Database Tables

MySQL database is a registered product of sun Microsystems. It is platform independent in nature that means a database administrator can install MySQL database on different operating systems like windows, Linux, and others. MySQL database uses tables for storing the data and records. Sometimes these tables get corrupted by the several reasons like virus attach, software malfunction, improper system shutdown, hardware problem, and so on. When you are trying to repair corrupt mysql database by “mysqlicheck” command but unable to fix then you can try lower level of mysqlicheck command.

You can use lower level of mysqlcheck command in following manner:

Stop Server Process: To use lower level command of mysqlicheck, you should have to stop the server process. You can do it by using service MySQL stop query.

Find Data Files: After end of the server process, you will have to find the data files where tables store. The data files usually present in var/lib/mysql/database_name.

 Now, you can run the given following command against the table.

myisamchk -r -v -f --sort_buffer_size=128M --key_buffer_size=128M /var/lib/mysql/database/table.MYI    

Now, corruption in the mysql table has been fixed

Replace Database: You can replace corrupt database/table with repaired database/table.

Note: In most of the cases, above inbuilt command is able to fix the corruption in tables but sometimes fail to repair. Most of the database administrators recommend to try third party MySQL database recovery software to fix corruption in the MySQL table.

PS: Always try demo version of any software before purchase.

How to Repair Corrupt Table of MySQL Database?

MySQL database has tables to store data. All Big and small organizations use MySQL database for storing the employee's records.  A database administrator can do various operations on table like insert, delete, retrieve, arrange, and many more. Tables are very prone to corruption, there may be various reasons for corruption like Unexpected server shutdown, faulty hardware, virus attach, and more.

Let’s consider a scenario, wherein a database administrator is trying to access MySQL database table and got below error message.

“undef error - DBD::mysql::db selectrow_array failed: Table 'emplyee' is
marked as crashed and should be repaired [for Statement "SELECT LENGTH(thedata)
FROM attach_data WHERE id = ?"] at height/Formula.pm line 344
Height::Attachment::datasize('Bugzilla::Attachment=HASH(0x9df119c)') called ” 

The above error message states that employee's table is corrupt and need to repair.

How to Fix: A database administrator can fix the above error message by the two methods.

1. Repair Table using PHPMyAdmin
2. Repair Table using myisamchk command 

1. Repair Table using PHPMyAdmin: If you have installed PHPMyAdmin on your MySQL server then use this method and follow the following steps:

• Login to PHPMyAdmin
• Choose database that has corrupt table.
• Go to the main panel.
• Go to the list of tables and select the table that you want to repair.
• And finally click on the “Repair Table” button.
• You have done it.

2. Repair Table using myisamchk Command: If you have not installed the PHPMyAdmin on your MySQL server then you can repair corrupt MySQL table via storage engine of MySQL database. Run below script of myisamchk command with r option to repair the corrupt MySQL database table. 

# myisamchk -r profiles.MYI
recovering (with sort) MyISAM-table 'profiles.MYI'
Data records: 80
Fixing index 1
Fixing index 2
Fixing index 3

Last Minute: Sometimes, PHPMyAdmin and myisamchk command are not able to fix badly corrupt MySQL database. At this time, it is recommended you to try any third party MySQL database repair software to fix the corrupt table.

Note: Always try free demo version of any software before complete download.