Table corrupted. Restoring backup will not fix

[gnznroses]

One of our database tables became corrupted during an apparent server crash. The table gets written to almost 24/7 so I'm sure it was in the midst of a write when the server crashed. I spent several hours trying to restore the table from our daily backups, but ran into a number of different issues and had no luck. It kept saying the table was corrupt even when it was a restored backup version. I eventually had to delete the table and start it over from scratch, but I'm hoping to be able to recover and re-import the data.

 

I'm hoping someone can explain a bit about the issues I had, because I don't want the table to be corrupted again if I start importing data back in.

 

I'll try and keep it short. Things went about like this:

 

1. I discovered that any new inserts to the table were failing. It would always return an error saying that the new record was a duplicate on ID 55097424. Should be impossible since it's auto-increment (the only unique column). The auto-increment value was at 55097423.

 

2. I did a Check Table and it told me:

Table is marked as crashed

4 clients are using or haven't closed the table

Found key at page 3795393536 that points to record outside the data file

Corrupt

 

3. I began a Repair Table but then realized that it would take ages (17gb table) so I stopped it.

 

4. I renamed the table's three files (.frm, .myd, .myi) as a backup (table_bak) and started a new repair while I retrieve yesterday's copy of the table from our backup server.

 

5. Retrieved yesterday's backup, when everything was working OK. Copied those three files into the mysql directory. The database (and the corrupted "bak" copies) both show up in phpmyadmin just fine.

 

6. Inserts are still failing, even with the restored table, with the same error as before (except with a lower auto-increment ID, since the backup had fewer rows). I run a Check Table and it tells me it's corrupt also.

 

7. At this point the table becomes inaccessible from phpmyadmin, since it's now marked as crashed, but now when I click on it I receive an error "Got error 28 from storage engine" (instead of "Table xxx is marked as crashed and should be repaired"). This apparently means "out of storage space" but our mysql drive is 48% free. Our /tmp drive however is 100% full. Killing the repair on the bak copy brings our /tmp usage back to 4% and now I get "Table xxx is marked as crashed and should be repaired" again.

 

8. I tried restoring a backup from 2 days priot and it says it's corrupt also.

 

9. I gave up and deleted the table, re-created it, and set the auto-increment a little above what it was before the crash, so I can fill in the old data later, if possible. Everything works fine.

 

 

I'm stumped as to why it kept saying my table was corrupt, even on the backup copies. Almost like it is remembering that it's corrupt rather than actually checking if it is, but it shouldn't be doing that. I read that the "flagged as crashed" flag is in the index file (.myi) that I restored/over-wrote. So now I'm wondering if I am able to repair the old table and get my data back, if it's safe to import it back into my new table.

[fenway]

You can always re-create just the MYI.