How to fix a MySQL database table

The symptom of a damaged table is usually an unexpected interruption of the query and you can see such errors as:
"Tbl_name.frm" is locked and cannot be changed.
Unable to find file ' Tbl_name. Myi "(Errcode: ###).
Error ### from the table processor (at this point, error 135 is an exception).
Unexpected end of file.
The record file was destroyed.
In these cases, you have to fix the table. The repair of a table is a very difficult task, and in many cases it is helpless. However, there are some conventional knowledge of ideas and processes that can be followed to increase the chance of modifying the table. Usually, you can start with the fastest fix to see if it can be a pocket-sized failure. If the discovery is unsuccessful, you can incrementally upgrade to a more thorough but slower fix. If it is still difficult to repair, it should be recovered from the backup. This section has been covered in detail in the previous chapter.
Simple and safe fixes
To repair a table perform the following steps:
First, the table is fixed with the--RECOVER,-R option, and the--QUICK,-Q option is used to recover only from the contents of the index file. This does not touch the data file to fix the index file. (-R means "recovery mode")
Myisamchk-r-Q tbl_nameisamchk-r-Q tbl_name
If the problem persists, ignore the--quick option and allow the hotfix to modify the data file because there may be a problem. The following command deletes the incorrect records and deleted records from the data file and rebuilds the index files:
Myisamchk-r Tbl_nameisamchk-r Tbl_name
If the previous step fails, use the. The security recovery model uses an old recovery method to handle a small number of cases (but more slowly) that do not work with the normal recovery model.
Myisamchk--safe-recover tbl_nameisamchk--safe-recover tbl_name
Difficult to repair
If the first 16K block in the index file is corrupted, or contains incorrect information, or if the index file is lost, you should only be at this stage. In this case, it is necessary to create a new index file. Follow these steps as follows:
Navigate to the database directory containing the crash table
Move the data file to a more secure location.
To create a new (empty) data and index file by using a table description file:
shell> MySQL db_namemysql> DELETE from tbl_name;mysql> quit
The above statement will recreate the new empty table and use the table's description file tbl_name.frm to regenerate the new data and index files.
Copy old data files to the newly created data file. (Don't just move old files back into new files; you need to keep a copy in case something goes wrong.) ）
In the use of standard repair methods. Now Myisamchk-r-Q should be working. (This should not be an infinite loop).
If you have a backup file for a table, then everything is much easier. You can recover the table's description from the backup file, and then in the checklist, you may want to continue using the standard fix method, which should correct the problem.
Very difficult to fix
Only the description file is destroyed, you should reach this stage. This should never have happened because the description file does not change after the table is created.
Restore the description file from a backup and return to Stage 2. You can also restore the index file and return to Stage 1. For the latter, you should start with Myisamchk-r.
If, for some reason, the data's backup file is missing or there is no backup file, but you remember to create the table's CREATE TABLE statement, so good, so that you can restore the index file:
Navigate to the database directory containing the crash table
Move the data file to a more secure location. Then delete the corresponding directory in the database directory.
Invoke the MySQL concurrent CREATE TABLE statement to establish the table. (www.3lian.com)
Exit MySQL, and move the original data files and index files back to the database directory, replacing the newly created file.
Then go back to Stage 2 and fix the table. You can also just move back to the data file, keep the new description and index file, and then go back to Phase 1 and continue with the standard method to fix the table.