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