The discussion in this section describes how to use
myisamchk on MyISAM tables
(extensions .MYI and
.MYD).
You can also (and should, if possible) use the
CHECK TABLE and
REPAIR TABLE statements to check
and repair MyISAM tables. See
Section 12.5.2.3, “CHECK TABLE Syntax”, and
Section 12.5.2.6, “REPAIR TABLE Syntax”.
Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as these:
tbl_name.frmCan't find file
tbl_name.MYInnn)Unexpected end of file
Record file is crashed
Got error
nnnfrom table handler
To get more information about the error, run
perror nnn, where
nnn is the error number. The
following example shows how to use perror to
find the meanings for the most common error numbers that
indicate a problem with a table:
shell> perror 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired
Note that error 135 (no more room in record file) and error 136
(no more room in index file) are not errors that can be fixed by
a simple repair. In this case, you must use
ALTER TABLE to increase the
MAX_ROWS and
AVG_ROW_LENGTH table option values:
ALTER TABLEtbl_nameMAX_ROWS=xxxAVG_ROW_LENGTH=yyy;
If you do not know the current table option values, use
SHOW CREATE TABLE.
For the other errors, you must repair your tables. myisamchk can usually detect and fix most problems that occur.
The repair process involves up to four stages, described here. Before you begin, you should change location to the database directory and check the permissions of the table files. On Unix, make sure that they are readable by the 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.
This section is for the cases where a table check fails (such as
those described in Section 6.4.2, “How to Check MyISAM Tables for Errors”), or you want to use
the extended features that myisamchk
provides.
The options that you can use for table maintenance with myisamchk are described in Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
If you are going to repair a table from the command line, you must first stop the mysqld server. Note that when you do mysqladmin shutdown on a remote server, the mysqld server is still alive for a while after mysqladmin returns, until all statement-processing has stopped and all index changes 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 stopped, you should
use the --update-state 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 unexpected errors when checking (such as out
of memory errors), or if myisamchk
crashes, go to Stage 3.
Stage 2: Easy safe repair
First, try myisamchk -r -q
tbl_name (-r
-q means “quick recovery mode”). This
attempts 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:
Make a backup of the data file before continuing.
Use myisamchk -r
tbl_name(-rmeans “recovery mode”). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.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 does not (but is slower).
Note
If you want a repair operation to go much faster, you should
set the values of the
sort_buffer_size and
key_buffer_size variables
each to about 25% of your available memory when running
myisamchk.
If you get unexpected errors when repairing (such as
out of memory errors), or if
myisamchk crashes, go to Stage 3.
Stage 3: Difficult repair
You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:
Move the data file to a safe place.
-
Use the table description file to create new (empty) data and index files:
shell>
mysqlmysql>db_nameSET autocommit=1;mysql>TRUNCATE TABLEmysql>tbl_name;quit Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)
Important
If you are using replication, you should stop it prior to performing the above procedure, since it involves file system operations, and these are not logged by MySQL.
Go back to Stage 2. myisamchk -r -q should work. (This should not be an endless loop.)
You can also use the REPAIR TABLE
SQL
statement, which performs the whole procedure automatically.
There is also no possibility of unwanted interaction between a
utility and the server, because the server does all the work
when you use tbl_name USE_FRMREPAIR TABLE. See
Section 12.5.2.6, “REPAIR TABLE Syntax”.
Stage 4: Very difficult repair
You should reach this stage only if the
.frm description file has also crashed.
That should never happen, because the description file is not
changed after the table is created:
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.
If you do not 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, and then move the
.frmdescription and.MYIindex files from the other database to your crashed database. This gives you new description and index files, but leaves the.MYDdata file alone. Go back to Stage 2 and attempt to reconstruct the index file.
User Comments
FYI,
With errors from table handler, please check free and (hosting accounts) available diskspace.
I got: "error=130 = Got error 122 from table handler" and then table couldn't be opened anymore (Can't open file: '...MYD'. Errno: 145).
It seems that an account with no space left causes this kind of error and after adding more diskspace to the account the repair was successfull!
I had a problem with my backup script:
shell> mysqldump --opt -A > mysql.dump
mysqldump: Error 1030: Got error 127 from table handler when dumping table 'backgroundimage' at row: 0
shell> cd /var/lib/mysql/shop601
shell> myisamchk -e -i -v backgroundimage
... much data ...
myisamchk: error: record delete-link-chain corrupted
... much data ...
shell> mysql shop601
mysql> check table backgroundimage;
... data about corruption here ...
mysql> repair table backgroundimage;
... data about fix here ...
mysql> exit
Figuring out which database had a problem involved trying to dump each database in turn, using a shell script, until one of them printed that error.
I'm changing my script to:
mysqldump --force -a --opt -A > mysql.dump
so that at least the other databases make it into the backup. Without the --force option, the backup stops on the first error. Not pretty, especially if you don't notice the error for a month!
If you do not have a .MYI file, you cannot repair it using myisamchk alone, but you *CAN* repair it from within the server.
From the manual, "As of MySQL 4.0.2, you can also use REPAIR TABLE tbl_name USE_FRM, which performs the
whole procedure automatically."
So, you could do something like this:
REPAIR TABLE FOO USE_FRM;
Hope this helps someone else. I spent awhile looking for this answer!
ISAM tables cannot be converted to MyISAM files if the server is version 4.1.1-alpha. The documentation says the code that reads ISAM format is not compiled into the new server.
I was able to convert my older tables by shutting down the 4.1.1-alpha server, starting the 4.0.20 server, and running mysql_convert_table_format inside each database's data directory. The log file shows that InnoDB noted that the server version was downgraded, and it did something in response. If I used InnoDB's new features, this might have resulted in loss of data but I don't know that. After I restored the 4.1.1-alpha server, InnoDB noticed that too and took an action in response. The master.info file (for replication) had the last letter of the FQDN of the remote master server truncated and other lines were changed, consistent with a changed master.info format that doesn't change cleanly back and forth. Otherwise, things appear to be OK after downgrading and upgrading again in order to convert ISAM tables to MyISAM format.
So: (1) believe the documentation when it says you should upgrade one step at at time (ie, to 4.0 before going to 4.1), (2) backup everything you can think of if you have to downgrade, (3) Mysql.com or an interested user might consider creating a utility to do this translation independently of the server, since someone is going to discover this problem after the server version has moved way beyond 4.1, and may have real trouble when downgrading!
You mention that you should increase the key_buffer_size and sort_buffer_size before the REPAIR TABLE but what happens if you update these AFTER you start the REPAIR?
I've done this twice . I never have time to benchmark it as I want my DB up ASAP.
The global update of course would be with:
SET GLOBAL sort_buffer_size=300000000;
The documentation isn't really clear on this.
I'm currently trying to repair a database thats having problems. We have large tables and I need to get this box up ASAP.
I'm trying to exec a REPAIR TABLE command on some of our larger tables and its taking forever.
A quick exec of
SHOW PROCESSLIST;
shows that the repair is executing with "Repair with keycache"
This makes sense since I just did a "REPAIR TABLE FOO QUICK;" which only works on indexes.
Since REPAIR TABLE gives no feedback on status setting any of these variables seems magic since we don't know if anything good or bad has happened.
You can now use platform tools to look at your CPU or IO. My mysql process was IO bound at this point with 0% CPU.
On Linux you can dump this info with iostat -k 1... which will then dump IO statistics as well as CPU to the console every second.
Starting out I was at about 0% CPU with an iowait of about 45%.
I increased key_buffer_size to about 300M and this blocked for a second on the mysql console and then returned. Then I increased this to 500M and it jumped to about 5%.
At this point I made a few more settings and eventually increased this to 1.4G.
My CPU is now sitting at about 10-13%.
In conclusion key_buffer_size, myisam_sort_buffer_size and sort_buffer_size can all be set at runtime. If you want to optimize a REPAIR TABLE to complete ASAP you should set these variables to correct values and monitor iostat or other platform specific CPU and disk performance metrics to determine feedback.
I was having a problem with my MySQL hanging and corrupting tables. Also, the repair attempts were constantly giving me Error 28, eventhough I was only using 3% of the approx 60 GB. It got so bad that mysqld couldn't even read db.MYI. Finally I unmounted the partition and ran the fschk. When I remounted all the repair attempts were successful. I hope this helps someone out there.
I've had some trouble repairing a MyISAM table after a number of crashes due to bad hardware. When I used the mysql client and selected the database I gor a number of messages saying the following:
Didn't find any fields in table 'errorQuery'
Didn't find any fields in table 'errorReportFunction'
Didn't find any fields in table 'friend'
Didn't find any fields in table 'functionID'
Didn't find any fields in table 'functionIDs'
Didn't find any fields in table 'guestbook'
.....
First I tried to start the mysql client with the -A switch in order for it to ignore reading the .frm files at database selection. By doing this I where able to conclude that there where only the first table "errorQuery" that didn't work, but MySQL reported every table "after" this table as corrupt too...
I tried the steps that where suggested on this page, but none worked. When I for instance tried to do a REPAIR TABLE errorQuery USE_FRM; I got the message that the database could't read the .frm-file for this table. I checked and rechecked the permissions and found them to be correct.
What I did now where to use another database and there recreate the errorQuery table with the exact same sql as before. I then copied the errorQuery.MYD and errorQuery.MYI to the new database and found that all the data where intact and the messages where gone. I then just backuped the files from the first database and then copied these working file to the original location.
This might not be a good solution, but in my case the data in this table where not important so I gave it a shot...
I made some large tables (around 3-4 billion records each) and then tried to create indexes on one column in each of them. I found that MySQL stopped at a seemingly arbitrary maximum index file size (.MYI file) for both of them. When I tried to open either table, I received an error message "ERROR 1016 (HY000): Can't open file: 'mytable.MYI' (errno: 144)" indicating that the index file was crashed.
CHECK TABLE mytable QUICK reported 'warning : Keyfile is almost full, 17179867136 of 17179867136 used' Then myisamchk -dv mytable reported similar information under 'Max keyfile length," and reported "Keyfile pointer (bytes): 3'
After a little digging around, I realized that a 3-byte keyfile pointer only allows the index file to hold 2^24 blocks (about 16 million blocks of 1024 bytes each). My index file was holding about 110 keys per block (e.g., each record needed 4 bytes for the key value, plus 4 bytes for the datafile pointer, and maybe one byte for null/housekeeping, and then each block kept a little more empty space). So the 3-byte keyfile pointer only allowed MySQL to index about 1.8 billion records. Then it gave up, leaving a corrupted index file.
I have found no documentation for how MySQL decides how big a keyfile pointer to use in the .MYI file. I had no trouble adding 3-4 billion records to the MYD file, so it didn't occur to me that there would be a problem with the MAX_ROWS clause of the CREATE TABLE command (which I hadn't specified). But that turned out to be the problem.
I recovered my data table without the index file, then issued ALTER TABLE <mytable> MAX_ROWS=4000000000, ADD INDEX (mykey); and now it looks like everything will be OK.
Here's how I got my table back, without the index file (similar to what's described above):
(1) make a backup of the data file (mytable.myd), just in case
(2) create a new empty table with the same structure as the existing table, but no index keys.
(3) copy the .FRM and .MYI files from the new (empty) table over the existing mytable.frm and mytable.myi files.
(4) issue REPAIR TABLE mytable USE_FRM; It is very important to include the USE_FRM clause. That way, MySQL knows to make a new (empty) .MYI file that matches the .MYD file, rather than truncating the .MYD file to match the .MYI file. You will eventually get a message along the lines of "Number of rows changed from 0 to xxxx".
(5) Now you have a working table, with no indexes. Then you can set MAX_ROWS to something appropriate, and try again to make the indexes.
I cannot overemphasize the need to make a copy of the relevant .MYI file _before_ attempting a repair. I recently ran repair table and the size went from 22GB to 45MB (with corresponding data loss).
Using myisamchk sometime requires shutting down MySQL server and that's not very easy in certain situation. This shell script accepts a database name from input and gets all table names and runs a SQL query against these tables. Currently, it only optimizes all the tables and could be expanded to do a lot more. OPTIMIZE does a lot more, an all-in-one solution (for MyISAM tables: repair, sort index, update stats). Enjoy!
##############################
#!/bin/sh
# this shell script finds all the tables for a database and run a command against it
# @date 6/14/2006
# @author Son Nguyen
DBNAME=$2
printUsage() {
echo "Usage: $0"
echo " --optimize <tablename>"
return
}
doAllTables() {
# get the table names
TABLENAMES=`mysql -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`
# loop through the tables and optimize them
for TABLENAME in $TABLENAMES
do
mysql -D $DBNAME -e "OPTIMIZE TABLE $TABLENAME;"
done
}
if [ $# -eq 0 ] ; then
printUsage
exit 1
fi
case $1 in
--optimize) doAllTables;;
--help) printUsage; exit 1;;
*) printUsage; exit 1;;
esac
##############################
Sample usage:
root@s# ./mysql_optimize.sh --optimize abc
This page of the documentation is over-optimistic. A power failure and a bad UPS left one of my 4Gb MyISAM files crashed. None of the examples in steps 1-3 worked. I never tried step 4 -- faced with the prospect of something "an order of magnitude slower" than what hadn't worked before, I decided to actually listen to the utility instead of the manual.
MyIsamChk would always get through nearly the entire file without error, then complain that links pointed outside the file, and that blob sizes hugely were too large. Assuming there were trashed pointers in the file, I used the --max-record-length=100000 (not mentioned on this page, but on the Myisamchk repair options page). That worked first time.
I recovered everything up to just minutes before the falure -- but I was down for hours repairing it. I have daily, weekly, and monthly backups. I'm now adding continuous backups.
Add your own comment.