Remove-Orphaned-Account-from-MySQL

Remove Orphaned Accounts from MySQL

---

 - This article is a Work in Progress, and may be unfinished or missing sections.

---

Problem

Due to reasons beyond our control, you may find a ghost account, or commonly referred to as a orphaned account. Example:

zmblobchk start
[] INFO: Setting mysql connector property: maxActive=100
[] INFO: Setting mysql connector property: maxActive=100
....
Checking mailbox 143.
no such account: 134a36d6-acf2-4287-80d3-f8d83e2be674

Solution

Remove entries of orphaned account from MySQL database manually.

Verify the orphaned account, and get the details of respective account.

# su - zimbra
$ mysql
$ use zimbra;

MariaDB [zimbra]> select * from mailbox where account_id="134a36d6-acf2-4287-80d3-f8d83e2be674"\G
*************************** 1. row ***************************
id: 143
group_id: 43
account_id: 134a36d6-acf2-4287-80d3-f8d83e2be674
index_volume_id: 2
item_id_checkpoint: 256
contact_count: 0
size_checkpoint: 0
change_checkpoint: 1
tracking_sync: 0
tracking_imap: 0
last_backup_at: NULL
comment: johm.smith@example.com
last_soap_access: 0
new_messages: 0
idx_deferred_count: 0
highest_indexed: NULL
version: 2.7
last_purge_at: 0
itemcache_checkpoint: 0
1 row in set (0.00 sec)

$zmprov gmi johm.smith@example.com
ERROR: account.NO_SUCH_ACCOUNT (no such account: johm.smith@example.com) 

Note: Replace 43 with mailbox group id "group_id", and 143 with "id" as per the previous command output.

mysql mboxgroup43 -e "delete from appointment where mailbox_id='143';"
mysql mboxgroup43 -e "delete from appointment_dumpster where mailbox_id='143';"
mysql mboxgroup43 -e "delete from data_source_item where mailbox_id='143';"
mysql mboxgroup43 -e "delete from imap_folder where mailbox_id='143';"
mysql mboxgroup43 -e "delete from imap_message where mailbox_id='143';"
mysql mboxgroup43 -e "SET FOREIGN_KEY_CHECKS=0; delete from mail_item where mailbox_id='143';"
mysql mboxgroup43 -e "delete from mail_item_dumpster where mailbox_id='143';"
mysql mboxgroup43 -e "delete from open_conversation where mailbox_id='143';"
mysql mboxgroup43 -e "delete from pop3_message where mailbox_id='143';"
mysql mboxgroup43 -e "delete from purged_conversations where mailbox_id='143';"
mysql mboxgroup43 -e "delete from purged_messages where mailbox_id='143';"
mysql mboxgroup43 -e "delete from revision where mailbox_id='143';"
mysql mboxgroup43 -e "delete from revision_dumpster where mailbox_id='143';"
mysql mboxgroup43 -e "delete from tag where mailbox_id='143';"
mysql mboxgroup43 -e "delete from tagged_item where mailbox_id='143';"
mysql mboxgroup43 -e "delete from tombstone where mailbox_id='143';"
mysql mboxgroup43 -e "delete from event where mailbox_id='143';"
mysql mboxgroup43 -e "delete from watch where mailbox_id='143';"

mysql zimbra -e "delete from mailbox where id='143';"
mysql zimbra -e "delete from mailbox_metadata where mailbox_id='143';"
mysql zimbra -e "delete from mobile_devices where mailbox_id='143';"
mysql zimbra -e "delete from out_of_office where mailbox_id='143';"
mysql zimbra -e "delete from pending_acl_push where mailbox_id='143';"
mysql zimbra -e "delete from scheduled_task where mailbox_id='143';"
mysql zimbra -e "delete from zmg_devices where mailbox_id='143';"

Verify storage, index directories of respective account, and remove manually if exist. Note: If HSM using verify on HSM partition too. Be careful while using rm command as it remove the directory, and it can not be restored.

if [ ! -d /opt/zimbra/store/0/143 ]; then echo "Directory doesn't exist"; else rm -rf /opt/zimbra/store/0/143; fi
if [ ! -d /opt/zimbra/index/0/143 ]; then echo "Directory doesn't exist"; else rm -rf /opt/zimbra/index/0/143; fi

Submitted by: Raghu Noti