mysql data store rebuild

[ashelley]

In a nutshell, mailbox3 has gone kaboom. From the mysql log:

InnoDB: stored checksum 1643286861, prior-to-4.0.14-form stored checksum 2860349746
InnoDB: Page lsn 0 10615170, low 4 bytes of lsn at page end 10615170
InnoDB: Page number (if stored to page already) 155,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 15
InnoDB: Page may be an index page where index id is 0 63
InnoDB: (index PRIMARY of table mailbox3/mail_item)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 155.

I know it is mailbox3 because simple queries there cause it to crash:

mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: mailbox3

The other mailbox databases appear to be fine. Before the obvious is pointed out, there's no mysqldump of that database , so it cannot be restored. However, the message stores and such all appear to be just dandy.

Dropping the database and re-initializing is easy enough, since the schema can be taken from another mailbox. While obviously appointments and address books and such are lost, it appears to be possible to rebuild the mail_item table (or enough of it) from the messages themselves to make the mailbox functional again. A little perl, a little patience, new table.

The hitch in that plan is that I can't seem to find any doc/discussion on just what comprises the fields in the table. From looking at another mailbox, obviously rows of type 6 are address book data, 5 and 4 are message data, type 1 is data on various folders. The various metadata bits and such are, of course, not obvious.

I could not find mention of any of this in architecture docs, though if I missed it just point it out and I'll go read some more.

Pointers?

[ashelley]

An update of myself, more for the curious (since most people would simply tell 'em "Nope, it's all gone" and recreate the user) and perhaps Google.

/opt/zimbra/db/create_database.sql has a few useful comments for the curious. You can use that as a reference for creating a new replacement database.

Comparing some other databases, I see that the first 11 rows for mail_item are the same, as the define the basic folders. The only changes are to the timestamp, which I'm not that worried about. Inserting these rows into the new mail_item (insert row 11 first and then rows 1-10 to avoid key constraint issues), make sure postfix is running correctly (lmtp tends to die when it can't query the mailbox db) and flush the queue.

Hey, queue empties and mail flows. Next is to "recover" the old mail. The messages are still in the message store, but the mailbox db knows nothing about them.

Zimbra-peeps: I hope I'm not revealing any secret-sauce from the Network Edition by accident.

[ashelley]

The final quick-and-dirty way to get something functional again. I'd still prefer something to be able to rebuild the entries from the messages themselves, but this allows quick access.

Nuke the trash and create table structure:

sudo su - zimbra
drop database mailbox3;
create database mailbox3;
create table mailbox3.mail_item like mailbox2.mail_item;
create table mailbox3.appointment like mailbox2.appointment;
create table mailbox3.tombstone like mailbox2.tombstone;
create table mailbox3.open_conversation like mailbox2.open_conversation;
insert into mailbox3.mail_item select * from mailbox2.mail_item where id <= 11;

Ok, copy out your old messages (just looking at 0 for now), nuke the store and then re-insert via zmlmtpinject (glad I read the migration thread). This will basicly re-deliver the messages, so timestamps will be off.

cd /opt/zimbra/store/0/3/msg/
rsync -av 0 /var/tmp
cd 0
rm -f *
cd /var/tmp/0
zmlmtpinject -r user -s root -D yourdomain.com *

This is not a substitute for a proper backup/restore. This is a "Huh? The analyst took the backups home and they were *stolen*?" last ditch to get the service back. Better methods welcomed.

Off to read the backup threads..