Wednesday, January 27, 2010

Three ways to Repair MyISAM tables


Corruption in MyISAM tables is a nightmare for almost every Database Administrator (DBA). These tables are one of the most important components to save personal and professional data, and corruption of these tables primarily result into inaccessibility of all that data. In such cases, a valid database backup allows you to restore the data and access the table records. While the backup file is mostly saved on a different storage media, the possibilities of its corruption still exist. In such cases, you will need to perform Repair MySQL Database.

Below are the few error messages that enable you to identify that your MyISAM table (employee) is corrupt:

“employee.frm is locked against change”
Or
“Can't find file employee.MYI (Errcode: nnn)”
Or
“Got error employee from table handler”

The data stored in the employee table becomes inaccessible after the above error message pops up. In addition, you receive any of the above error message each time you attempt to access the table.

Cause:

Few main reasons for the employee table corruption are improper MySQL Server shutdown, virus infection, file system damage, and human errors.

Resolution:

To resolve the above error messages and access the employee table records, you will need to follow the below measures:

You can use an in-built tool “myisamchk” to check and repair the employee table. The command-line tool scans the database components, identifies the exact cause of corruption, and repair the tables.

You can use various SQL commands to repair the employee table. These commands are CHECK TABLE, REPAIR TABLE, and OPTIMIZE TABLE.



The probabilities of employee table not being repaired by any of the above steps is very less, but still exists. Such cases occur when the table is badly corrupted. To repair the table in such situations, you will need to use an advanced third-party Repair MySQL tool.

These MySQL Repair tools provide complete repair of MyISAM tables by repairing them using advanced repairing algorithms. Such tools are completely read-only in nature and perform repair without modifying the original file. Moreover, these tools are easily understandable without any prior technical knowledge.

Friday, January 22, 2010

MySQL Database Table corrupt after running a Procedure

A procedure is a group of specific set of actions and operations to perform a particular task. These actions consist of various queries that fetch data from various tables connected to a database. While creating and running a procedure makes your work easy, it can also result into corruption of a database table. This occurs when either of the queries in the procedure is fetching records from two tables, one of which is corrupted due to virus infection. In such situations, an ideal way is to delete the virus infected database table and restore the table from an virus-free and latest database backup. However, if in case the database backup is unable to restore the required table or the backup is corrupted/damaged, then you will need to use a third-party MySQL Repair application to repair the corrupt table.

Picture a scenario, where you have created a below script to perform certain operation:

Create Procedure
Run Procedure
Drop Procedure

However, after the above script runs, you find that one of your table always gets corrupted. Furthermore, the records stored on that table become inaccessible after it gets damaged.

Cause:

The table gets corrupted if there is query that relates it with another table, which is virus-infected. To prevent the table from being corrupted, you should always scan your database components with an up-to-date anti-virus software.

Resolution:

To resolve the above error message and to repair the table, you will need to run the “myisamchk” command. While the above command repair the table in most cases, it fails to repair the tables in case of severe corruption. For repairing such tables, you will need to use an advanced Repair MySQL Database application.

Easily downloaded from the Internet, these MySQL Repair applications can repair MYSQL database tables, regardless of their extent of corruption. These tools are primarily incorporated with an interactive user-documentation so that even a novice user can perform the repair task. Such repair utilities use high-end scanning methods to repair the corrupt table and also allow you to restore the repaired table on your desired location.

Sunday, January 17, 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.

Wednesday, January 13, 2010

Fixing “Key in wrong position...” Error Message

MySQL database table, a major storage component for all MySQL users to store their records, can be corrupted due to various factors. Few of the most common include improper system shutdown, fatal virus attacks, file system damage, application malfunction, and human errors. An error message while accessing the table, clearly indicates that it has been corrupted. This error message can be a huge cause of concern in case the table contains some critical data. To overcome such table corruption scenarios, you can always use an adequate database backup. But if in case, the database backup gets damaged or is inaccessible, then you will need to repair the corrupt table. For comprehensive repair, you will need to opt for an advanced third-party Repair MySQL Database application.

Consider a practical scenario, where you attempt to open the table and an error message pops up stating that the table is corrupt. Furthermore, when you attempt to run CHECK TABLE command on that table, you receive another error message:

“Employee check error Key in wrong position at page 4096
Employee check error Corrupt”

After the above error message appears, the data stored in the table becomes inaccessible. You encounter the same error message each time you attempt to run the CHECK TABLE command.

Cause:

The above error message appears when the Employee table is severely corrupted.

Resolution:

The resolution for the above error message is restoration from an updated backup. But, there are many case studies where the database user forgets to create a database backup on regular basis. For such situations, these users you can use an advanced MySQL Database Repair application.

A third-party MySQL Repair utility performs systematic repair of corrupt MySQL tables by using effective scanning methods. The tool can be easily understood as it comes along with highly interactive user-interface and documentation. The repair application does not overwrite, delete, or modify the original data stored in the table. The software allows you to save the repaired table on default or new destination.

Friday, January 8, 2010

Repairing Corrupt MySQL InnoDB table

MySQL InnoDB table corruption or damage results in inaccessibility of all its stored records. There can be different causes for corruption in table including improper system shutdown, fatal virus hit, application malfunction, and human errors. However, the most common way to identify that your database has been corrupted is when you view an error message, instead of the records. Restoration of data from an updated backup allows you to overcome such error messages. But restoration process can only be performed in case the user has maintained a valid backup. If you have not created a backup or the backup is corrupted, then you will need to repair the corrupt database table using an advanced third-party Repair MySQL Database tool.

Consider a practical case, where you receive the below error message when you attempt to access the records saved in the MySQL table (employee):

“#1051 - Unknown table 'employee'”

After the above error message appears, the data stored in the “employee” table gets inaccessible. Furthermore, the error message pops up whenever you open the table.

Reason:

The main reason for the occurrence of the above error message is corruption of employee table.

Resolution:

The resolution for the above error message can be achieved by following steps:

1.The first steps is to add “innodb_force_recovery=4” to /etc/my.cnf configuration file.
2.Restart MySQL and dump the employee table.
3.Shutdown your database and erase the data directory.
4.Create default tables using mysql_install_db.
5.Remove “innodb_force_recovery=4” from /etc/my.cnf configuration file.
6.Restore the data from a valid backup.

The above steps fail to resolve your issue if you have not created a backup. In such situations, you will need to search for effective MySQL Database Repair tool that can repair the corrupt table. Such MySQL Repair tools are non-destructive in nature and perform repair without modifying the original data. The repair utilities employ intensive scanning methods to systematically repair the database and restore it on the user-specified destination. These tools can easily be used by both non-technical users.

Check These Related Articles :-
http://www.articlealley.com/article_1104081_11.html

http://www.articlealley.com/article_1094363_11.html