Repairing MySQL database when Error 1146 indicates table corruption

MySQL database is one of most used open-sourced database, which is used by numerous users over the world. Being a free, reliable, secure, and fast-paced database system, it is highly preferred as the backend support for numerous high-traffic and popular web-based applications such as Flickr, Google, You Tube, Yahoo!, etc.

For different types of storage needs, it offers different kinds of database engines such as InnoDB, MyISAM, etc. However, at times these tables do get corrupt due to various reasons including virus infections, abrupt system shutdown, bugs in the current MySQL version, etc. In such cases, there can be different methods to provide the workaround. If these methods do not work, then you should use a third-party MySQL repair software to repair MySQL database.

Consider a situation in which you start the MySQL 5.1 server by starting the 'mysqld' process. However, the following error message is displayed and the server does not start:

?Default storage engine is not available.?
You reinstall the MySQL server without removing the data directory. Next time, when you try to access any MySQL database table, you are unable to do so. The following error message is displayed this time around:

?ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist?

Here, 1146 refers to the error code.

42SO2 refers to the five-character SQL state.

Cause:

Such erroneous situation occurs when the MySQL table that you are using is corrupt.

Resolution:

To repair MySQL database in such case, you should perform either of the following methods:

* Use updated backup: First of all, you should check if there is any clean and updated backup of the MySQL table. If yes, then restore the table with the backup.
* Use Repair Table tool: If the backup is not up-to-date, then you should try to repair MySQL table using the Repair Table tool with either 'QUICK' or 'EXTENDED' repair clause. This tool can be used in the following syntax:

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE

tbl_name [, tbl_name] ...

[QUICK] [EXTENDED] [USE_FRM]

The situation will be resolved using the aforementioned solution, however if the problem persists then this situation calls for the use of a third-party MySQL recovery software. These MySQL repair tools are highly interactive and the use of non-destructive repairing mechanisms enable easy and safe recovery of MySQL tables.

In such situations, I would suggest you to use Stellar Phoenix Database Recovery for MySQL, which works for MySQL 3.x, 4.x, 5.x, and 6.x versions. With the ability to repair MySQL database created for various database engines including MyISAM and InnoDB, this MySQL repair utility recovers several objects like tables, views, primary keys, etc. Compatible with Windows 7, Vista, Server 2003, XP, and 2000, this MySQL recovery software recovers MySQL files including .ibdata, .ibd, .myd, .myi, and .frm. About Author

My name is Mark Willium, I have done Ph.D in computer science and currently doing research on how to recover and repair corrupted mysql database. During research i found a third party repair mysql database softwqare to repair corrupted mysql database.

Article Source: http://www.1888articles.com/author-mark-willium-32501.html

View the original article here