Softpedia
 

NEWS CATEGORIES:



NEWS ARCHIVE >>
SOFTPEDIA REVIEWS >>
MEET THE EDITORS >>
Home > News > Webmaster > Tips and Tricks

September 29th, 2007, 10:55 GMT · By Adrian Placinta

How to Repair Tables in MySQL

SHARE:

Adjust text size:



Enlarge picture
If you got an error from the table handler, the best way to see what it means is to run in command prompt the command perror nnn, where nnn is the number of the error.
C:/xampp/mysql/bin>perror 145
MySQL error code 145: Table was marked as crashed and should be repaired


If you want to repair a table from the
command prompt, stop the mysqld server. After you stopped the server, run the following command: myisamchk *.MYI, and to suppress any information, use -s argument. Using --update-state option you will tell the myisamchk to mark the table as checked. You will repair only the tables that myisamchk will mark with errors.

In the next step, you will try to repair the tables. Use myisamchk -r -q tbl_name to try to repair the index file without repairing the data file. The arguments -r -q mean that you will attempt a quick recovery mode. If the data file contains everything that it should and the delete links point to the correct locations within the data file, this should work, and the table will be fixed.

If this doesn't work, we will try another method. First, backup the data. Next, use myisamchk -q tbl_name in recovery mode and this will remove incorrect rows and deleted rows from the data file and reconstructs the index file. If this method doesn't work either, try to run myisamchk --safe-recover tbl_name which is slower, but it should also be more efficient.

If you encounter unexpected errors during the repairing process for one of the tables, we will try to create an index file. Before starting, make sure you make a backup. Then run the following commands:
shell> mysql db_name
mysql> SET AUTOCOMMIT=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit


Then move the data into the newly created data file. After that, the myisamchk -r -q command should work. Another way is to use REPAIR TABLE tbl_name USE_FRM SQL statement which will perform everything automatically.
FILED UNDER:
MySQL
repair
tables

TELL US WHAT YOU THINK:

7,431 hits · Link to this article · Print article · Send to friend · Subscribe to news

MUST-READ RELATED ARTICLES:


MySQL Query Browser

How to Assign a Password to the Root Account on MySQL

MySQL Migration Toolkit

How to Reset Root Password on MySQL

How to Secure Accounts in MySQL

READER COMMENTS:



No user comments yet.
Be the first to express your opinion!
Copyright © 2001-2012 Softpedia. Contact/Tip us at

WindowsGamesDriversMacLinuxScriptsMobileHandheldNews

SUBMIT PROGRAM   |   ADVERTISE   |   GET HELP   |   SEND US FEEDBACK   |   RSS FEEDS   |   UPDATE YOUR SOFTWARE   |   ROMANIAN FORUM