0

I'm trying to recover a corrupted MyISAM table from a legacy system. The table is corrupted when there is a sudden power loss. It has little over 500 million rows and is 23GB is size (.MYD file)

Usually, running this command myisamchk -r -v -f table_name fixes the table and I can get the DB up and running. This time was a little different. Running this command results in this error:

- recovering (with sort) MyISAM-table 'table_name'
Data records: 545297330
- Fixing index 1
- Searching for keys, allocating buffer for 596523227 keys
Found block that points outside data file at 24215711712
Wrong bytesec: 49- 52- 57 at 2740875236; Skipped
Wrong bytesec: 49- 53- 56 at 2740875256; Skipped
Found block that points outside data file at 24215711712
Wrong bytesec: 49- 52- 57 at 2740875236; Skipped
Wrong bytesec: 49- 53- 56 at 2740875256; Skipped
myisamchk: error: Key 1 - Found too many records; Can't continue
MyISAM-table 'table_name' 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

If I start with -o option it just goes on a loop stating that there is a Duplicate key 1 for record at 16412879816 against new record at 16412879816 and after running almost 8 hours, nothing was changed.

I've tried REPAIR TABLE table_name; which creates a .TMD file, fills it up to 33GB, then resets .TMD file to 0B and starts over. It does update .MYI file but it does not change it's size. After 10 hours nothing has changed.

I've also tried REPAIR TABLE table_name USE_FRM; which after doing something for 20min eats up all the ram (32GB RAM/32GB swap).

What are my other options of recovering the table?

I'm OK with data loss of latest written rows. It is frequent telemetry data that gets aggregated so a latest few measurements lost is not a problem.

MariaDB version: 5.7.33

EDIT: It appears that there is something that is causing the repair to run in a loop.

Checking MyISAM file: table_name
Data records: 104616403   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check record links
myisamchk: error: Recordlink that points outside datafile at 24215711712
MyISAM-table 'table_name' is corrupted
Fix it using switch "-r" or "-o"

# myisamchk table_name -r   
- recovering (with keycache) MyISAM-table 'table_name'
Data records: 104616403
Found block that points outside data file at 24215711712
Found block that points outside data file at 24215711712
Found block that points outside data file at 24215711712
Found block that points outside data file at 24215711712
Found block that points outside data file at 24215711712
Found block that points outside data file at 24215711712
32027100000000

1 Answer 1

1

Give this a try:

  1. Make note of the INDEXes (including PRIMARY KEY and UNIQUE). One way is "SHOW CREATE TABLE .."
  2. Drop each of the indexes.
  3. Re-ADD those indexes.

In the future, consider switching to InnoDB. And consider summarizing the data 'immediately' and not saving more than a few hours/days worth of data.

More

Since .TMD was mentioned, it sounds like you ran out of disk space. Look at the size of both the .MYD and .MYI. Then make sure you have at least that much free space in two places:

  • The filesystem where the data is stored, and
  • the filesystem indicated by the variable tmpdir.

(That may be overkill.) (The two places may be the same filesystem; depending on OS and MySQL configs.)

Another approach

Simply delete the .MYI file before attempting the Repair. If there is enough free disk space where it is needed, Repair will discover the missing indexes and rebuild them.

6
  • 1
    I have tried the suggested but none worked. In the end I wrote my own parser for my specific MYD file and extracted all the rows to csv. During the extraction I found out that the last row wasn't finished writing and so the MYD file was corrupted. I removed the last X bytes that corresponded to the unfinished record, ran the regular myisamchk -r -q table_name command that rebuild index file and was up and running! Sep 3, 2022 at 14:12
  • @CodeBreaker - Yet another example of why to move to InnoDB.
    – Rick James
    Sep 3, 2022 at 16:06
  • @CodeBreaker "I wrote my own parser for my specific MYD" How did you write the parser from MYD to CSV? Is it possible to find the source online?
    – serguei
    Sep 9, 2023 at 8:07
  • I have to find the source somewhere but it wouldn't do you much good since the data is written to MYD file based on your table structure. I didn't write the parser generically, just raw dog reading the bytes and using the table definition to hardcode what comes next. The reference to mysql docs as well as myisam source code helped. dev.mysql.com/doc/refman/8.0/en/storage-requirements.html Sep 11, 2023 at 5:05
  • Simple tables are easy to parse in the .MYD. Minor complications come in with free space, fragmented rows, binary representation of FLOAT (etc), length fields, big-endian vs little-endian [MySQL's representation is consistent across all hardware, but your target may be different], etc. Show me a short hex dump, to gether with CREATE TABLE; I provide more specifics for that case.
    – Rick James
    Sep 13, 2023 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.