Wednesday, November 25, 2009

Delete Operation resulting in MySQL Table corruption

As the name suggests, Delete statement is used to delete selected or all rows from the table. The statement also returns a count of all the rows deleted by the user. However, the user always needs a delete privilege to perform delete operation. While the delete operation is safe and easy to perform, it can also result in corruption of the MySQL table. In such situations, the data stored in the table becomes inaccessible and you receive an error message. To overcome such error message and access the data stored in MySQL table, an updated backup always acts as a boon. But if you have not maintained any database backup or back falls short to restore the required data, then you will need to use efficient third-party MySQL Repair application.

Consider a practical scenario, where you run a delete query to delete some records. However, instead of query deleting the records, below error message pops up stating that the table has been corrupted:

“InnoDB: Failing assertion: fil_page_get_type(page) == FIL_PAGE_TYPE_BLOB...”

After the above error message appears, the records stored in the table become inaccessible.

Cause:

Two reasons responsible for the occurrence of the error message could be buggy BLOB handling code or installation of wrong version of MySQL database server.

Resolution:

To completely resolve the above error message and to access the data stored in the table, you will need to follow the below steps:

1.Run check table on corrupt table
2.Then run repair table command on that table
3.Try and re-run the Delete command.
There is still a slight possibility that your table still remains corrupted after following the above steps. For successful and complete repairing of table, you will need to use an advanced MySQL Recovery software. Such tools use powerful repairing methods to provide absolute MySQL Repair in all situations. With interactive user-interface, these tools are easy-to-understand without any prior technical knowledge. These non destructive tools ensure safe repair of database without making any changes in the original MySQL database.

No comments:

Post a Comment