Page 1 of 1

Account Ghosts

Posted: Fri Feb 22, 2008 10:34 pm
by peter@mxtoolbox.com
I have run into a problem and I don't know how to fix it. I recently activated HSM on my 4.5.6 NE box and the process is crashing shortly after:
2008-02-22 22:06:22,607 WARN [Thread-617] [mid=74;] hsm - Skipping mailbox 74: com.zimbra.cs.account.AccountServiceException: no such account: e50ca357-cb30-4340-957d-678b7c215816
The problem is that box #74 was created a LONG time ago for a user. It was deleted or modified in some way and I think restored back from a backup to a different name which was renamed back to his old account name. I cannot recall the details, but here is the symptom:
[zimbra@SERVER libexec]$ zmprov -l ga e50ca357-cb30-4340-957d-678b7c215816

ERROR: account.NO_SUCH_ACCOUNT (no such account: e50ca357-cb30-4340-957d-678b7c215816)
Basically, some piece of the system has a record for good old box #74 and the messages are still there, but the LDAP has no record of him. How do I get this user dead dead dead?

Account Ghosts

Posted: Fri Feb 22, 2008 11:27 pm
by kirme3
What happens when you do the following as zimbra user:

mysql

connect zimbra

select * from mailbox where account_id="e50ca357-cb30-4340-957d-678b7c215816";
My guess is you will get a line with the comment as the users email address and an id of 74. You will probably need to manually clean up the account info out of the databases, store, and indexing.

Account Ghosts

Posted: Sat Feb 23, 2008 11:44 am
by peter@mxtoolbox.com
Is there any documentation on the mysql schema. I believe that I do need to delete this stale user from the DB, but I want to make sure I get all the references to it.
Also, as far as Zimbra is concerned, is there any other record of the user in the system outside of mysql and ldap? Is there any other place I might need to go to erase this user?

Account Ghosts

Posted: Sat Feb 23, 2008 8:53 pm
by peter@mxtoolbox.com
Okay, so now I've decided to try to clean this up. However I can't figure out the correct SQL. I've tried this:
DELETE from mailbox WHERE id = 74;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mboxgroup74/mail_item`, CONSTRAINT `fk_mail_item_mailbox_id` FOREIGN KEY (`mailbox_id`) REFERENCES `zimbra`.`mailbox` (`id`))
frustration
AND
DELETE FROM mail_item WHERE mailbox_id = 74;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mboxgroup74/mail_item`, CONSTRAINT `fk_mail_item_folder_id` FOREIGN KEY (`mailbox_id`, `folder_id`) REFERENCES `mail_item` (`mailbox_id`, `id`))

mysql> desc mail_item;

Account Ghosts

Posted: Mon Feb 25, 2008 10:15 am
by peter@mxtoolbox.com
I guess I just thought that perhaps I needed a more complex SQL query to catch the FKs as well. I thought the constraints might have been put on there for a reason besides just stopping users from running queries on the table.

Account Ghosts

Posted: Fri Feb 29, 2008 7:20 am
by peter@mxtoolbox.com
Okay, I removed refereneces to this box in mysql in zimbra.mailbox, zimbra.mailbox_metadata, and all tables in mboxgroup74. I then went and deleted all the messages in the ~/store/74 directory. I still hit box 74 when I run HSM.
Do I need to remove the index files, and if so how do I do that? How does Zimbra generate the list of mailboxes to process for HSM?