DeutschEnglish

Submenu

 - - - By CrazyStat - - -

26. March 2017

Zimbra: fix corrupt index open_conversation in an mboxgroup MySQL-DB

Filed under: DBMS,Linux,Server Administration — Tags: , , , , , , — Christopher Kramer @ 20:50

Just wanted to upgrade a Zimbra server from 8.7.3 to 8.7.5. The upgrade always asks you whether to check database integrity. Even it was only a minor upgrade, I chose yes to be on the safe side. And it turned out the MySQL DB was indeed corrupt.

I had seen corrupt zimbra dbs a lot and the “MySQL crash recovery” guide in the zimbra wiki always helped out. But not this time.

I tried the crash recovery as explained in the wiki. When doing dumps, four mboxgroup-databases always failed because the index open_conversation of the table open_conversation was corrupt. As the guide explains, I increased innodb_force_recovery step by step from 1 to the maximum 6, but the error did not go away.

So here is what helped:

  1. Try to create the dumps as explained in the crash recovery guide. You will get errors like this:
    Dumped mboxgroup8
    mysqldump: Error 1712: Index open_conversation is corrupted when dumping table `open_conversation` at row: 0
    Dumped mboxgroup9

    This means that mboxgroup9 (not 8!) is corrupt. Write down all the mboxgroup numbers where an error appeared.

  2. Remove  innodb_force_recovery from the my.cnf if you inserted it
  3. Login as zimbra
    su zimbra
  4. Restart the mysql server
    mysql.server restart
  5. Load the MySQL account data into shell variables
    source ~/bin/zmshutil ; zmsetvars
  6. Log into MySQL using the root account
    mysql -u root --password=$mysql_root_password
  7. Open the first database that is corrupt:
    USE mboxgroup9;
  8. Repair the corrupt open_converstation table:
    OPTIMIZE TABLE open_conversation;

    Note: If this fails, check if you really removed innodb_force_recovery from the my.cnf!

  9. Go back to step 7 and open the next database that is corrupt until all have been repaired.
    Now exit the MySQL prompt:

    exit;
  10. You can now continue with the crash recovery, it should now create all dumps correctly. But if the open_conversation tables where the only corruption problem, you could also just stop here as this should have fixed the corruption. In my case, I jus started the upgrade again and let it verify message store database integrity again, and this time it completed with “No errors found”. 🙂
  11. Clean up the MySQL dumps
     rm -R /tmp/mysql.db.list /tmp/mysql.sql/

Please let me know if this helped you or if you have some additions.

Recommendation

Try my Open Source PHP visitor analytics script CrazyStat.

8 Comments »

  1. Thanks that was just perfect!!!!

    Comment by Bob — 15. April 2017 @ 01:18

  2. Thanks a lot. I’m planing migrate from Zimbra 8.6 to 8.7.11 but when I check the database found a mailboxgroup corrupted. This information was perfect, but on Zimbra 8.6 i must use in step 8: ALTER TABLE instead of OPTIMIZE TABLE.
    Greetings from Cuba.

    Comment by eallende — 10. August 2017 @ 19:52

  3. Thank you for posting this. It was a relief at the end of a long day. I said a prayer since databases are beyond me and then I found your site. Many thanks!

    Comment by Jacob — 27. November 2017 @ 20:23

  4. Thank you a lot, that helped me

    Comment by Oviri — 6. January 2018 @ 09:33

  5. Thank you. It works !
    Mine just corrupted after power outage. Not by upgrade.
    Here are the messages from mysql_error.log

    InnoDB: index “i_parent_id” of table “mboxgroup6″.”mail_item” is corrupted
    InnoDB: load corrupted index index “i_parent_id” of table “mboxgroup6″.”mail_item”
    InnoDB: index “i_folder_id_date” of table “mboxgroup6″.”mail_item” is corrupted
    InnoDB: load corrupted index index “i_folder_id_date” of table “mboxgroup6″.”mail_item”
    InnoDB: index “i_date” of table “mboxgroup6″.”mail_item” is corrupted
    InnoDB: load corrupted index index “i_date” of table “mboxgroup6″.”mail_item”
    InnoDB: index “i_mod_metadata” of table “mboxgroup6″.”mail_item” is corrupted
    InnoDB: load corrupted index index “i_mod_metadata” of table “mboxgroup6″.”mail_item”
    InnoDB: index “i_conv_id” of table “mboxgroup6″.”open_conversation” is corrupted
    InnoDB: load corrupted index index “i_conv_id” of table “mboxgroup6″.”open_conversation”
    [ERROR] Got error 180 when reading table ‘./mboxgroup6/mail_item’

    my step.

    su – zimbra
    source ~/bin/zmshutil ; zmsetvars
    mysql -u root –password=$mysql_root_password
    USE mboxgroup6;
    OPTIMIZE TABLE open_conversation;
    OPTIMIZE TABLE mail_item;

    Comment by James — 19. March 2018 @ 03:51

  6. Two days analyzing whats the procedure to follow for repair a server with 400 mailboxes and 3 tb of information and later find your post! Thank you my friend! you save me.

    Comment by Jose Miguel Marcano — 19. December 2018 @ 01:51

  7. Thanks a LOT! I had to use ALTER for one table when OPTIMIZE gave me an error (so errors don’t only depend on innodb_force_recovery set, the error was Table ‘mboxgroup18/#sql-ib4290-2763060859’ already exists) but for the other DB OPTIMIZED worked well.
    You saved my day, thanks again

    Comment by markit — 1. June 2019 @ 14:40

  8. Thanks friend

    Comment by Nguyên — 20. February 2023 @ 10:46

RSS feed for comments on this post. TrackBack URL

Leave a comment