Monday, April 19, 2010

Fixing “Tablespace Error” Post power failure

A tablespace in MySQL database is a storage destination where the data related to database objects (tables, indexes, etc.) is kept. A MySQL database consists of at least one tablespace, which can also be increased if required. While these tablespaces increase the performance of the database, corruption in these logical storage units can result in unmountability of database, further leading to inaccessibility of database records. One of the main reasons for the corruption of a tablespace is improper shutdown of MySQL Server due to power failure. In most of these situations, you receive an error message. For complete accessibility of database records and to surpass the error message, an ideal way is to restore the data from an updated backup. But, the possibilities of backup not being able to restore the desired content always persists. In such cases, you will need use an advanced MySQL Repair tool.

Consider a practical scenario, where your running MySQL server experiences a power failure. The next time you attempt to mount your database, you receive the below error message:

“InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace.”

After the above error message pops up, the database becomes unmountable. This, further results in inaccessibility of all the records. In addition, the above error message appears every time you try to mount your MySQL database.

Cause:

As discussed in the error message, the InnoDB database tablespaces are corrupt due to improper server shutdown.

Resolution:

To resolve the above tablespace error message and to access the database, you will need to use follow the below steps:

Restart MySQL using innodb_force_recovery = 4 option.
Restart MySQL using innodb_force_recovery = 6 option.
Run 'reiserfsck' to fix corrupt file system.

However, if the above resolutions fail to resolve the above error message, then you will need to use an effective MySQL Repair tool to repair the database. Such Repair MySQL Database utilities can be downloaded from the Internet and used even by a novice user.

No comments:

Post a Comment