MySQL Table Corruption Due to Error While Using an Optimize Table

MySQL OPTMIZE TABLE command is used to de-fragment a MySQL table and thus reclaim its unused space. You generally use this command when you delete a large part of the particular table or make several changes to it with variable-length rows. But while the OPTMIMIZE TABLE command is running, the server should run seamlessly. Any interruption while this process is running could cause database to corrupt. In such situations, you either require using your recent data backup or commercial MySQL Database Recovery tools.

As an example, you run OPTIMIZE TABLE command on table 'A' and MySQL Server process gets unexpectedly killed. The next time, when you start the database, you find table 'A' giving corruption errors. Such errors also occur even if use FLUSH TABLE (used to clear and reload internal caches used by MySQL) command before using OPTMIMIZE TABLE command.

Cause

MySQL database table is corrupted due to unexpected interruption while OPTIMIZE statement is de-fragmenting the database table. MySQL could not read such tables and gives corruption errors.

Solution

To fix such corruption errors, you should follow these steps in order:

• Check database table for consistency. To do so, execute CHECK TABLE command with following syntax:

CHECK TABLE table_name

Before running the command, make sure that you are connected to the server. The command can be used with options like QUICK, MEDIUM, FAST and CHANGED etc. If using MyISAM, you can also use myisamchk command-line utility.

• If the above check reports corruption, next you should repair the database table using following command:

REPAIR TABLE table_name

This command accepts parameters like QUICK, EXTENDED and USE_FRM. In case of MyISAM database, you can use myisamchk utility with recovery option -r.

• You should again verify the database integrity using CHECKTABLE or myisamchk utility. If it still reports corruption, use your recent database backup to restore.

• If no clean backup is available, scan your database using third-party MySQL Recovery applications. Such tools examine the corrupted database using powerful scanning algorithms, repair it and restore it at a safe location. They provide you efficient MySQL Database Recovery through interactive interface.

Stellar Phoenix Database Recovery For MySQL is the comprehensive MySQL Recovery application to repair and restore corrupt MySQL tables. It supports MySQL 4.x and 5.x. and can recover database files for both InnoDB (.ibdata, .ibd and .frm) and MyISAM (.myd, .myi and .frm) database storage engines. This software restores all database objects, like tables, relations, primary key(s), and others. It can recover databases based on Windows and Linux platforms and is compatible with Windows Vista, XP, 2003, and 2000.

Naveen Kadian is a self employed Internet entrepreneur and product reviewer Stellar Phoenix Database Recovery For MySQL is the premier MySQL Repair tool that can instantly repair and restore corrupt MySQL databases. It supports InnoDB (.ibdata, .ibd and .frm) and MyISAM (.myd, .myi and .frm) files. Article Source: http://EzineArticles.com/?expert=Naveen_Kadian