Checking and repairing MySQL tables: MySQL table is marked as crashed and last (automatic?) repair failed

0x001 Issue Background

MySQL executes the relevant MySQL command (we performed, show procedure status) When prompted Mysql.proc table crashed, cannot be repaired (marked as crashed and last (automatic?) Repair failed)

Error message: MySQL table is marked as crashed and last (automatic?) repair failed

0x002 Analytical Processing

MySQL provides commands for checking and repairing tables :

MySQL command:

Check table "table name" to see if the tables are corrupted.

After REPAIR TABLE ' table_name ' executes, the prompt cannot be repaired and can only be discarded.

When you are confused, you can only exit the MySQL interactive terminal and try the Mysqlcheck.

to fix a table using the Mysqlcheck command :

Check:

Mysqlcheck-uuser-ppassword Database Table-c #检查单个表是否损坏

Mysqlcheck-uuser-ppassword database-c #检查整个库那些表损坏

Repair:

Mysqlcheck-uuser-ppassword Database Table-r # Repair Data Sheet

Mysqlcheck-uuser-ppassword Database-r # Repairing the entire database

Mysqlcheck--help

This program can is used to CHECK (-C,-M,-C), REPAIR (-R), ANALYZE (-a)

or OPTIMIZE (-O) tables. Some of the options (Like-e or-q) can be

Used at the same time. Not all options is supported by all storage engines.

Consult the MySQL Manual for latest information about the

Above. The Options-c,-r,-a And-o is exclusive to all other, which

means the last option is used, if several was specified.

The option-c is used by default, if none is specified. You

Can change the default behavior by making a symbolic link, or

Copying this file somewhere with another name, the alternatives is:

Mysqlrepair:the default option would be-r

Mysqlanalyze:the default option would be-a

Mysqloptimize:the default option would be-o

Usage:mysqlcheck [OPTIONS] database [tables]

OR Mysqlcheck [OPTIONS]--databases DB1 [DB2 DB3 ...]

OR Mysqlcheck [OPTIONS]--all-databases

--print-defaults Print the program argument list and exit

--no-defaults Don ' t read default options from the any options file

--defaults-file=# only read default options from the given file #

--defaults-extra-file=# read this file after the global files is read

-A,--all-databases Check all the databases. This would be same as

--databases with all databases selected.

-A,--analyze analyze given tables.

-1,--all-in-1 Instead of issuing one query for each table, use one

Query per database, naming all tables in the

A comma-separated list.

--auto-repair If A checked table is corrupted, automatically fix it.

Repairing'll be do after all tables has been

Checked, if corrupted ones were found.

--character-sets-dir=name

Directory where character sets is.

-C,--check check table for errors.

-C,--check-only-changed

Check only tables this has changed since last check or

Haven ' t been closed properly.

-G,--check-upgrade check tables for version-dependent changes. May be used

With--auto-repair to correct tables requiring

Version-dependent updates.

--compress use compression in Server/client protocol.

-B,--databases to check several databases. Note the difference in usage;

In this case no tables is given. All name arguments is

Regarded as databasenames.

-#,--debug[=#] This is a non-debug version. Catch this and exit.

--default-character-set=name

Set the default character set.

-F,--fast Check only tables that haven ' t been closed properly.

-F,--force Continue even if we get an sql-error.

-E,--extended If you is using this option with CHECK TABLE, it would

Ensure the table is a percent consistent, but would

Take a long time. If You is using this option with

REPAIR TABLE, it would force using the old slow REPAIR with

Keycache method, instead of much faster repair by

sorting.

-?,--Help Display this help message and exit.

-H,--host=name Connect to host.

-M,--medium-check Faster than Extended-check, but only finds 99.99 percent

of all errors. Should is good enough for most cases.

-O,--optimize optimize table.

-P,--password[=name]

Password to use when connecting to server. If Password is

Not given it's solicited on the TTY.

-P,--port=# port number to use for connection.

--protocol=name the Protocol of Connection (Tcp,socket,pipe,memory).

-Q,--quick If you is using this option with CHECK TABLE, it

Prevents the check from scanning the "Rows to" check for

Wrong links. This is the fastest check. If you is using

This option with REPAIR TABLE, it would try to REPAIR only

The index tree. The fastest repair method for a

Table.

-R,--repair Can fix almost anything except unique keys that aren ' t

Unique.

-S,--silent Print only error messages.

-S,--socket=name socket file to use for connection.

--tables Overrides option--databases (-B).

--use-frm when used with REPAIR, get table structure from. frm

file, so the table can be repaired even if. MYI header is

Corrupted.

-U,--user=name user for login if is not current user.

-V,--verbose Print info about the various stages.

-V,--version Output version information and exit.

Myisamchk Repairing MySQL Table

Myisamchk is a very useful tool for MyISAM table maintenance. 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).

MYISAMCHK option Explanation

–debug=debug_options,-# debug_options

Output debug log files. Debug_options strings are often ' d:t:o,filename '.

–silent,-s

Silent mode. Writes the output only if an error occurs.

–wait,-W

If the table is locked, it is not signaled that the error terminates, but waits until the table is unlocked before proceeding.

If you do not use –skip-external-locking, you can use MYISAMCHK to check the table at any time. When the table is checked, all clients that attempt to update the table will wait until MYISAMCHK is ready to continue.

Note that if you run mysqld with the –skip-external-locking option, you can only lock the table with another MYISAMCHK command.

–var_name=value

The following variables can be set through the –var_name=value option:

–check,-C

Check the table for errors. If you do not explicitly specify an action type option, this is the default action.

–check-only-changed,-C

Checks only the tables that have changed since the last check.

–extend-check,-E

Check the table very carefully. If the table has many indexes it will be quite slow.

–fast,-f

Only check for tables that are not properly closed.

–force,-F

If Myisamchk finds any errors in the table, it is automatically repaired.

–information,-I.

Prints statistics for the checked table.

–medium-check,-M

Check more quickly than the –extend-check. Only 99.99% errors are found

–update-state,-U

Save the information in the. Myi file to indicate when the table was checked and whether the table crashed. This option is used to take full advantage of the –check-only-changed option

However, this option should not be used if the MYSQLD server is using the table and is running with the –skip-external-locking option.

–read-only,-t

Do not mark the table as checked. Useful if you use MYISAMCHK to check tables that are being used by other applications without locking them

–backup,-B

Back up the. myd file to File_name-time. Bak

–character-sets-dir=path

Character Set installation directory.

–correct-checksum

Corrects the checksum information for the table.

–data-file-length=len,-D len

Maximum length of the data file

–extend-check,-e

The repair attempts to recover each row from the data file. A large amount of garbage can be found in general. Do not use this option unless you disregard the consequences.

–force,-F

Overwrite the old intermediate file (file name is similar to Tbl_name. TMD), rather than interrupting

–keys-used=val,-K Val

For Myisamchk, the option value is a bit value that describes the index to update. An index of each bits of the option value corresponding to the table, where the first index corresponds to bit 0.

Option value 0 disables updates to all indexes, which guarantees quick insertion. The disabled index can be reactivated by Myisamchk-r.

–parallel-recover,-P

Same as-R and-n usages, but all keys are created in parallel using different threads.

–quick,-q

Do not modify the data files to quickly fix them.

–recover,-R

Can fix almost everything, unless the unique key is not unique (this is very unlikely for the MyISAM table). If you want to restore the table,

This is the first option to try. If the Myisamchk report table cannot be restored with-R, you can only try-O.

In the unlikely event that-R fails, the data file remains intact).

–safe-recover,-O

Reads with an old recovery method, reads all rows sequentially, and updates all index trees based on the rows found. This is slower than-R,

But can handle the case that-R cannot handle. The recovery method uses less hard disk space than-R. In general, you should first repair with-R, if-R fails to use-O.

–sort-recover,-N

Forces Myisamchk to resolve key values by sorting, even if the temporary file is likely to be large.

–analyze,-a

Analyze the distribution of key values. This improves junction performance by allowing the junction optimizer to better select the order in which the table should be joined and which key should be used.

To get distribution-related information, use the Myisamchk–description–verbose tbl_name command or the show KEYS from Tbl_name statement.

–sort-index,-S

Sort the index tree blocks in order from high to low. This optimizes the search and will make the table scan for key values faster.

–set-auto-increment[=value],-a[value]

Forces a new record starting with the given value to use the auto_increment number (or a higher value if there is already a record of auto_increment value).

If value is not specified, the auto_increment number of the new record should use the maximum value of the current table plus 1.

–description,-D

Prints descriptive information about the table.

During the use of the Myisamchk repair process, the discovery prompted an error, and later searched for the next, found the need to stop the MySQL service for repair.

To the MySQL DB directory, complete the repair of the data table by Myisamchk-r-F table name.

Go to your Data folder and try running Myisamchk-r <table_name>. You should stop MySQL process first. If that doesn ' t work, you can try with myisamchk-r-v-f <table_name>

0X003 Summary

MySQL will inadvertently occur in the use of the library table corruption, through the repair table name, mysqlcheck,myisamchk to check the library table is damaged, and repair.

Checking and repairing MySQL tables: MySQL table is marked as crashed and last (automatic?) repair failed