Sunday, December 27, 2009

Table Corruption after running of Flush and Optimize Commands

There are numerous MySQL commands (with different syntax) that help you to systematically maintain your database. Out of the various commands, the two most common MySQL commands are Flush and Optimize. The Flush command reloads or clears all the internal patches used by MySQL. For using the Flush statement, you will need to have the RELOAD privilege. The Optimize command is used to defragment the database file and to reclaim the unused space. While both the commands hold different functionalities, using these commands one after the another can result into corruption of table. In such situations, the data stored in the table becomes inaccessible. For complete accessibility of table records in such situations, a fundamental way is to restore the data from an updated database backup. But if in case, the backup file is unavailable, then you will need to use advanced MySQL Repair to repair the table.

As a practical case, you run the command “Flush tables” and then immediately run “Optimize table” command. After doing so, the table on which the commands are running gets corrupt and the records stored in it become inaccessible.

Cause:

The root cause for table corruption is that these commands do not wait for each other to perform there particular tasks.

Resolution:

An easy way to repair the corrupted tables is to run the below commands:

1.Check the corruption limit of the table using underneath statement:
mysqlcheck [options] dbname tablename [tablename2... ]
2.Repair the corrupt table using below statement:
repair table tablename [,tablename1...][options]

The above steps enable you to repair the table and bring back the table in working state. But if in case, the above steps fail to resolve the corruption, then it means that the table is beyond the capabilities of Repair command. In such scenarios, the only way to repair your table is by using advanced MySQL Database Repair utility.

These Repair MySQL Database applications use powerful scanning and repairing procedures that enable you to repair the table completely. With highly graphical user-documentation, the software is self explanatory and does not require any technical understanding.

No comments:

Post a Comment