ZCS 8.7: Problem with database corruption

[oayfer2]

Hello everyone,

I am one of those weirdos who likes to run his own mail server for personal purposes. It used to serve as a development setup for the Zimbra NE instance I ran at work with ~200 users, but now is simply for my immediate family's accounts. I need help because my FOSS Zimbra instance is suffering from a corrupted database, which is the first time I have had any technical difficulties with Zimbra in 9+ years. I have unofficial FOSS backups, but they are copies of the corrupted situation.

There are no visible symptoms, i.e., mail, calendar, etc. all seem to be working fine; however I noticed that mysql_error.log has been spewing frequent errors for several weeks that look like:

2016-12-08 02:17:06 7fa064f3ab00 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is INT UNSIGNED
NOT NULL but should be BINARY(4) NOT NULL (type mismatch).
2016-12-08 02:17:06 7fa064f3ab00 InnoDB: Error: Fetch of persistent statistics requested for table "zimbra"."config" but
the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected str
ucture. Using transient stats instead.

This is preventing me from cleanly starting/stopping the services and therefore upgrading from 8.7.0 to 8.7.1, which is when I noticed the mess.

I am also motivated to move Zimbra to a new server as the current hardware is over 4 years old, and figure I can use this excuse to tidy up after the corruption.

I need advice on the best course of action that will help me end up with a clean installation without database issues. I am okay with setting up a fresh machine and migrating the mail, as long as there is a good way to move configuration such as the hundreds of email aliases and things like spam filtering settings tuned over many years. I know there are many guides to rsync based migrations but I worry these will not solve my corruption issues.

I am currently on 8.7.0 64-bit running on CentOS 6.7 and would ideally like to end up on CentOS 7.x.

Please let me know your thoughts and whether I can add any more detail to this cry for help.

I have enjoyed this community for a long time both professionally and personally, and I could really use sound advice.

Thanks!

[phoenix]

The obvious advice comes first, before you do anything else shutdown ZCS and take a full backup of the /opt/zimbra directory structure (there's articles in the wiki fon this). Take a look at the following wiki article on DB recovery: https://wiki.zimbra.com/wiki/Mysql_Crash_Recovery

[shockwavecs]

I'd start with doing the backup as well. This is a mysql issue so start with that right after. You'll be trying random things, so the backup is so important.

I would search for "Column last_update in table "mysql"."innodb_table_stats" is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch)."

just a quick note...the mysql issue happened to me recently. I did not fix the problem, but used a "livesync" copy of my server to overwrite the problem per se. This is a zimbra FOSS HA solution. The reason it worked is the data is synced on a file level but the databases for ldap and mysql are inserted by "replay" via redo logs. So the queries are inserted on the fly, unlike other types of mysql replication. This meaning the bad primary servers mysql instance was not the same on the secondary server. It's one of those times I loved the fact that they used non standard mysql replication.

[oayfer2]

Bill: good to know you're still here! Thanks for the suggestions and yes, I have multiple backups made nightly.

shockwavecs: the livesync idea sounds really promising.

Is this the specific set of recipes that you would suggest:

https://wiki.zimbra.com/wiki/Server_Live_sync

?

[JDunphy]

This looks very similar to what I saw on my servers during their upgrades to 8.7+... I followed the advice in this thread https://forums.zimbra.org/viewtopic.php?t=60288 to clear it up.
It was weird because the only place I noticed any issues was in the mysql error logs. I saw it with my centos 6.8 machines.

Basic process as outlined in thread above was:

1. Obtain mysql root password:
$ zmlocalconfig -s | grep mysql | grep password

2. Create missing directory and symbolic link:
$ mkdir /opt/zimbra/data/tmp/mysqldata
$ ln -s /opt/zimbra/data/tmp/mysql/mysql.sock /opt/zimbra/data/tmp/mysqldata/mysql.sock

3. Perform mysql_upgrade
$ /opt/zimbra/common/bin/mysql_upgrade -u root -p

Done.

[racerock]

Agreed, I too had this problem various Ubuntu 14.04 based 8.6 Zimbra, I fixed this on two of my servers with the suggested fix posted here: viewtopic.php?t=60288,
In my conclusion from various servers it was an error in the script that failed to upgrade the mysql on any 8.6 installs upgraded to 8.7 it carries across to 8.71 as far as I can see.

[bulletxt]

This is obviously a Zimbra bug. Has someone reported it on bugzilla? This needs to get fixed.

[JDunphy]

This is obviously a Zimbra bug. Has someone reported it on bugzilla? This needs to get fixed.

This seems to be the bug: https://bugzilla.zimbra.com/show_bug.cgi?id=106012

[oayfer2]

Folks, thanks for pointing out this known issue. Mine turns out to be the same, and the suggested fix cleared frequent errors. Since then, I only got a couple of these in the last ~24 hours:

InnoDB: index "i_conv_id" of table "mboxgroup9"."open_conversation" is corrupted
InnoDB: load corrupted index index "i_conv_id" of table "mboxgroup9"."open_conversation"

I am going to try using the live sync method mentioned earlier and report back.

[shockwavecs]

Oh I am not suggesting the live sync will fix a mysql error on a system that is currently a single server moving to a live sync setup. During the setup you have to rsync the entire /opt/zimbra directory which would just replicate the bad mysql data. I am only suggesting that a live sync setup is nice to have and that it would help you in a mysql database corruption related situation. It appears there is a bug and you should follow the fixes above. livesync would be helpful if you already have it setup and run into a issue.