Serious MySQL table repair help

November 15, 2008 1:22 PM

Are there uncommon ways to repair a corrupt MySQL table? I have a MySQL database that is experiencing some serious corruption. Using the standard instructions, I have repaired all of the tables but one. No matter how many ways I try those instructions on the ornery table, in all cases either a) nothing happens or b) the data file is emptied, which is, obviously, not the result I want.

I linked to the instructions above because I wanted to make it plain I have tried *all* of the instructions there, on two different servers (one BSD, one Mac OS X), and have had no joy including use of "REPAIR TABLE tbl_name USE_FRM"). Also, I do not have a good backup. I have only the corrupt database table to work with (it's somebody else's server--they are highly unsavvy).

In its default corrupt state, I get the following when I check the file:

check warning Size of datafile is: 4791392 Should be: 0
check error Wrong bytesec: 0-0-0 at linkstart: 0
check error Corrupt

The "should be" size is wrong. This means there's some discrepancy between the MYD, MYI, and .FRM files. If I repair the file with MySQL, I get

repair info Wrong bytesec: 0- 0- 0 at 0; Skipped
repair status OK

This makes the file 0 bytes, which is wrong. Then I restore the copy of the corrupt file and try all the steps on the MySQL repair page. They do not solve the problem, so I am looking for other ideas and other solutions.

posted by Mo Nickels to Computers & Internet (6 answers total)

Right, you're using the repair options available when the serve is running. Have you tried stopping the server and using myisamchk directly on the .MYI file?

Please include all that you've done, by copying the shell/ mysql command command and any output.
posted by orthogonality at 2:13 PM on November 15, 2008

Well, first thing you should do is shutdown MySQL and backup the affected files. It's not unheard of for MySQL to truncate a file while performing a repair.

Then you need do what orthogonality suggests. Some additional info might be helpful: MySQL version, BSD/OS X versions, what filesystems are involved (obviously HFS+ for OS X, but is journaling enabled?). For example, XFS likes to zero out files on recovery that have suspect metadata.

Also, what do you think led to this corruption? Bad disks? Bad memory? Crashed MySQL? Crashed server OS?
posted by sbutler at 2:41 PM on November 15, 2008

Response by poster: I'm indeed working on these files outside of a running MySQL instance but did try them on a running instance, just to see.

I speculate that updating the server from MySQL 4 to 5 did the damage, but the ISP, Pair, won't admit it. Mac OS X is only being used to repair the file. It was not the system in use when the file was corrupted. I do not currently have the information on the corrupting system and what it was running.

Here's the last batch of attempts, though there were many, many others; these are just the ones still in my scrollback. The table in question is pm_weblog. The file was replaced before each attempt below with a copy of the corrupted version.

% /bin/myisamchk -r -q pm_weblog
- check record delete-chain
- recovering (with sort) MyISAM-table 'pm_weblog'
Data records: 0
- Fixing index 1
Wrong bytesec: 0- 0- 0 at 0; Skipped
MyISAM-table 'pm_weblog' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

% /bin/myisamchk --safe-recover pm_weblog
- recovering (with keycache) MyISAM-table 'pm_weblog'
Data records: 0
Wrong bytesec: 0- 0- 0 at 0; Skipped

% /bin/myisamchk -r pm_weblog
- recovering (with sort) MyISAM-table 'pm_weblog'
Data records: 0
- Fixing index 1
Wrong bytesec: 0- 0- 0 at 0; Skipped

% /bin/myisamchk -f pm_weblog
Checking MyISAM file: pm_weblog
Data records: 0 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
myisamchk: warning: Size of datafile is: 4791392 Should be: 0
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 0
- recovering (with sort) MyISAM-table 'pm_weblog'
Data records: 0
- Fixing index 1
Wrong bytesec: 0- 0- 0 at 0; Skipped

% /bin/myisamchk --safe-recover pm_weblog/MYI
myisamchk: error: File 'pm_weblog/MYI' doesn't exist

% /bin/myisamchk --safe-recover pm_weblog.MYI
- recovering (with keycache) MyISAM-table 'pm_weblog.MYI'
Data records: 0
Wrong bytesec: 0- 0- 0 at 0; Skipped

% /bin/myisamchk -r -q pm_weblog
- check record delete-chain
- recovering (with sort) MyISAM-table 'pm_weblog'
Data records: 0
- Fixing index 1
Wrong bytesec: 0- 0- 0 at 0; Skipped
MyISAM-table 'pm_weblog' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

% /bin/myisamchk --safe-recover pm_weblog
- recovering (with keycache) MyISAM-table 'pm_weblog'
Data records: 0
Wrong bytesec: 0- 0- 0 at 0; Skipped

% /bin/myisamchk -e *.MYI
myisamchk: error: 'pm_weblog.MYI' is not a MyISAM-table

% /bin/myisamchk -r -q pm_weblog
- check record delete-chain
- recovering (with sort) MyISAM-table 'pm_weblog'
Data records: 0
- Fixing index 1
Wrong bytesec: 0- 0- 0 at 0; Skipped
MyISAM-table 'pm_weblog' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

% /bin/myisamchk -o pm_weblog
- recovering (with keycache) MyISAM-table 'pm_weblog'
Data records: 0
Wrong bytesec: 0- 0- 0 at 0; Skipped

% /bin/myisamchk -r -f pm_weblog
- recovering (with sort) MyISAM-table 'pm_weblog'
Data records: 0
- Fixing index 1
Wrong bytesec: 0- 0- 0 at 0; Skipped
posted by Mo Nickels at 6:28 PM on November 15, 2008

I got nothing; can you try recovery using MySQL 4.0 tools?
posted by orthogonality at 11:20 AM on November 16, 2008

Or by looking at the raw data in a hex editor and seeing if there's any recoverable structure?
posted by orthogonality at 11:21 AM on November 16, 2008

Response by poster: It turned out that the data was beyond recoverable.
posted by Mo Nickels at 3:37 AM on December 16, 2008