MySQL: Corrupt ARCHIVE table. Help, Please?

Question

I'm a programmer and a total newbie as a sysadmin...

Today I started receiving error emails from our production site:

Incorrect key file for table 'xxx'; try to repair it

I found the server with 2 out of 4 cores pegged at 100% CPU by MySQL. Took down the site, the crons, everything, and about 10 minutes later CPU went to 0.

Trying to backup the DB (mysqldump) resulted in CPU at 100% again, for several minutes, without any real output to log file.

I tried REPAIR TABLE, got this:

+----------------------------+--------+----------+----------+
| Table                      | Op     | Msg_type | Msg_text |
+----------------------------+--------+----------+----------+
| db.table                   | repair | error    | Corrupt  |
+----------------------------+--------+----------+----------+

So that didn't work...

I thought of creating a new table (InnoDB) and "INSERT SELECT" from this one to try and recover some records, but SELECTING from the table (SELECT * FROM table LIMIT 1) also sends the CPU to 100% for a couple minutes, and again gives me the "Incorrect key file" error...

I got this error for 3 of the 4 tables in this DB, all 3 of them use the ARCHIVE engine and have several million records. (Largest is 46M records)

This started about an hour ago, all of a sudden.

Any ideas what I should do next? I would ideally like to lose as few records as possible, but at the same time I need to get the site back up ASAP.

Any help will be greatly appreciated!
Thank you!
Daniel

How recent is the most recent backup, to get an idea of how many records you'd lose? — Bart Silverstrim, Aug 04 '11 at 13:03
Sorry I missed this comment. I'd hope last night. My client's supposed to have arranged backups with the hosting company, but I don't know for a fact. I'm hoping to not have to count on Backups, ideally, and there may be none. — Daniel Magliola, Aug 04 '11 at 13:15

score 0 · Answer 1 · answered Mar 30 '22 at 19:02

We had a corrupt ARCHIVE format table. Looked like it crashed in the middle of OPTIMIZE or REPAIR, with the size of the files.

Running REPAIR TABLE $tablename EXTENDED; twice fixed it, as per this old bug report: https://bugs.launchpad.net/maria/+bug/881383

Worth a try before running to your backups.

score 0 · Answer 2 · answered Aug 04 '11 at 13:06

0

MySQL had a long-standing bug with Archive tables starting to corrupt after they got bigger than 2 GB. How big is your table?

answered Aug 04 '11 at 13:06

Janne Pikkarainen

31,454
4
56
78

Hi, thank you for your answer. How do I check that? (NOTE: Trying to query information_schema.tables sends the CPU to 100% again) :-( – Daniel Magliola Aug 04 '11 at 13:07
In an old dev copy that I have (few weeks / months ago), largest table is 365 Mb, it can't have grown to 2Gb in the time since that dump... – Daniel Magliola Aug 04 '11 at 13:09
Never say never, check at the file system level. And is there disk space left in your DB partition? – Janne Pikkarainen Aug 04 '11 at 13:10
How can I check at the FS level? (Sorry, i'm a noob at this) There's 100Gb free according to DF – Daniel Magliola Aug 04 '11 at 13:14
See with ls command in your MySQL data directory, /var/lib/mysql or so. – Janne Pikkarainen Aug 04 '11 at 13:15
Damn, don't have permissions to that folder :-( (not a root at this server) Any other options? – Daniel Magliola Aug 04 '11 at 13:17
What does SHOW TABLE STATUS tell you? – Janne Pikkarainen Aug 04 '11 at 13:22
Ok, managed to get in there.... None of the files are even close to 1Gb. Closest is 800 Mb – Daniel Magliola Aug 04 '11 at 13:28
SHOW TABLE STATUS kills CPU for several minutes and doesn't show anything. I've killed it after 5 minutes. – Daniel Magliola Aug 04 '11 at 13:29
Ok, so not about that bug this time. Then I have no idea. – Janne Pikkarainen Aug 04 '11 at 13:30

MySQL: Corrupt ARCHIVE table. Help, Please?

2 Answers2