imapsync, exchange migration, zimbra doesn't show mail, but is in database

[dverweire]

I have successfully migrated several Exchange 2003 mailboxes to Zimbra 5.0.11 on Ubuntu 8.04.

One mailbox did have a problem though. This user has many sub-folders in their Inbox, one of which is called Personal. When I look at this folder in Zimbra, there is one email listed in the right pane. When I mouse over the folder name, it says there are 2679 emails totalling 341 MB.

I ran /opt/zimbra/bin/mysql and did the following:

use mboxgroup7;
select id from mail_item where name like 'personal';
select * from mail_item where folder_id = 67714; --from the previous query

The last statement returns 2679 rows. I have verified that the related email files physically exist on the hard drive too.

I re-indexed the mailbox which did not help.

Does anyone know what needs to be done to get these to show up? I'm afraid that somehow these messages have been flagged for deletion or something and if an IMAP expunge happens they will actually be deleted which I cannot afford to have happen.

I look forward to any suggestions possible.

Thank you,

Dan

[dverweire]

I have determined that the messages in the database are flagged for deletion. Luckily this server is running in a virtual machine and I was able to make a copy of the entire file system. After I did that, I used Kmail to compact that folder. Now the database only has one email listed for that folder.

I have restored to before the compact. How can I unflag all these deleted emails?

Thanks,

Dan

[dverweire]

After spending some time figuring out what the different flag field values represent, it seems that bit 8 determines whether or not the mail is deleted. So I determined which mails in the database have this bit set with the following query:

select * from mail_item where (flags & 128) ;

It returned all of the mail that was not showing up in that folder. Then a simple:

update mail_item set flags = flags - 128 where (flags & 128)

and poof all the mail is back.

From what I've read on the forums here, it's not recommended to modify the database directly. So if you find yourself in this situation, be sure you know what you're doing. I don't know Zimbra very well, but I have gotten the results I need with this method. I've made my choice and now I'm going to stick with it.

Oh and just for reference, other flags. And please take this with a grain of salt, because I'm not a Zimbra developer and I'm sure there is a header file somewhere that has all this stuff laid out for fact. Or, at least I hope so.

1 - 00000001 - Sent
2 - 00000010 -Read
4 - 00000100 - Replied
8 - 00001000 - Forwarded
16 - 00010000 - dunno
32 - 00100000 - Flagged
64 - 01000000 - Draft
128 - 10000000 - Deleted

I stopped there.

[reinard]

I found this through google and just wanted to add a useful link for reference for future visitors that I ended up finding when looking for the same thing:

a more complete list of the meaning of the flags bits can be found here:
Message Flags - Zimbra :: Wiki

Also wanted to say I like the bit selection method using " (flags & 128) " in the where clause. i was using bit-shifting and mod to compare, which has it advantages in that you can read which bit is being tested easily, but it's probably not as efficient: for example " (flags >> 3) % 2 = 1 " to test if a message had been forwarded (4th bit from the right, or 3 since you have to count them 0-based).

Thanks for posting your solution. It's always great when people take the time to come back and post how they fixed their problems.