Friday, April 30, 2010

Missing or Deleted .frm File May Cause Table Corruption in MySQL

MySQL Server database stores data dictionary information in the .frm files that it stores in database directories. On the other hand, InnoDB storage engine keeps information in individual data dictionary in tablespace files. When you move the .frm file or the server gets crashed in halfway of any data dictionary procedure, locations of .frm files might end up out of the synchrony with locations recorded in InnoDB inner data dictionary. In such circumstances, the database may become inconsistent and inaccessible. At this point, you need to opt for MySQL Recovery to gain access of your valuable data in MySQL database.

The out-of-sync data dictionary situation can be indicated by various symptoms. One symptom of this problem is that the “Create Table” statement does not work. When this situation occurs, you should check the error log of server. If the error log indicates that table already exists in InnoDB internal data dictionary, there is an orphaned table in InnoDB tablespace files, which does not have corresponding .frm file.

You are allowed to drop orphaned table, using the steps given in error message. If you are unable to run “Drop Table” command successfully, the issue may be caused by name completion in MySQL client. To sort out this issue, start MySQL client with -skip-auto-rehash option and try using Drop Table command.

Another symptom of the out-of-sync issue with data dictionary is the error message that MySQL gives when you try to open a database table:

“ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1.”
In error log of MySQL, you may find the below message:
“InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?”

This behavior of MySQL database indicates corruption to the database tables and you need to restore them from backup to Repair MySQL Database.

In the else case, you can use third-party MySQL Repair software. These applications are able to repair and restore damaged MySQL database table in original state, without altering original data and structure. The absolutely Recover MySQL database through high-end scanning techniques, with all of the database objects.

MySQL is a powerful application to restore all of the MySQL database objects such as table, tablespace, queries, report, form, view, and more. The software is designed for MySQL 5.x and 4.x. It works well with both InnoDB and MyISAM storage engines of MySQL.

1 comment:

  1. For working out present condition you may use mysql recovery. It has many various features such as working under all Windows OS, using modern methods of recovering mysql info.