[SOLVED] Missing BLOB errors from zimbra-4.5.10

[shinacalypse]

hi. i have a serious error with my zimbra OSS setup.. due to a badly formatted rsync command, in trying to copy /opt/zimbra from an old to a new server, i corrupted my message data store without realizing it.

Now everyone has not only lost some two days of email, but in most users email accounts, i get a "missing blob"(eg. -ERR system failure: missing blob for id: 19512, change: 67500) error anytime most users (it seems) try to connect to zimbra using pop3 or imap, and it also pops up when trying to open some(randomly distributed) email messages when they try to use the zimbra webclient. I want to ask if there is some way of restoring these blobs? or what can actually be done?

im using zimbra-4.5.10, by the way and i cant really upgrade at this point (senior management disagrees with me). But would upgrading solve this?
eagerly anticipating answers... please!

[mmorse]

First make yourself a backup of the entire current /opt/zimbra (and any linked directories).

Is everything else fine, just the store is bad? Restore as much as you can into /opt/zimbra/store from backups.
Start by making sure permissions are fixed:

su - root

chown -R zimbra:zimbra /opt/zimbra/ (might just do this on the /opt/zimbra/store if everything else is fine)

cd /opt/zimbra/libexec/

./zmfixperms (not needed if you're saying it's just the store that's bad)
Ok, so there's the MySQL metadata DB, the Lucene index, and the actual blob files on disk in /opt/zimbra/store. If you can click on a folder in the web UI and get results, it means MySQL is up and happy. If you open a message and get the error, it means that the message files on disk aren't where they're supposed to be.
If you're managed to restore a bunch try re-indexing first, but you're probably gonna have to remove some stuff from the DB (example">http://www.zimbra.com/forums/administra ... ml>example) if your absolutely missing those blobs (the previously stated 2 days of email) then re-index again.
In 5.0.6 there's Bug">http://bugzilla.zimbra.com/show_bug.cgi?id=19927>Bug 19927 - tool to do consistency checks and repair for missing blob for ID x while the mailbox.log "com.zimbra.common.service.ServiceException: system failure: missing blob for id: 19512, change: 67500" can tell you just as much, zmblobchk is needed because you don't get all those errors in the log in an order, you get them when accessed, and you might want to spit out a list.
In 5.0.8 (target) this will take all the tough work out of it: Bug">http://bugzilla.zimbra.com/show_bug.cgi?id=27958>Bug 27958 - Add support for 'repair-mode' to the zmblobchk tool
I would read Account">http://wiki.zimbra.com/index.php?title= ... re>Account mailbox database structure - Zimbra :: Wiki so you understand the below.
Below is a Perl script that will reconcile the user's database to account for the missing blob(s). Run it like this: ./mailboxfixdb455.pl user@domain.com

The script will remove references to the missing blob. After running the script, it'll probably be a good idea to run reindex again just to make sure things are set.

mailboxfixdb.pl:



#!/usr/bin/perl
# This script compatible with Zimbra version 4.5.x only. Do not use with any other version.

# OK, there's 2 MAILBOX_*_BITS values in the VOLUME table.

# Take the mailbox ID, right-shift it by MAILBOX_BITS, and take the lowest MAILBOX_GROUP_BITS of the result.

# That's your mailbox hash.

# Take the message ID, right-shift it by FILE_BITS, and take the lowest FILE_GROUP_BITS of the result. That's your msgid hash.

# I think.

# //msg//-.msg
my ($fbits, $fgbits, $mbits, $mgbits, $basepath) = split (' ',`echo "select file_bits,file_group_bits,mailbox_bits,mailbox_group_bits,path from volume where type='1'" | mysql -N zimbra`);

my $ARGV = shift @ARGV;
chomp $basepath;
my $mbmask = sprintf "1" x $mgbits;

my $fmask = sprintf "1" x $fgbits;
foreach (`echo "select id, group_id, account_id, comment from mailbox where comment = '$ARGV'" | mysql -N zimbra`) {

chomp;

my $path = "$basepath/";

my ($id, $grid, $aid, $nm) = (split);

my $mbhash = $id >> $mbits;

$mbhash &= $mbmask;

$path .= $mbhash."/".$id."/msg/";

foreach my $msgstuff (`echo "select id, mod_content, type from mail_item where blob_digest is not null and mailbox_id=${id};" | mysql -N mboxgroup${grid}`) {

chomp $msgstuff;

if ($msgstuff eq "") {next;}

my ($msgid, $modContent, $type) = split (' ',$msgstuff);

my $msghash = $msgid >> $fbits;

# $msghash &= $fmask;

my $nm = $msgid;

if ($modContent) {$nm .= "-$modContent";}

my $npath = $path.$msghash."/".$nm.".msg";

if (-e $npath) {print $npath." OK
";}

else {

print $npath." NOT OK
";

# not ok, remove the entry from the database so it is not a nuisance

print "Delete from mail_item where MSGID is ${msgid} and MAILBOXID is ${id}
";

print "Uncomment line below me in script to have me delete.
";

# `echo "delete from mail_item where id=${msgid} and mailbox_id=${id}" | mysql -N mboxgroup${grid}`;

if ($type eq "11") {

print "Delete from appointment where MSGID is ${msgid} and MAILBOX_ID is ${id}
";

print "Uncomment line below me in script to have me delete.
";

# `echo "delete from appointment where item_id=${msgid} and mailbox_id=${id}" | mysql -N mboxgroup${grid}`;

}

}

# print $npath."
";

}

}

There are several ways to reindex:
The admin console GUI. (Which might be time consuming if you have a large # of accounts.)
http://i30.tinypic.com/izqgsm.jpg />
SOAP ReIndexRequest on each account (starts a java thread to re-index).
BTW when you do upgrade (I haven't read up on your other threads to find out why your mgt is objecting but it seems you sorted out http://www.zimbra.com/forums/installati ... -04-a.html) re-indexing is a native zmprov feature in 5.0: zmprov rim user@domain.com start. (status & cancel are also useful) Bug">http://bugzilla.zimbra.com/show_bug.cgi?id=15348>Bug 15348 - zmprov should be able to control reindexing from the command line
Quick script for that reindex_mailboxes:

for i in `zmprov gaa -s `

do

echo "reindexing $i"

zmprov rim $i start

done

It can easily slam your disk and CPU i/o. So you might put a "sleep 120" between each one to space it out a bit if you don't care how fast it got done - just need it to do it for all accounts without all at the same time.
Another method:

1. Set the problematic account into maintance mode

2. Get the mailbox id of the account as described.

3. Shutdown Zimbra (zmcontrol stop)

4. Delete the folder called /opt/zimbra/index/0/ (probably better to NOT delete and move this to a safe place)

5. Startup Zimbra - You will see that zimbra rebuilds the index of the mailbox. (zmcontrol start)

6. Wait until the reindexing is finished

7. Make the account active again.
UserID is system wide

MailboxID is per server store
To get those:

zmprov ga user@domain.com| grep -i zimbraId
If your on the appropriate mailserver:

zmprov getMailboxInfo user@domain.com

or globally:

/opt/zimbra/bin/mysql -e "use zimbra; select id from mailbox where account_id = 'UserID HERE including the leading 0'"
SCRIPTS:
This is from FRANKLIN/ZimbraServer/src/perl/soap/reindex.pl (The 5.0 script just seems to be a slight 2 line correction from the FRANK 4.5 script on password inputs - pw instead of p).

reindex.pl:

#!/usr/bin/perl -w

#

# ***** BEGIN LICENSE BLOCK *****

#

# Zimbra Collaboration Suite Server

# Copyright (C) 2005-2008 Zimbra, Inc.

#

# Software distributed under the License is distributed on an "AS IS"

# basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.

#

# ***** END LICENSE BLOCK *****

#
use Time::HiRes qw ( time );

use strict;
use lib '.';
use LWP::UserAgent;

use Getopt::Long;

use XmlElement;

use XmlDoc;

use Soap;

use ZimbraSoapTest;
my $ACCTNS = "urn:zimbraAdmin";

my $MAILNS = "urn:zimbraAdmin";
# If you're using ActivePerl, you'll need to go and install the Crypt::SSLeay

# module for htps: to work...

#

# ppm install http://theoryx5.uwinnipeg.ca/ppms/Crypt-SSLeay.ppd

#
# app-specific options

my ($mbox, $action, $types, $ids);
#standard options

my ($user, $pw, $host, $help); #standard

GetOptions("u|user=s" => $user,

"pw=s" => $pw,

"h|host=s" => $host,

"m|mbox=s" => $mbox,

"a|action=s" => $action,

"t|types=s" => $types,

"ids=s" => $ids,

"help|?" => $help);
if (!defined($user)) {

die "USAGE: $0 -u USER -m MAILBOXID -a ACTION [-pw PASSWD] [-h HOST] [-t TYPES] [-ids IDS]";

}
my $z = ZimbraSoapTest->new($user, $host, $pw);

$z->doAdminAuth();
my %args = ( 'action' => $action );


my $d = new XmlDoc;

$d = new XmlDoc;

$d->start('ReIndexRequest', $MAILNS, %args); {

my %mbxArgs = ( 'id' => $mbox );

if (defined $ids) {

$mbxArgs{'ids'} = $ids;

}

if (defined $types) {

$mbxArgs{'types'} = $types;

}

$d->add('mbox', $MAILNS, %mbxArgs);

} $d->end();
print "
OUTGOING XML:
-------------
";

my $out = $d->to_string("pretty");

$out =~ s/ns0://g;

print $out."
";
my $start = time;

my $firstStart = time;
my $response = $z->invokeAdmin($d->root());
print "
RESPONSE:
--------------
";

$out = $response->to_string("pretty");

$out =~ s/ns0://g;

print $out."
";

Run usage is ./reindex.pl -u USER -m MAILBOXID -a ACTION [-pw PASSWD] [-h HOST] [-t TYPES] [-ids IDS]
Here's another script zmreindexbyname.pl:

#!/usr/bin/perl

#

# ***** BEGIN LICENSE BLOCK *****

#

# Zimbra Collaboration Suite Server

# Copyright (C) 2005-2008 Zimbra, Inc.

#

# Software distributed under the License is distributed on an "AS IS"

# basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.

#

# ***** END LICENSE BLOCK *****

#
use strict;
use lib "/opt/zimbra/zimbramon/lib/zimbrapm";

use lib "/opt/zimbra/zimbramon/lib/zimbrapm/SOAP";

use lib "/opt/zimbra/zimbramon/lib";
use Getopt::Std;
use POSIX ":sys_wait_h";

use LWP::UserAgent;
use Zimbra::SOAP::XmlElement;

use Zimbra::SOAP::XmlDoc;

use Zimbra::SOAP::Soap;
use Time::Local;
# Exit if software-only node.

exit(0) unless (-f '/opt/zimbra/conf/localconfig.xml');
my $ACCTNS = "urn:zimbraAccount";

my $MAILNS = "urn:zimbraMail";

my $ADMINNS = "urn:zimbraAdmin";

my $SOAP = $Zimbra::SOAP::Soap::Soap12;
my $authToken;

my $START;

my $END;

my $PERIOD;
our %GlobalOpts;
sub usage { exit 1; }
sub get_auth_token {

my $host = shift;

my $user = shift;

my $pass = shift;
# print "Authenticating to $host as $user/$pass
";
my $url = https://$host:7071/service/admin/soap/";
">https://$host:7071/service/admin/soap/";
> my $d = new Zimbra::SOAP::XmlDoc;

$d->start('AuthRequest', $ADMINNS);

$d->add('name', undef, { by => "name"}, "$user");

$d->add('password', undef, undef, "$pass");

$d->end();
my $authResponse;

eval {

$authResponse = $SOAP->invoke($url, $d->root());

};
if (!$authResponse) {return undef;}

my $authToken = $authResponse->find_child('authToken')->content;
# print "authToken($authToken)
";
return $authToken;
}
#

#

#

#

#

# []

#

# -- Progress data is currently ONLY returned by the "status" and "cancelled" calls

#

# Access: domain admin sufficient

#
sub reIndexMailboxes {

my $name = shift;

my $action = shift;

my $host = "localhost";

my $user = "zimbra";

chomp $user;

my $pass = `zmlocalconfig -s -m nokey zimbra_ldap_password`;

chomp $pass;

$authToken = get_auth_token ($host, $user, $pass );
if (!defined ($authToken)) {exit 1;}
my $context = $SOAP->zimbraContext($authToken, "");
my $url = https://$host:7071/service/admin/soap/";
">https://$host:7071/service/admin/soap/";
> print "Indexing mailbox for $name...
";

my $d = new Zimbra::SOAP::XmlDoc;

$d->start('GetAccountRequest', $ADMINNS);

$d->add('account', undef, { by => "name"}, "$name");

$d->end();
my $response = $SOAP->invoke($url, $d->root(), $context);

if (!defined($response) || $response->{name} ne "GetAccountResponse") {

print "FAILED - can't get ID
";

return;

}

my $id = $response->find_child('account')->attr('id');
my $date = `date +%Y%m%d:%H%M%S`;

print "ID: $id...";

print "ACTION: $action
";

print "$date
";
$d = new Zimbra::SOAP::XmlDoc;

$d->start('ReIndexRequest', $ADMINNS, {action => $action});

$d->add('mbox', undef, { id => "$id"}, undef);

$d->end();
my $response = $SOAP->invoke($url, $d->root(), $context,360000);
if (defined($response) && $response->{name} eq "ReIndexResponse") {

print "".$response->to_string("pretty")."
";

} else {

print "Reindex for $name failed
";

return;

}
$d = new Zimbra::SOAP::XmlDoc;

$d->start('ReIndexRequest', $ADMINNS, {action => "status"});

$d->add('mbox', undef, { id => "$id"}, undef);

$d->end();
while (1) {

print "Status for $name...
";

my $response = $SOAP->invoke($url, $d->root(), $context,360000);

#if ($response->{name} eq "ReIndexResponse") {

if (defined ($response)) {

print "".$response->to_string("pretty")."
";

} else {

#print "".$response->to_string("pretty")."
";

print "$name complete
";

return;

}

sleep 120;

}
}
$|=1;
my @userList = <>;

foreach (@userList) {

chomp;

reIndexMailboxes($_, "start");

}

Should you be able to scrounge up some individual message blobs from backups some later on, see some of the info here:
Appointment">http://www.zimbra.com/forums/installati ... #post64962
Appointment blobs can be tricky...
Good luck!

[shinacalypse]

Thanks so much for your response, but due to the enormity of the problem, and the need for a quick and effective solution, i just renamed the zimbra folder, and reinstalled zimbra. I had already gone through the Account database structure document, and i was able to understand the relationship between the users' mailbox id and the mailbox store.

So i simply copied configuration data from the old zimbra store, and used a post i saw on zmlmtpinject to send the user's old email into the user's new zimbra email folder.

A bit drastic, sure. i had to automate by a small bash script that took the user's mailbox id and repopulated the mailbox. but it served the purpose at hand.

Im saved your response on my laptop in case i have this problem again.
Thank you kindly!

[bjared]

I have this same problem, but It appears that my user hasn't had any blob files for quite some time, and it has only become an issue because her mailbox stopped receiving new messages the other day. I saw log entries saying mail was delivered, but then it'd never appear.
Long story short, I exported her e-mail via outlook into a pst file, deleted all her mail from zimbra, reinstalled the connector, used the pst import wizard, and she has all her mail, and is receiving new mail.
Her calendar didn't get restored properly, and now I'm struggling with 5000+ calendar entries that have metadata (mysql) but no corresponding blob file. The perl script above deletes from the database those which isn't the solution I desire, and barring the result of one other idea today, I'm expecting to modify that script to actually parse the metadata, and create dummy .msg files that will make zimbra happy. "touch -.msg" just made zimbra complain about trying to cast a string to a mimeMultiPart type or something... So...if I can generate those blobs, wihch doesn't seem impossible (but a bit of a chore), we should be good to go.
The server in concern is 4.5.10 NE, and the user was using the 4.5.10 connector, and I upgraded her yesterday to the 4.5.11 connector, but she's currently in the state mentioned above, with her calendar 'shared' from restored_user into her real account, so she can at least see the entries in the web interface.
Anyway, We have a server running 5.0.9, but I'm not sure if there's a way to borrow the supposed repair utility that is with it, and run it on the 4.5.10 server. Nor, do I know

if upgrading this server to 5.0.9 (via 4.5.11) will delete all her metadata and leave me in a worse situation, or if it would actually create the .msg blob files and save me the scripting.
--Brian
(For using the "Quick Reply" option, I sure typed a lot...)

[bjared]

I ran into a problem during this recent case, where I finally got an calendar (ICS format)

to export, and imported it successfully. Then, for some reason, I rsync'd the blobs from

that restored account which had the in-tact calendar, and I suddenly realized I had

2x the mail items for the mailbox I was trying to restore.
Inspired by the mailboxfixdb.pl, I give to you, mailboxfixblobs.pl!
(NOTE: This is only for when you KNOW the blobs not indexed are not supposed to

be in the mailbox. If you are not sure, then you might simply want to just reindex the

mailbox, rather than using this script.)


#!/usr/bin/perl
# ========================================================================

# This script was insipred by the mailboxfixdb.pl script.

#

# This script deletes blobs that aren't in the mysql table "mail_item"

# for the account specified. (The script this is based on deleted records

# from the mysql database if no corresponding blob existed, which is

# the opposite of my script.)

#

# You'd only want to use this if you somehow restored/copied a bunch

# of .msg files into an account that you are certain aren't supposed

# to be there. (i.e. I imported calendar items, and then rsynced

# the blobs, so I now had twice the .msg files. Oops!)

#

# Usage (As the 'zimbra' user):

#

# ./mailboxfixblobs.pl user@their.domain.here

# ========================================================================
$DEBUG=1; #
if ($DEBUG) {

print "=============================================================
";

print "NOTICE: You are in DEBUG MODE! You must set $DEBUG=0 for
";

print " this script to actually delete .msg files form the
";

print " file system for the user's account.
";

print "
";

print " If you plan on disabling debug mode, you should be
";

print " certain you have sufficient backups of the user's
";

print " blob files beforehand.
";

print "=============================================================
";

} else {

print "=============================================================
";

print "NOTICE: This script is planning on deleting .msg files that
";

print " don't appear in the mail_item table for this user.
";

print " You have a few seconds to hit cntrl-C if this is not
";

print " what you want to do...
";

print "=============================================================
";

}
# Make the user think for a few seconds. It's a good thing.

sleep 5;
my ($fbits, $fgbits, $mbits, $mgbits, $basepath) = split (' ',`echo "select file_bits,file_group_bits,mailbox_bits,mailbox_group_bits,path from volume where type='1'" | mysql -N zimbra`);

my $ARGV = shift @ARGV;
chomp $basepath;
my %mail_item;

my $mbmask = sprintf "1" x $mgbits;

my $fmask = sprintf "1" x $fgbits;
# This bugged me with the other script, so instead of relying on the

# (outdated, and inaccurate in my case) comment field of the "mailbox"

# table, I use zmprov to get the mailbox ID:

$line = `zmprov getMailboxInfo $ARGV | grep mailboxId`;

chomp $line;

($id) = $line =~ m/mailboxId:s+(d+)$/;

print "id=($id)
";

if ($id eq "") {

print "That account doesn't appear to exist.
";

exit 1;

}
# Figure out the full path to this user's mailbox data on the file system.

$line = `echo "select group_id, account_id, comment from mailbox where id=${id}" | mysql -N zimbra`;

chomp $line;

my $path = "$basepath/";

my ($grid, $aid, $nm) = (split(/s+/, $line));

my $mbhash = $id >> $mbits;

$mbhash &= $mbmask;

$path .= $mbhash."/".$id."/msg/";
# Fill up a hash of items from the database, since reconnecting for each query is SLOW.

foreach my $mitem (`echo "select id,mod_content from mail_item where mailbox_id=${id}" | mysql -N mboxgroup${grid}`) {

chomp $mitem;

($tmp_id, $tmp_modContent) = split(/s+/, $mitem);

$mail_item{"$tmp_id-$tmp_modContent"} = 1;

}
# For every blob on the file system, see if we have an entry in the database.

# If we don't have an entry for it, nuke the file. (SET $DEBUG=0 to actually delete)

foreach my $blob (`find ${path} -type f -print`) {

chomp $blob;

($msgid, $modContent) = $blob =~ m/(d+)-(d+).msg$/;

if (! defined $mail_item{"$msgid-$modContent"}) {

if ($DEBUG) {

print "DEBUG: $blob should be deleted.
";

} else {

if (unlink $blob) {

print "INFO: Deleted blob: $blob
";

} else {

print "ERROR: Could not delete blob: $blob -- $!
";

}

}

} else {

print "OK: Message $msgid with mod_content $modContent is in the database.
";

}

}


The output (without setting $DEBUG=0) looks something like this:

OK: Message 8872 with mod_content 40382 is in the database.

OK: Message 8316 with mod_content 37049 is in the database.

OK: Message 8257 with mod_content 36801 is in the database.

OK: Message 8255 with mod_content 36797 is in the database.

DEBUG: /opt/zimbra/store/0/33/msg/2/8573-38396.msg should be deleted.

OK: Message 8793 with mod_content 39588 is in the database.

OK: Message 8216 with mod_content 36620 is in the database.

OK: Message 8301 with mod_content 36969 is in the database.

OK: Message 8646 with mod_content 38932 is in the database.

OK: Message 8850 with mod_content 40262 is in the database.

OK: Message 8354 with mod_content 37242 is in the database.

DEBUG: /opt/zimbra/store/0/33/msg/2/8552-38286.msg should be deleted.

OK: Message 8259 with mod_content 36807 is in the database.

OK: Message 8869 with mod_content 40374 is in the database.
I hope this script is useful to some people still running ZCS 4.5.x. It saved my butt

last night when I went from 5,000+ mail items to 10,000+ because I wasn't paying

attention...
--Brian

[bjared]

I have some blobs that are obviously supposed to be in the mailbox (just a handfull) and I have no idea how to get Zimbra to create the corresponding mysql entries so it'll show them. I assumed a reindex on the mailbox would fix that, but it doesn't. (Which makes me wonder what the reindexing actually does...)
I know this thread is marked as [SOLVED] but I don't feel this has been solved adequately. In my case, deleting all the mysql entries pointing to missing blobs was

not the solution. (Going from 5000+ mail items to 14 somehow doesn't sit well with

the end-user...)
Now I have legitimate e-mail blobs on the file system, and zimbra doesn't know about

them.
I also have a case that has been opened that I'm still waiting to have a single

response, update or a simple acknowledgment that my case was reviewed.

I guess the forums are the appropriate support channel...I guess.
Maybe I'll read that the lmtpinject thing will solve my problem, but we'll see.
--Brian