How to fix a MySQL database table

You may be in the process of using MySQL, all kinds of accidents cause damage to the database table, and this data is often the most recent data, it is often impossible to find in the backup data. This chapter will show you how to fix the table after examining the table's questions in the previous article.

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.