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

Rectifying Corruption Issues in MyISAM Table in MySQL 5.0.22 Database

MySQL is the most popular open-source database in the world. It has the distinction of being the only database that is used in every continent. Yes, even Antartica!!! The reasons for such popularity is its availability, performance, robustness, etc. It provides three types of database engines, MyISAM, InnoDB, and MySQL Archive. Out of these, the MyISAM table is the default table type. It is fast, simple, and allows full-text searching. Although it is reliable, it can become corrupt and, thus, inaccessible following a system crash. You should use appropriate corrective measures to repair MySQL database. However, if you are not able to do so then you should use a third-party MySQL recovery software to do so.

Consider a scenario wherein you have MySQL 5.0.22 database running in your system. You use it for MediaWiki installation that is reported as corrupt. When you try to repair the table, the following error message is displayed:

"Key in wrong position at page 4096"

You, then, quit the database and stopped the mysql process in the Task Manager and executed the myisamchk - r and myisamchk - o commands. But you were unable to resolve the issue.

Cause
The root cause of this error message is that the MySQL database is corrupt.

Resolution
To resolve this erroneous situation, you should firstly create the backup of the corrupted database. Then, you should use the following methods to repair the MySQL database:

You should use the following REPAIR TABLE query to repair the corrupted MySQL repair table tablename use_frm;

If the REPAIR TABLE query does not repair the database, then you should check for the backup. If the backup is updated and clean, then you should restore the database from its backup.

The aforementioned methods should be able to resolve the issue. However, if the problem is still there then you should use a third-party mysql repair software to restore the corrupted database. Such read-only tools are able to repair MySQL database using fast yet sophisticated scanning algorithms.

Database Recovery for MySQL is a MySQL recovery software that enables you to repair MySQL database. It is able to recover MySQL database for the MyISAM and InnoDB MySQL database storage engines. This mysql database repair tool is designed for MySQL 4.x and 5.x versions. It supports MySQL recovery for MyISAM (.myd,.myi,.frm) and InnoDB (.ibdata,.ibd,.frm) files. It is compatible with Windows 7, Vista, Server 2003, XP and Server 2000.

Advika Singh work as a freelancer and researcher repair mysql & recover mysql software.

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

Restore MyISAM tables by MySQL Recovery Software

Any corruption in MyISAM table is a terrible situation for Database Administrator. These MyISAM tables are the most important components in the MySQL Database to store personal as well as professional data, and the corruption in these tables may inaccessibility to the database. In this situation, a database backup allows you to restore the database and access the records in the table. Duplicate file (Backup) of the database is mostly stored on a different storage media, the changes of database corruption still exist. In that situation, you will need to perform MySQL database repair by the using of third party software.

Some error messages that enable you to identify that your MyISAM table (student) is corrupt:

“Student.frm is locked against change”
Or
“Got error message student from table handler”

Or
“Can't find file student.MYI (Errcode: nnn)”

The record saved in the student table becomes inaccessible after the above error message appears. In addition, you will receive any of the above three error messages each time when you attempt to access the table of student.

Cause of These Errors:

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

How to Resolve These Errors:

To resolve above three error messages in student table and access the records, you will need to follow the below procedures:

You will have to use an in-built tool “myisamchk” to find and repair the student table records. The command-line of this tool scans the MySQL database components and found the exact cause of corruption & repairs the student tables.

You can also use several SQL commands to repair and restore the student records. Some of those commands are REPAIR TABLE, CHECK TABLE & OPTIMIZE TABLE.



The probabilities repair of student table records is very less by the using of any two above methods, but sometimes exists. Such case occurs when the student table is badly damaged at that point you (DBA administrator) will have to need advanced MySQL recovery software to repair student table.

Stellar Phoenix Database Recovery for MySQL repairs of MyISAM tables by using of advanced repairing algorithms. This software is completely read-only in nature and repairs without any modifying in the original data file. Moreover, this MySQL recovery software is easily understandable without any prior technical knowledge related to MySQL.

Source: http://hubpages.com/hub/Restore-MyISAM-tables-by-MySQL-Recovery-Software