Copyright (C) 2000-2012 |
GNU Info (mysql.info)RepairHow to Repair Tables .................... In the following section we only talk about using `myisamchk' on `MyISAM' tables (extensions `.MYI' and `.MYD'). If you are using `ISAM' tables (extensions `.ISM' and `.ISD'), you should use `isamchk' instead. Starting with MySQL Version 3.23.14, you can repair MyISAM tables with the `REPAIR TABLE' command. Note: REPAIR TABLE. The symptoms of a corrupted table include queries that abort unexpectedly and observable errors such as these: * `tbl_name.frm' is locked against change * Can't find file `tbl_name.MYI' (Errcode: ###) * Unexpected end of file * Record file is crashed * Got error ### from table handler To get more information about the error you can run `perror ###'. Here is the most common errors that indicates a problem with the table: shell> perror 126 127 132 134 135 136 141 144 145 126 = Index file is crashed / Wrong file format 127 = Record-file is crashed 132 = Old database file 134 = Record was already deleted (or record file crashed) 135 = No more room in record file 136 = No more room in index file 141 = Duplicate unique key or constraint on write or update 144 = Table is crashed and last repair failed 145 = Table was marked as crashed and should be repaired Note that error 135, no more room in record file, is not an error that can be fixed by a simple repair. In this case you have to do: ALTER TABLE table MAX_ROWS=xxx AVG_ROW_LENGTH=yyy; In the other cases, you must repair your tables. `myisamchk' can usually detect and fix most things that go wrong. The repair process involves up to four stages, described below. Before you begin, you should `cd' to the database directory and check the permissions of the table files. Make sure they are readable by the Unix user that `mysqld' runs as (and to you, because you need to access the files you are checking). If it turns out you need to modify files, they must also be writable by you. If you are using MySQL Version 3.23.16 and above, you can (and should) use the `CHECK' and `REPAIR' commands to check and repair `MyISAM' tables. Note: CHECK TABLE. Note: REPAIR TABLE. The manual section about table maintenance includes the options to `isamchk'/`myisamchk'. Note: Table maintenance. The following section is for the cases where the above command fails or if you want to use the extended features that `isamchk'/`myisamchk' provides. If you are going to repair a table from the command line, you must first take down the `mysqld' server. Note that when you do `mysqladmin shutdown' on a remote server, the `mysqld' server will still be alive for a while after `mysqladmin' returns, until all queries are stopped and all keys have been flushed to disk. *Stage 1: Checking your tables* Run `myisamchk *.MYI' or `myisamchk -e *.MYI' if you have more time. Use the `-s' (silent) option to suppress unnecessary information. If the `mysqld' server is done you should use the -update option to tell `myisamchk' to mark the table as 'checked'. You have to repair only those tables for which `myisamchk' announces an error. For such tables, proceed to Stage 2. If you get weird errors when checking (such as `out of memory' errors), or if `myisamchk' crashes, go to Stage 3. *Stage 2: Easy safe repair* NOTE: If you want repairing to go much faster, you should add: `-O sort_buffer=# -O key_buffer=#' (where # is about 1/4 of the available memory) to all `isamchk/myisamchk' commands. First, try `myisamchk -r -q tbl_name' (`-r -q' means "quick recovery mode"). This will attempt to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure: 1. Make a backup of the data file before continuing. 2. Use `myisamchk -r tbl_name' (`-r' means "recovery mode"). This will remove incorrect records and deleted records from the data file and reconstruct the index file. 3. If the preceding step fails, use `myisamchk --safe-recover tbl_name'. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode doesn't (but is slower). If you get weird errors when repairing (such as `out of memory' errors), or if `myisamchk' crashes, go to Stage 3. *Stage 3: Difficult repair* You should only reach this stage if the first 16K block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it's necessary to create a new index file. Do so as follows: 1. Move the data file to some safe place. 2. Use the table description file to create new (empty) data and index files: shell> mysql db_name mysql> SET AUTOCOMMIT=1; mysql> TRUNCATE TABLE table_name; mysql> quit If your SQL version doesn't have `TRUNCATE TABLE', use `DELETE FROM table_name' instead. 3. Copy the old data file back onto the newly created data file. (Don't just move the old file back onto the new file; you want to retain a copy in case something goes wrong.) Go back to Stage 2. `myisamchk -r -q' should work now. (This shouldn't be an endless loop.) *Stage 4: Very difficult repair* You should reach this stage only if the description file has also crashed. That should never happen, because the description file isn't changed after the table is created: 1. Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with `myisamchk -r'. 2. If you don't have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new data file, then move the description and index files from the other database to your crashed database. This gives you new description and index files, but leaves the data file alone. Go back to Stage 2 and attempt to reconstruct the index file. automatically generated by info2www version 1.2.2.9 |