MySQL Table error: is marked as crashed and last (automatic?) and error:table "MySQL". " Innodb_table_stats "not Found

I. MySQL report table error when executing select: is marked as crashed and last (automatic?)

Here's how to fix it:

Locate the Bin/myisamchk tool for the MySQL installation directory and enter it at the command line:

Myisamchk-c-R. /data/dedecmsv4/dede_archives. MYI

Then the Myisamchk tool will help you recover the index of the data table. Restart MySQL for problem resolution.

Problem Analysis:

1, the cause of the error, the Netizen said is frequently 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 burst power outage, in the database table provides services when the table's original file for some kind of operation may cause the MySQL database table is corrupted and unable to read the data. This is because some of the unpredictable problems caused the damage to the table.

The issue is numbered 145

2. Problem solving method.

When you try to fix a corrupted table, there are three types of repair. If you get an error message stating that a temporary file cannot be established, delete the file indicated by the information and try again – this is usually left over from the last repair operation.
The three repair methods are as follows:

1. % Myisamchk--recover--quick/path/to/tblname
2. % Myisamchk--recover/path/to/tblname
3. % Myisamchk--safe-recover/path/to/tblname

The first is the quickest, which is used to fix the most common problems, and the last is the slowest one to fix problems that other methods cannot fix.

Checking and repairing 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 the table's index file (*. MYI) An uncorrectable error has occurred, even if the file is lost, you can use the data file (*. MYD) and a 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 the service, use the following command to delete the contents of the table:
Mysql> DELETE from Tblname;
When you delete the contents of a table, a new index file is created. Quit logging in and close the service again, and then overwrite the new (empty) data file with the data file (tblname.myd) you just saved. Finally, using MYISAMCHK to perform a standard fix (the second method above), the index data is regenerated 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 uncorrectable error has occurred, but you know how to use the corresponding CREATE TABLE statement to regenerate the table, you can regenerate a new. frm file and your data file and index file ( 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 about the table in the Data directory).

Start the MySQL service and create a new table using the original CREATE table file. The new. frm file should work correctly, but it's a good idea to perform a 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 database tables or to check, repair, and optimize them. Myisamchk applicable MyISAM table (corresponding. Tables for myi and. myd files).

To invoke the Myisamchk method:

Shell> myisamchk [options] tbl_name ...
Options specify what you want Myisamchk to do. They are described later. You can also get a list of options by calling Myisamchk–help.

Tbl_name is the database table that you want to check or repair. If you do not run Myisamchk somewhere in the database directory, you must specify the path to the database directory because Myisamchk does not 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 file that corresponds to the database table elsewhere and perform the recovery there.

If you want, you can name several tables with the MYISAMCHK command line. You can also name the index file by using the. MYI "suffix" to specify a table. It allows you to use the mode "*. MYI "Specifies all tables in a directory. For example, if you are in the database directory, you can check all the MyISAM tables under the directory:

Shell> Myisamchk *. MYI
If you are not in the database directory, you can check all the tables there by specifying the path to the directory:

Shell> myisamchk/path/to/database_dir/*. MYI
You can even check all the tables in all the databases by specifying a wildcard character for the path to the MySQL data directory:

Shell> myisamchk/path/to/datadir/*/*. MYI
The recommended way to quickly check all MyISAM tables is:

1. Shell> myisamchk--silent--fast/path/to/datadir/*/*. MYI

If you want to check all the MyISAM tables and fix any corrupted tables, you can use the following command:

1. shell> myisamchk--silent--force--fast--update-state
2. -O key_buffer=64m-o sort_buffer=64m
3. -O read_buffer=1m-o write_buffer=1m
4. /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 other programs do not use tables. Otherwise, when you run Myisamchk, the following error message is displayed:

1. Warning:clients is using or haven "T closed the table properly

This means that you are trying to check for a table that is being updated by another program (such as a mysqld server) that has not closed the file or has been terminated without properly closing the file.

If Mysqld is running, you must use flush tables to force empty any table modifications that are still in memory. When you run Myisamchk, you must make sure that other programs do not use tables. The easiest way to avoid this problem is to use check table instead of Myisamchk to examine the table.

Two. mysql Mysql.err file reported the following error:

InnoDB:Error:Fetch of persistent statistics requested for table "test". " Orderqueue "But the required system tables Mysql.innodb_table_stats and Mysql.innodb_index_stats is not present or has U nexpected structure. Using transient stats insttestd.
InnoDB:Error:Table "MySQL". " Innodb_table_stats "not found.
Innodb:recalculation of persistent statistics requested for table "test". " Orderqueue "But the required persistent statistics storage are not present or is corrupted. Using transient stats insttestd.

View discovery is due to the previous DBA directly importing files, MySQL database files and installed MySQL version mismatch, missing innodb_table_stats,innodb_index_stats these tables, So x first delete the innodb_index_stats and innodb_index_stats data files in the MySQL database file

RM-RF Innodb_index_stats;

RM-RF Innodb_index_stats;

Then, after manually creating these tables in the MySQL schema, restart MySQL.

CREATE TABLE ' Innodb_table_stats ' (
' database_name ' varchar (COLLATE) Utf8_bin not NULL,
' table_name ' varchar (COLLATE) Utf8_bin not NULL,
' Last_update ' timestamp not NULL the DEFAULT current_timestamp on update current_timestamp,
' N_rows ' bigint () unsigned not NULL,
' Clustered_index_size ' bigint () unsigned not NULL,
' Sum_of_other_index_sizes ' bigint () unsigned not NULL,
PRIMARY KEY (' database_name ', ' table_name ')
) Engine=innodb DEFAULT Charset=utf8 collate=utf8_bin stats_persistent=0;

CREATE TABLE ' Innodb_index_stats ' (
' database_name ' varchar (COLLATE) Utf8_bin not NULL,
' table_name ' varchar (COLLATE) Utf8_bin not NULL,
' index_name ' varchar (COLLATE) Utf8_bin not NULL,
' Last_update ' timestamp not NULL the DEFAULT current_timestamp on update current_timestamp,
' Stat_name ' varchar (COLLATE) Utf8_bin not NULL,
' Stat_value ' bigint () unsigned not NULL,
' Sample_size ' bigint () unsigned DEFAULT NULL,
' stat_description ' varchar (1024x768) COLLATE Utf8_bin not NULL,
PRIMARY KEY (' database_name ', ' table_name ', ' index_name ', ' stat_name ')
) Engine=innodb DEFAULT Charset=utf8 collate=utf8_bin stats_persistent=0;

Reference article: http://www.kuqin.com/database/20090811/66473.html

http://blog.csdn.net/lwei_998/article/details/41278457

MySQL Table error: is marked as crashed and last (automatic?) and error:table "MySQL". " Innodb_table_stats "Not found