Table gone.... forever?
My server crashed, yesterday. On this machine I have VMware and two virtual machines. One of these runs Zimbra mail (Open Source Edition). Zimbra used MySQL to store metadata and indexes of messages. ONE table got corrupted and after googling and trying to dump the table in order to recover it, I have ended up nowhere near a solution...
The problem:
The table "mail_item" seems beyond repair. However I need the data in it.
SELECT count(*) FROM 'mail_item';
...shows that there are over 75000 lines in the table, however a dump fails at row number 8200, crashing the MySQL database.
The mysqldump ends with:
zimbra@shenandoah:~/db/data/mboxgroup4$ mysqldump mboxgroup4 -S /opt/zimbra/db/mysql.sock -u root -f -p'*********' mail_item > /var/tmp/mboxgroup4.sql
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `mail_item` at row: 8200
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'mail\_item'': MySQL server has gone away (2006)
mysqldump: Couldn't execute 'UNLOCK TABLES': MySQL server has gone away (2006)
zimbra@shenandoah:~/db/data/mboxgroup4$ mysqldump mboxgroup4 -S /opt/zimbra/db/mysql.sock -u root -f --skip-triggers -p'*********' mail_item > /var/tmp/mboxgroup4.sql
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `mail_item` at row: 8200
mysqldump: Couldn't execute 'UNLOCK TABLES': MySQL server has gone away (2006)
...and several more variantions... anyway, mysqld crashes after a while. The logs show this:
;InnoDB: End of page dump
090201 21:20:23 InnoDB: Page checksum 1317410699, prior-to-4.0.14-form checksum 2715107171
InnoDB: stored checksum 3372273502, prior-to-4.0.14-form stored checksum 2715107171
InnoDB: Page lsn 0 280692670, low 4 bytes of lsn at page end 280692670
InnoDB: Page number (if stored to page already) 412,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 130
InnoDB: Page may be an index page where index id is 0 356
InnoDB: (index PRIMARY of table mboxgroup4/mail_item)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 412.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB:
http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
090201 21:21:23 InnoDB: We now intentionally generate a seg fault so that
InnoDB: on Linux we get a stack trace.
090201 21:21:23 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
Before that, it shows a lot of data from the table in the logs....
----------------------------------------------------------------
I've found parts of my problem everywhere using google, but no complete solution. Most of the stories tell me to dump the database and restore from the dump.
This morning I was able to make a dump (to /tmp). While restoring from this dump the machine locked up. After restarting the virtual machine /tmp was erased, the dump gone and the database had been corrupted even further.
Oh, and by the way... Due to lack of time, I haven't been able to set up a backup yet... Nothing to recover from. I know... stupid, there's a 1 TB disk waiting to be filled with backups.
I hope someone has come across this problem and knows how to my InnoDB tables...
Best regards,
Menno Pieters