Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Read a mysql .MYD file without a working .MYI file?

by japhy (Canon)
on Nov 15, 2008 at 21:56 UTC ( [id://723852]=perlquestion: print w/replies, xml ) Need Help??

japhy has asked for the wisdom of the Perl Monks concerning the following question:

The prodigal hacker returns. I've been in Perl Purgatory (where I have to use PHP a lot)...

I have a 42 GB mysql table which appears to be SLIGHTLY corrupted (due to disk space running out, I think). The last record in the table ends abruptly (I know this by 'tail'ing the file). mysql seems to be able to repair the DATA, but it fails to rebuild its index (sort_buffer is too small, it says, even though I've got it maxed out at 4 GB).

I'd very much like to use the .frm (table format) file and the .MYD (data) file and go through extracting the data for insertion into a new table. Is there a module out there for doing that already?

Jeff japhy Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and perl hacker
Nos autem praedicamus Christum crucifixum (1 Cor. 1:23) -- The Cross Reference (My Blog)

Replies are listed 'Best First'.
Re: Read a mysql .MYD file without a working .MYI file?
by snowhare (Friar) on Nov 16, 2008 at 18:40 UTC
    I have no idea if it will work given your corruptions, but have you tried just dumping (mysqldump) and reloading it?
Re: Read a mysql .MYD file without a working .MYI file?
by oko1 (Deacon) on Nov 17, 2008 at 01:39 UTC

    This might be a somewhat backwards and hand-wavy approach; I only suggest it because, well, it worked for me once when I really needed it. Is there an easy way (or even a semi-difficult one) for you to delete or fix that last record manually? MySQL might start doing The Right Thing if you present it with clean data.

    (If you have no interest in the details, don't click the 'Read More' link. The above says it all, really. :)


    --
    "Language shapes the way we think, and determines what we can think about."
    -- B. L. Whorf
Re: Read a mysql .MYD file without a working .MYI file?
by converter (Priest) on Nov 17, 2008 at 14:30 UTC
      I looked at the docs, yes, and I've tried various repairs. There are numerous errors about bad links going outside the data file (these occur as the .MYD file is being regenerated). The killer errors come up during the rebuilding of the index:
      | itrade_new.offers | repair | info | Found link that points at 82 +43107105727870068 (outside data file) at 42211469888 | | itrade_new.offers | repair | info | Found link that points at 82 +43107105727870068 (outside data file) at 42211469944 | | itrade_new.offers | repair | info | Found link that points at 82 +43107105727870068 (outside data file) at 42211470004 | | itrade_new.offers | repair | info | Found link that points at 44 +71241038974708841 (outside data file) at 42211470120 | | itrade_new.offers | repair | info | Found link that points at 44 +71296179294533182 (outside data file) at 42211470284 | | itrade_new.offers | repair | info | Found link that points at 83 +67816172579929378 (outside data file) at 42211470300 | | itrade_new.offers | repair | info | Found link that points at 33 +42363012281692769 (outside data file) at 42211470900 | | itrade_new.offers | repair | info | Found link that points at 44 +71433271728779990 (outside data file) at 42211470952 | | itrade_new.offers | repair | info | Found link that points at 73 +7591309940387956 (outside data file) at 42211471032 | | itrade_new.offers | repair | info | Found link that points at 44 +71296179294533182 (outside data file) at 42211471756 | | itrade_new.offers | repair | info | Found link that points at 10 +612976429166 (outside data file) at 42211471860 | | itrade_new.offers | repair | info | Found link that points at 78 +10403350942084128 (outside data file) at 42211471872 | | itrade_new.offers | repair | info | Found link that points at 71 +64208255722136163 (outside data file) at 42211472196 | | itrade_new.offers | repair | info | Found link that points at 82 +43107105727870068 (outside data file) at 42211472360 | | itrade_new.offers | repair | error | sort_buffer_size is to small + | | itrade_new.offers | repair | error | Can't copy datafile-header t +o tempfile, error 9 | | itrade_new.offers | repair | status | Operation failed
      That "sort_buffer_size" error comes up even when I set that value to 4G (the max value permitted). I haven't found much helpful information concerning "Can't copy datafile-header to tempfile, error 9".

      Jeff japhy Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and perl hacker
      Nos autem praedicamus Christum crucifixum (1 Cor. 1:23) - The Cross Reference (My Blog)
        Have you run fsck to make sure your filesystem isn't hosed? Never hurts to be paranoid.

        REPAIR TABLE rebuilds the entire table and index, so you'll need to have enough diskspace. The error 9 is probably mysql running out of space during the rebuild. You won't notice it after because it discards the temp files.

        You'll need table size + index size + extra to do a full repair. Also, because mysql doesn't run as root, you won't have your entire disk available for the repair (if you're running Linux on ext2 or ext3) because of the reserved blocks. Check tune2fs's manpage for more info on those...

        I've worked with 80Gb+ tables more then I care for, and have run into this sort of issue way too often...

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://723852]
Approved by GrandFather
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others romping around the Monastery: (6)
As of 2024-03-29 01:17 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found