How to Ward off Table Corruption Problems in MySQL

Database corruption is a problem that may be lurking around the corner and will only come to your notice once you hit the corrupt block. As any other database, corruption may hit your MySQL databases making millions of corporate data records inaccessible. Finding corruption in MySQL tables is painstaking. You may expect queries to stop working or showing inconsistent outcome. When you start experiencing such issues, you need to check your tables for consistency. In the event of MySQL database corruption, the below given errors may show up intermittently on your screen:

Discussing MySQL database corruption and an effective resolution for MySQL Recovery

You might have faced circumstances wherein you require transferring your MySQL databases to some other SQL Server. In such situation, it is necessary to create a database dump, which would comprise the structure of database tables and probably a list of SQL statements. This dump can be used for backup purposes in any distressing event of database corruption or inaccessibility. In MySQL, a database is dumped using the 'mysqldump' client. Sometimes you face problems while performing this operation. This unusual behavior is normally caused due to database corruption. As a consequence, you might lose access to your precious database components. To overcome this problem, you need to go for MySQL recovery through a reliable third-party utility.

For instance, when you attempt to dump one of your important MySQL databases, you may receive the following error message on your screen:

“[root@www diaendomet]# mysqldump -ucojjohealth -p --all-databases >

alldatabases.sql

Enter password:

mysqldump: Got error: 29: File '../diaendomet/users.MYD' not found

(Errcode: 2) when using LOCK TABLES”

Cause:

The above error occurred because of corruption in your MySQL database. The database may get corrupt due to reasons, such as virus infections, application errors, operating system bugs etc.

Corruption will make all the database components inaccessible and you may face a critical data loss situation. In this case, a backup will help to restore all the lost or inaccessible data. However, if the backup is missing, follow the below given resolution to recover MySQL database.

Resolution:

• Try the procedure described below to resolve the problem-
• Stop the MySQL server by killing a process named 'mysqld'.
• Switch to the directory '$MYSQL_DATA_DIR'
• Run the 'myisamchk' utility as follows:

myisamchk */*.myi
This will repair all the tables present in the directory.

• Now, start the server and try to dump the database again using the 'mysqldump' utility.

If the problem still persists, you should take help of a MySQL database recovery software. These software are backed by efficient scanning algorithms to perform a complete recovery of the corrupt MySQL database. They can effectively repair and retrieve all the damaged database objects, including tables, relations, keys, constraints, queries etc.

Free download MySQL recovery software from here:
http://www.mysql-database-recovery.com/free-download.php

* Free Download offers free evaluation of the software & preview all the lost files and folder that can be recovered by the software.

The Safe Way to Recover MySQL Database

MySQL is a relational database application, which is widely used for many web based projects. Like any other database application, MySQL also stores various important data. Hence, any problem with the database, results in the complete inaccessibility of the valuable data, which may lead to huge business loss. However, there are many MySQL recovery software that can help you deal with the MySQL problems and recover your precious data back.

MySQL runs as a database server and provides multi-user access to many different databases. For its usability and features, this application is used by many big World Wide Web products, such as Google, Wikipedia, Facebook etc. However, there are occasions, when MySQL gets corrupted and all the data in it becomes completely inaccessible.

There are thousands of such reasons behind possible MySQL corruption. Below listed, are some of the most prominent ones:

1. Unexpected MySQLD (MySQL Server) shutdown
2. Operating system corruption
3. Virus or Malware infection
4. Corruption of files: ".myd", ".frm" and ".myi"
5. Damage to the Metadata structure
6. MySQL bugs
7. Damage to the "ibdata" file
8. Configuration problem etc.

In all the above situations, your MySQL becomes completely inaccessible until it is being repaired by any effective MySQL recovery application.

These software are developed with many fine search algorithms that can scan the whole drive and gather every detail about the inaccessible MySQL database. These powerful utilities are able to repair the database and retrieve the data even in case of severe corruption.

Before running the repairing utility, you need to handle the drive with extreme care. The drive must be guarded against any further corruption or overwriting of the MySQL database in it. Moreover, the software selection is very crucial, as you need to ensure the read-only property of the software, you are using.

However, most of the available MySQL recovery software are completely risk-free in nature and you can run them on your precious database without worrying about the safety of the data in it. Apart from this, these utilities are developed with detailed instructions based graphical user interfaces, which do not require any further assistance in using them.

Stellar Phoenix MySQL Recovery is a wonderful utility to recover MySQL database safely. The software can help you to retrieve your tables, views, relations etc. from both InnoDB as well as MyISAM database engines. This software is developed to work on MySQL 6.x, 5.x, 4.x, 3.x and is compatible with almost all recent Windows operating systems.

Mark willium is an Internet interpreter doing research on the application, that recover corrupted MySQL database and its objects like tables, views, relations and many more.

For More Details about MySQL database recovery software Visit http://www.mysql-database-recovery.com/.

Article Source: http://EzineArticles.com/?expert=Mark_Willium

Article Source: http://EzineArticles.com/6192953

A small analysis on MySQL database corruption and the possible resolution

MySQL is a reliable and higher secure relational database management system that was developed to deal with much bigger databases. Its improved performance, high speed and connectivity make it a better client-server application that has a support for different back-ends, client programs, and Application Programming Interfaces (APIs). But one often faces corruption issues while working with MySQL databases. The corruption may occur due to any reason, such as quitting application in the middle of a write operation, improper system shutdown, hardware issues etc. All these reasons may prevent you from accessing your important databases tables and other objects in the corrupt MySQL database. To recover back all your inaccessible and valuable data in such situations, you need to go for MySQL recovery through a reliable third party utility.

For instance, you may get an error message reading:

"Table './confluencedb/EXTRNLNKS' is marked as crashed and should be repaired"

Causes:

The possible reasons for this error message are-

You may have a limited disk space on the volume where your database is stored
Corruption in some database tables

Any type of corruption in your database may render your database inaccessible. Here, you can use a backup to recover MySQL database and get all your vital data back. You may still try to repair your corrupt database table by following the resolution steps mentioned below.

Resolution:

Follow these steps to repair your MySQL database-
Open up the Control Panel.
In 'MySQL' section, click 'REPAIR' icon against your database's name.

The above steps may help you repair all the corrupt tables in your MySQL database. If the problem still persists, you should take help of an efficient MySQL database recovery tool. These software can easily recover various database objects including tables, views, keys, constraints etc. from your corrupt MySQL database. They support both the MySQL database engines- MyISAM and InnoDB.

Stellar Phoenix MySQL Database Recovery is a comprehensive and highly secure tool that repairs damaged or corrupt MySQL tables created in MySQL 3.x, 4.x, 5.x, and 6.x versions. It enables you to preview all recoverable objects of corrupt MySQL database without even requiring a MySQL installation. It compatible with Windows 7, Vista, Server 2003, XP, and 2000.

Resource: http://www.americanchronicle.com/articles/view/218783

Incorrect Information in .FRM File of InnoDB Table

MySQL organizes all the data as tables, irrespective of storage engine used. If you are using MySQL with InnoDB tables, these tables might get corrupt due to hardware faults, unexpected power failure, MySQL code errors, kernel bugs and other similar reasons. In such cases, InnoDB will typically give some errors indicating table corruption. As a data restoration source, you will need to use your latest database backup. But in case if backup fails to restore required information or doesn’t exist, you should scan your damaged database using third-party MySQL Repair or MySQL Recovery tools.

You might encounter the similar error message while accessing an InnoDB table:

“#1033 - Incorrect information in file: '"table name" .frm”

MySQL crashes after you receive this error message.

Cause: You receive this error message if InnoDB table is corrupted or damaged. An InnoDB table is stored as three files: .FRM (database table definition), .MYI (Index file) and .MYD (table data). This particular error occurs if .FRM table is damaged or corrupted.

Solution:
To solve such problems, you should follow these steps:

Check the table for corruption errors. To do so, you need to use CHECK TABLE command with options like QUICK, EXTENDED, CHANGED etc. that can be used on the basis of check required. The basic syntax is: "CHECK TABLE tablename [options]" If table corruption is the issue, repair it using REPAIR TABLE command. Before using the command, you should make sure that your disk contains about twice as much disk space as of damaged table. REPAIR TABLE command can be used with options like QUICK and EXTENDED with the following syntax: "REPAIR TABLE tablename [options]" If above measures cannot repair the damaged table, check your backup status. If valid backup is available, delete the damaged table and restore from backup. If no valid backup is present, you can make use of MySQL Recovery software. These are advanced tools to analyze and repair damaged MySQL tables. Using powerful scanning, these tools are also capable of maintaining database integrity. Moreover, these MySQL Repair applications come available with rich user interface.

Stellar Phoenix Database Recovery For MySQL is an optimum tool to repair and restore damaged MySQL tables. It supports recovery for both InnoDB (.ibdata, .ibd and .frm) and MyISAM (.myd, .myi and .frm) database storage engines. The software supports MySQL 5.x. This non-destructive MySQL Recovery tool can restore all database objects, like tables, primary key(s), relations and others. It can recover databases of Windows and Linux platforms and is compatible with Windows Vista, XP, 2003, and 2000.

Article is taken from: http://hubpages.com/hub/Incorrect-Information-in-FRM-File-of-InnoDB-Table

MySQL recovery utility for any instance of corruption

In order to start your MySQL server, you used to type the whole path like: "C:\mysql\bin\mysql123" in your DOS prompt. However, sometimes, the MySQL server fails to start with this usual command and the reason may be any possible case of database corruption or damage. In such a case, you cannot be able to access your database and need any MySQL repair utility to repair the troubled MySQL database and recover the data thereof.
In case of any corruption, you may encounter an error message at the start of the MySQL server, which may be as below:

"Default storage engine is not available"

Moreover, in order to resolve the error, if you reinstall the MySQL server without removing the data directory, the server starts normally. However, to your utter dismay, while accessing the any database table, you may encounter another error message on the screen as below:

"ERROR 1146 (42S02): Table 'Tablename' doesn't exist"

Note: Here, the 'Tablename' is the name of the table, you have tried to access.

The above error message appears each time you try to access the table. Even, you cannot be able to resolve the error with the 'mysqlcheck' utility.

In general, the reason behind the occurrence of the error messages is the corruption of the MySQL database, which can have been caused due to an improper system shut down, virus/malware intrusion or any other application malfunction etc.

Resolution:

In order to resolve the problem, you need to repair MySQL with the following command:

Repair Table Tablename
However, with severe corruption, the above command does not able to repair the database and you need to look for any valid backup to restore your seemingly lost MySQL database. If you fail to restore the database from the backup, then, you need to run any MySQL recovery software to recover back the database efficiently.

These read-only utilities are designed with advanced algorithms to repair MySQL database and recover the data safely. Moreover, with highly interactive graphical user interface, you may never find any problem in operating the software.

Stellar Phoenix Database Recovery For MySQL is an extremely powerful utility to repair the corrupt database and recover MySQL database with all its internal structure being intact. The software works well with almost all Windows versions, including Windows Vista, XP, 2003, 2000 and is absolutely easy to use on the parts of experts to normal users.

Advanced Utility to Repair and Restore Corrupt MySQL Table

MySQL database has many commands to systematically maintain its data. All of these commands have different syntax. Out of the several commands, MySQL database has two most common commands that are Optimize and Flush. The Optimize command of MySQL database is used for the defragmenting the database files and use the free space. The Flush command of MySQL database reloads and clears all the internal patches used by database. You can use Flush statement with RELOAD privilege. The above mentioned two commands have different functionalities and features. Using MySQL commands one after the another, your MySQL database table can get corrupted. In such scenarios, your data saved in the table will be inaccessible. For accessibility of table data, you should use an advanced MySQL repair software to fix the problem.

Consider a practical scenario wherein, you run the Optimize command and consequently run the Flush command, after doing this you may got an error message and the data stored in the mysql table become inaccessible.

Root Cause of Problem:

The main cause of the MySQL table corruption is you have used Optimize and Flush command concurrently.

Way to Resolve:

The safe and effective way to repair MySQL table is to run the given below command.

Check corruption limit of mysql table using given statement:
mysqlcheck [options] dbname tablename [tablename2... ]
Repair damaged table using given below statement:
repair table tablename [,tablename1...][options]

The above mentioned steps enable you to repair the mysql database table and fix the problem. But if, these steps fail to fix the corruption issue, that means table is not repair by the command. In such scenarios, you can used the backup of your database for restoring the table. If in case, backup is not updated then only way to repair your corrupt table is by using a third party MySQL Recovery Repair software.

These third party MySQL recovery software used advanced algorithm to repair the corrupted MySQL table. With highly graphical user-documentation, the software is self explanatory and does not require any technical understanding.

Recover Crashed Tables in MySQL Database

MySQL is a popular database management system, a most widely used for web databases, offers efficient techniques to ensure absolute integrity of the database. When you start MySQLD (MySQL Server), it automatically examines all the database tables for integrity. MySQL Server also fixes the tables if they are marked as ´not closed properly´ or ´crashed´. It backs up all the corrupted database tables and makes their entries in the error log. Though, it works in a number of cases, but cannot necessarily fix all the corruption issues and fails to recover MySQL database.

The MySQLD technique cannot repair MySQL database if it is severely damaged. As a practical scenario, where MySQL Server cannot handle the table corruption, you may encounter the below error message-

"Got an error from thread_id=1, mi_dynrec.c:368"

The above error message occurs when you start the MySQL server for accessing the database records that are stored in the MyISAM (MySQL database engine). After initialization, the server examines for integrity of all database tables and attempts to repair the damaged ones. MySQL Server logs this error in error log.

This problem occurs every time you start MySQL Server database, until you repair or remove the damaged tables. All the records that you have saved in MyISAM table become unusable after this error.

Root of the problem:


There are numerous reasons of MySQL database corruption, such as virus infection, unexpected system shutdown, application malfunction, and subsequent editing of the database. MySQL server cannot recognize the database after corruption and thus you face this problem.

Solution:

In order to perform MySQL database recovery in such situations, follow the underwritten steps-

Check the extent of damage to the table using the CHECK TABLE command. Once the corrupt table is identified, use REPAIR TABLE command to repair MySQL database table.

If you have an updated and valid backup of the damaged table, restore it. Backup is the best way to perform MySQL recovery.

In case you do not have a proper backup of your database, you are required to look for alternative MySQL repair solution, which is to use MySQL database recovery software.

MySQL recovery software are particularly designed to repair MySQL database with highly-advanced scanning techniques. They safely repair MySQL database with read-only and non-destructive conduct.

Stellar Phoenix Database Recovery for MySQL is the comprehensive SQL database recovery software that works in all corruption scenarios, with both MyISAM and InnoDB storage engines.

How to repair MySQL database when the Error 145 occurs

In MySQL databases, the MyISAM table is the default database engine, which provides a variety of functions to manage fields and indexes. These tables are best used for table locking, which makes it very useful while performing real-time transactions on the websites. However, when these tables get corrupt due to any random reason it makes the trading done on the web very insecure. In such cases, you should try to repair MySQL table using some appropriate methods. If none of the methods succeed, then I would recommend you to use a third-party MySQL Repair software to repair MySQL database.

Taking a practical case into consideration in which the following error message is displayed while you are working on a MyISAM table in the MySQL database:

"MySQL error code 145 = Table was marked as crashed and should be repaired"

Cause:
The root cause of this problem is that the MyISAM table has corrupted as there is no more space in it to hold more data.

Resolution:

To overcome this MyISAM table corruption, you need to perform MySQL repair. For this, you should perform the following steps:

Create a backup copy of the database in case something goes wrong during the MySQL recovery.
Stop the mysqld server by killing the process in the Task Manager.
Run the following command to repair the index file to perform quick MySQL recovery:
myisamchk -r -q
If that does not work, then try the following command to perform a detailed scan and repair, if any, of the MyISAM table.
myisamchk --safe-recover
if the previous method also fails, then create a new blank database using the following commands:
shell> mysql db_name
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit
copy the contents of problem database in the new one.
Restart the mysqld server to check if the problem has been resolved or not.

If the problem is still not resolved, then you must use a third-party MySQL repair software to repair MySQL database. The key features of these MySQL recovery tools are the use of powerful but safe scanning algorithms in a user friendly interface thereby ensuring safe and easy recovery of MySQL database.

The best MySQL repair software that I would suggest you in this case is Stellar Phoenix Database Recovery for MySQL, which repairs MySQL files created in MySQL 3.x, 4.x, 5.x, and 6.x versions. With the ability to recover MySQL components such as tables, views, primary keys, etc, this MySQL recovery tool supports both InnoDB and MyISAM database engines. It is compatible with Windows 7, Vista, Server 2003, XP, and 2000.
Article Source: http://www.articlesbase.com/data-recovery-articles/how-to-repair-mysql-database-when-the-error-145-occurs-3888843.html

About the Author

My name is Mark Willium, I have done Ph.D in computer science and currently doing research on how to repair mysql database.

How to repair MyISAM tables in MySQL 5.0

MyISAM is the default storage engine in MySQL server. It consists of three types of files: .myi, .frm, and .myd. Of these three, the MYI file is the index file that stores a counter in its header. This header is used to signify whether a particular table was closed properly or not. Sometimes, while using the myisamchk command if you get an error message that means the table has corrupted. Such problems can occur due to various reasons such as power outages, abrupt system shutdown, mysqld process getting killed in between a write operation, etc. You should use in-built workarounds to repair the table. If you are not able to do this, then you should use a third-party MySQL repair software to recover MySQL database.

Consider a scenario wherein you have installed MySQL 5.0 in your system. One day, when you try to run the myisamcheck command to check a database table, it fails to run. An error message is displayed, that is:

“clients are using or haven't closed the table properly”

Cause:

The root cause of this error could be either of the following reasons:

You copied a MyISAM table without using the Lock Table or Flush Tables command.
MySQL crashed while you were about to commit a change in the database table.
Either myisamchk --recover or myisamchk –update-state updated a table while it is used by the mysqld process.
Many mysqld servers are referring to the same table at the same time and one of them performed Repair Table or Check Table command.

For details how to overcome from above error click here

Repair your Corrupted MySQL Database

Yesterday one of our computer’s user partition filled up automatically that caused database corruption in the mysql database. After freeing the space from the partition, we ran some commands to repair and recover mysql database. Here is the list what we did yesterday:

1.cd into your directory where your database files are stored. In my case it was in the /usr…/data/mysql/ folder.

2.Then We run this command:
myisamchk *.MYI | grep -3 –color corrupted

3.After running above command this should give you some output on the current state of your database files & indicate which file is corrupted and need to repair. This also gives you the next command to run on the line that is:
Fix it using switch “-r” or “-o”

4.So that is we did
myisamchk -r file.MYI

5.Your database should be repaired

Recovering MySQL Corruption in Bugzilla Database

Bugzilla is a web-based bug tracking and testing application that is used by many software development companies. It extensively various databases including MySQL for its backend support especially because both of these applications are freely available. Although this tool runs pretty fine in most cases, it can get stalled when the some of the MySQL tables get corrupt. In such cases, you are unable to perform various tasks on the database. You can use the 'myisamchk' command with different parameters to repair MySQL database. If it does not work, then you should use a professional MySQL recovery software to recover MySQL database.

Discussing a similar situation in Bugzilla, consider you get the following error message while generating a bugs report:

"undef error - DBD::mysql::db selectrow_array failed: Table 'attach_data' is
marked as crashed and should be repaired [for Statement "SELECT LENGTH(thedata)
FROM attach_data WHERE id =?"] at Bugzilla/Attachment.pm line 344
Bugzilla::Attachment::datasize('Bugzilla::Attachment=HASH(0x9df119c)') called"

Cause:

As can be inferred from the message itself, the 'attach_data' table is corrupt, which is why you are getting this error message in Bugzilla.

Resolution:

To resolve this error message and repair MySQL database, you should perform the following steps:

You should find out all the corrupt tables in the MyISAM database using the following command:

# myisamchk /var/lib/mysql/bugs/*.MYI >> /tmp/myisamchk_log.txt
The /tmp/myisamchk_log.txt file will contain the list of all the checked tables as displayed:
Checking MyISAM file: user_group_map.MYI

Data records: 182 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1

Repair the table using the following command:

# myisamchk -r profiles.MYI
You should do a check and repair using the myisamchk command as displayed:

# myisamchk --silent --force --fast --update-state /var/lib/mysql/bugs/*.MYI

The 'myisamchk' tool is competent enough to check and repair the MyISAM table. But some instances have been noticed where the table is not repaired using this tool. On such occasions, the most worthwhile option is to use a third-party software to perform MySQL repair. These MySQL recovery tools guarantee MySQL repair without any damage to the original tables.

One such software is Stellar Phoenix Database Recovery for MySQL, which repairs MySQL files from all instances of logical corruption. Can be used on various Windows platforms including Windows 7, Vista, Server 2003, XP, and 2000, this MySQL recovery utility recovers MySQL tables created in MySQL 3.x, 4.x, 5.x, and 6.x versions.

Source: http://goarticles.com/article/Recovering-MySQL-Corruption-in-Bugzilla-Database/3759302

Repairing MySQL table to ensure proper functioning of MythTV

MythTV is a home entertainment application, which is freely available as a open source solution for live streaming programs, and converts into a computer with the necessary supporting hardware components. This application is able to run on different operating systems such as Linux, Macintosh, FreeBSD, etc and primarily uses MySQL database as the backend to store the recordings. Having started in 2002, this application has undergone several developments and now is a competent combination of a TV and a computer. Having said that, at times you may face some issues while operating on the MythTV. Sometimes these issues may be because of corrupt database that can appear because of several reasons including virus infections, abrupt or unplanned system shutdowns, etc. You should try to fix such issues by using in-built MySQL methods failing which the use of a third-party MySQL repair software to repair MySQL database is a must.

Let us consider a situation in which you are watching MythTV but are unable to fast forward the programs. You tried to check the 'mythconverg.recordedseek' table to see if the data is alright by retrieving the records but you get the following error message repeatedly:

"MySQL error code 145 = Table was marked as crashed and should be repaired"

Cause:

The most prominent cause of this issue is that the database for the MythTV programs has damaged. Due to this, you are unable to properly operate MythTV.

Resolution:

To resolve this problem by recovering MySQL database, you should perform the following steps:

First of all, you should check the table for errors using the following command: CHECK TABLE recordedseek; You should run the 'REPAIR TABLE' command on the damaged table using the following syntax: REPAIR TABLE recordedseek; Then, rebuild the 'mythcommflag' using the following command: mythcommflag --rebuild -f

After running the aforementioned commands, try to reproduce the steps that resulted in the error. It should run perfectly now. However, if the problem is still persisting, then you should repair MySQL database using a third-party MySQL database recovery software. These tools are absolutely safe to use that do not damage the existing database while performing the database recovery.

The most recommended software the repairs different MySQL database (InnoDB and MyISAM) engines is Stellar Phoenix Database Recovery for MySQL. With the ability to repair different types of MySQL files such as .myd, .frm, .myi, .ibdata, and .ibd, this software repairs several objects including tables, view, database keys, etc. Supporting MySQL 3.x, 4.x, 5.x, and 6.x versions, this MySQL repair tool is compatible with Windows 7, Vista, Server 2003, XP, and 2000.


Resource: http://goarticles.com/cgi-bin/showa.cgi?C=3571219