Monday, December 21, 2009

MyISAM Table Corrupted due to Improper Indexing

Indexes in MySQL are primarily used to increase the data accessing and retrieval speed of records stored in the database tables. While correct and orderly indexing of data provides the user with lots of benefits, a badly indexed table can result into corruption of tables (primarily when the query is fetching millions of records). Once the table is corrupted, the data stored in it becomes inaccessible. To surmount the effects of table corruption and to access the data stored in the table, you will need to restore the data from an updated backup (mainly maintained on some other storage media). However, in case of backup unavailability or backup falling short to restore, then you will need to search for powerful MySQL Repair software.

As a practical scenario, you have approximately 2 millions rows saved in your MyISAM table. When you run a query to view all the records stored in the table, the table crashes. Once the table crashes, all the records stored in it become inaccessible.

Cause:

The root cause for table corruption is that table is badly indexed.

Note: In most cases of MyISAM table corruption, you encounter an error message.

Resolution:

The error message and the MySQL database corruption can be resolved by following the underneath steps:
1.You will need to check the limit till the table is corrupted by using below command:
mysqlcheck [options] dbname tablename [tablename2... ]
2.Next, repair the table using the underneath command:
repair table tablename [,tablename1...][options]

The above two steps repair the corrupt MyISAM table in most corruption scenarios. However, there might be situations where the table does not repair, then it means that it is severely corrupted. To repair the table in such situations, you will need to opt for effective MySQL Database Repair software.

A third-party MySQL Repair tool is an easy to understand repair application that systematically repairs MySQL database tables with ease. Such tools use advanced scanning methods to scan and repair the corrupt table. In addition, these applications provide safe repair by not deleting or modifying the original content.

No comments:

Post a Comment