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:
- 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.
- Remove innodb_force_recovery from the my.cnf if you inserted it
- Login as zimbra
su zimbra
- Restart the mysql server
mysql.server restart
- Load the MySQL account data into shell variables
source ~/bin/zmshutil ; zmsetvars
- Log into MySQL using the root account
mysql -u root --password=$mysql_root_password
- Open the first database that is corrupt:
USE mboxgroup9;
- 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!
- Go back to step 7 and open the next database that is corrupt until all have been repaired.
Now exit the MySQL prompt:exit;
- 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”. 🙂
- 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.
Thanks that was just perfect!!!!
Comment by Bob — 15. April 2017 @ 01:18
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
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
Thank you a lot, that helped me
Comment by Oviri — 6. January 2018 @ 09:33
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
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
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
Thanks friend
Comment by Nguyên — 20. February 2023 @ 10:46