Results 1 to 4 of 4

Thread: mysql data store rebuild

  1. #1
    ashelley is offline Junior Member
    Join Date
    May 2006
    Posts
    5
    Rep Power
    9

    Default mysql data store rebuild

    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?

  2. #2
    ashelley is offline Junior Member
    Join Date
    May 2006
    Posts
    5
    Rep Power
    9

    Default

    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.

  3. #3
    ashelley is offline Junior Member
    Join Date
    May 2006
    Posts
    5
    Rep Power
    9

    Default

    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..

  4. #4
    weaksauce is offline New Member
    Join Date
    Aug 2008
    Posts
    4
    Rep Power
    7

    Default

    As an update to this thread you might find this a good place to inject received by dates into your stream. It looks like you will lose some of the associated metadata such as read/unread status, if it was sent by you, etc.... but as a first pass approximation this should work well.

    [SOLVED] Using zmlmtpinject

    There may be a sql command that you could run to update the flags in the mail_item table to reflect when something is sent by the owner of the mailbox to mark it as being a sent item but I don't know the flag structure or if that is how they track sent status.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Zimbra fails after working for 2 weeks
    By Linsys in forum Administrators
    Replies: 10
    Last Post: 10-07-2008, 12:42 AM
  2. Zimbra wont start, only LDAP...
    By redbaron in forum Installation
    Replies: 14
    Last Post: 08-10-2007, 11:17 PM
  3. rebuilding mysql data
    By cangeceiro in forum Administrators
    Replies: 1
    Last Post: 12-06-2006, 03:23 PM
  4. Replies: 18
    Last Post: 03-20-2006, 02:22 PM
  5. Can't start MySQL to init db - Gentoo
    By fak3r in forum Installation
    Replies: 3
    Last Post: 10-10-2005, 06:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •