Table ' xxx ' is marked as crashed and should as repaired error resolution reference _mysql

Source: Internet
Author: User
Tags memory usage mysql manual parent directory

It is also possible that any of these methods can be applied to this database. I temporarily did not have the condition test, has the condition to test, hoped solves the database to report this error the question.
Preventive measures:
1, must back up a database, at least keep the table structure, some dispensable data, you can directly cover.
2, important data should always pay attention to backup, generally one months or so backup.
3, the occurrence of such errors, the general can be solved by jb51.net test the following method is more feasible. However, there are occasional data loss cases, it is strongly recommended to back up first.

Copy Code code as follows:

Solution: First need to locate your MySQL bin directory, which contains the directory of Myisamchk.exe files
./myisamchk-c-R database table myi the path to the file (for example:/HOME/MYSQL/VAR/CRAWLERFEEDSKY/AAAA. MYI)
If not, then the-F force fix


how to fix MySQL under Windows Server:
F:\sqldata\mysql\bin>cd.. Return to Parent Directory
F:\SQLDATA\MYSQL>CD data into the directory where the database is located (MySQL database files are placed in the directory of data)
F:\SQLDATA\MYSQL\DATA>CD jb51 into the database, the database in the Windows Server is a folder (here take jb51 for example)

F:\sqldata\mysql\data\jb51>myisamchk-r Jb51_soft

-Recovering (with sort) myisam-table ' Jb51_soft '
Data records:7216
-Fixing Index 1
-Fixing Index 2
-Fixing Index 3

F:\sqldata\mysql\data\jb51>

Jb51_soft is the table name of the database, indicating which table is wrong, and which table to fix.

for later convenience, we can write a batch, save this file as Fixit.bat in the directory where the database resides.
Copy Code code as follows:

Myisamchk-r Jb51_soft


Here are a number of ways to collect online, you can test.
A
Last night browsing their blog, suddenly found that all pages can not be displayed, to the background view, found a "Table ' xxx ' is marked as crashed and should to repaired" error. Quickly search the Internet, the original modification of this serious error is very simple:
1. Access to manage MySQL phpMyAdmin
2. Select your own database on the left
3. In the right then check the error message in the ' xxx ' table
4. Scroll to the bottom of the screen, there is a Drop-down menu (with selected:), select "Repair table"
After the modification, and did not find any data loss, hurriedly backed up a database into the mail, backup is very important!
Two
Phenomenon:
Mysql> Use YOUREALCN
Database changed
Mysql> Select User,company from Biz_user where commend= ' 1 ' is by Regtime desc,checked, Desc 0, 5;
ERROR 145 (HY000): Table './yourealcn/biz_user ' is marked as crashed and should
Mysql> exit
Use the command Myisamchk to repair the myi file of the database
#/usr/local/mysql5/bin/myisamchk-c-r/bak/lib/mysql/yourealcn/biz_user.*
Myisamchk:error: '/bak/lib/mysql/yourealcn/biz_user.frm ' is not a myisam-table
---------
Myisamchk:error: '/bak/lib/mysql/yourealcn/biz_user. MyD ' is not a myisam-table
---------
-Recovering (with sort) myisam-table '/bak/lib/mysql/yourealcn/biz_user. Myi '
Data records:20414
-Fixing Index 1
-Fixing Index 2
-Fixing Index 3
-Fixing Index 4
Three
caused by:java.sql.SQLException:Table ' ' table name ' is marked as crashed and should to be repaired
Solution:
./myisamchk-c-R database table myi the path to the file (for example:/HOME/MYSQL/VAR/CRAWLERFEEDSKY/AAAA. MYI)
If not, then the-F force fix
Four
Today on the server A look, found that the Web page error, unable to connect to the database server. MySQL service itself down, and then restart the server, found that the Web page could not be opened, prompted: [Mysql]table Tblname is marked as crashed and should are repaired
MySQL prompts tblname table is corrupted, need to fix, workaround:
Go to the corresponding database directory:
Cd/var/lib/mysql/dbname
Use Myisamchk Repair:
Shell> Myisamchk-r Tblname
Five
The repair command I used was: Myisamchk-r bbsthreads
Where bbsthreads is my problem table name, of course, use this command to go into MySQL you have the problem of the database table storage path, specific more detailed commands can see help: Myisamchk--help;
If you use the above command you can not solve the problem please look back, the contents of the following is reproduced by me.
My website has a problem, visit a look, really full screen error, check the MySQL log, error message is:
Table '. \dedecmsv4\dede_archives ' is marked as crashed and should are repaired
Hint that the CMS article table dede_archives is marked with problems that need to be repaired. So quickly restore historical data, the Internet to find out why. Eventually solve the problem. The workaround is as follows:
Locate the Bin/myisamchk tool for the MySQL installation directory and enter at the command line:
Myisamchk-c-R. /data/dedecmsv4/dede_archives. Myi
The Myisamchk tool will then help you recover the index of the datasheet. Reboot MySQL, problem solved.
Problem Analysis:
1, the cause of the error, a netizen said is frequent query and update dede_archives table caused by the index error, because my page is not static generation, but dynamic page, so I agree with this argument. It is also said that the MySQL database for some reason has been damaged, such as: The database server bursts of power, in the database table to provide services to the table of the original file to do some kind of operation may cause the MySQL database table is corrupted and can not read data. In short, the damage to the table is caused by some unpredictable problems.
The problem is numbered 145.
2. Problem solving method.
When you try to fix a problem with a damaged table, there are three types of fixes. If you get an error message stating that a temporary file cannot be created, delete the file indicated by the information and try again-this is usually the legacy of the last repair operation.
The three repair methods are as follows:
% Myisamchk--recover--quick/path/to/tblname
% Myisamchk--recover/path/to/tblname
% Myisamchk--safe-recover/path/to/tblname
The first is the fastest, to fix the most common problems, and the last one is the slowest to fix problems that some other methods cannot fix.
Review and repair MySQL data files
If the above method cannot fix a damaged table, you can try these two techniques before you give up:
If you suspect that the index file of the table (*. Myi An unrecoverable error occurred, or even lost the file, you can use the data file (*. MYD) and the data format file (*.FRM) to regenerate it. First, make a copy of the data file (TBLNAME.MYD). To restart your MySQL service and connect to this service, delete the contents of the table using the following command:
Mysql> DELETE from Tblname;
When you delete the contents of a table, a new index file is created. Quit logging on and shut down the service and overwrite the new (empty) data file with the data file (tblname.myd) you just saved. Finally, use MYISAMCHK to perform a standard fix (the second method above) to regenerate index data based on the contents of the table's data and the table's format file.
If your table's format file (TBLNAME.FRM) is missing or an unrecoverable error occurs, but you know how to regenerate the table using the corresponding CREATE TABLE statement, you can regenerate a new. frm file and and your data files and index files ( If there is a problem with the index file, use the method above to reconstruct a new one. First make a copy of the data and index file, and then delete the original file (delete all the records in the data directory about the table).
Start the MySQL service and create a new table using the original CREATE table file. The new. frm file should work, but the best thing to do is to perform the standard fix (the second method above).
3, Myisamchk tool Introduction (see the official MySQL manual)
You can use the Myisamchk utility to get information about a database table or to check, fix, and optimize them. Myisamchk applicable to the MyISAM table (corresponding. Tables for myi and. myd files).
method to invoke Myisamchk:
Shell> myisamchk [options] tbl_name ...
Options specify what you want Myisamchk to do. Describe them later. You can also get a list of options by calling Myisamchk--help.
Tbl_name is the database table you want to check or fix. If you don't run myisamchk somewhere in the database directory, you must specify the path to the database directory because Myisamchk doesn't know where your database is located. In fact, Myisamchk doesn't care if the file you're working on is in a database directory; You can copy the files that correspond to the database tables elsewhere and perform recovery operations there.
If you prefer, you can name several tables using the MYISAMCHK command line. You can also name index files by using the. Myi "suffix) to specify a table. It allows you to use the mode "*. Myi "Specifies all the tables in a directory. For example, if you are in the database directory, you can check all the MyISAM tables in this directory:
Shell> Myisamchk *. Myi
If you are not in the database directory, you can check all tables that are there by specifying the path to the directory:
Shell> myisamchk/path/to/database_dir/*. Myi
You can even check all the tables in all databases by assigning a wildcard character to the path to the MySQL data directory:
Shell> myisamchk/path/to/datadir/*/*. Myi
The recommended way to quickly check all MyISAM tables is:
Shell> myisamchk--silent--fast/path/to/datadir/*/*. Myi
If you want to check all MyISAM tables and fix any corrupted tables, you can use the following command:
shell> myisamchk--silent--force--fast--update-state \
-O key_buffer=64m-o sort_buffer=64m \
-O read_buffer=1m-o write_buffer=1m \
/path/to/datadir/*/*. Myi
This command assumes that you have free memory greater than 64MB. For more information about allocating memory with MYISAMCHK, see section 5.9.5.5, "Myisamchk memory usage."
When you run Myisamchk, you must make sure that the other programs do not use the table. Otherwise, when you run Myisamchk, the following error message is displayed:
Warning:clients are using or haven ' t closed the table properly
This means that you are trying to check a table that is being updated by another program that has not closed the file properly, such as a mysqld server, that has not been shut down or has been terminated.
If Mysqld is running, you must forcibly empty any table modifications that are still in memory through flush tables. When you run Myisamchk, you must make sure that the other programs do not use the table. The easiest way to avoid this problem is to use check table instead of Myisamchk to check the table.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.