LDAP and MySQL mismatch

[scottp]

I am unable to create users - always reporting a SOAP error, but I can edit, only not create...

"Server error encountered Message: system failure: createAccount Error code: service.FAILURE Method: ZmCsfeCommand.prototype.invoke Details:soap:Receiver"

My LDAP database crashed completely, and had a backup, but it was not exactly the same time as the other database.

After lots of debugging, stack back traces, logs with debugging - we have worked out where the problem is, but not the solution.

The problem is that mysql (zimbra and others) show users that do not exist in LDAP.

So a quick "select id, comment from mailbox" on zimbra shows two users, 199 and 200 that do not exist in LDAP.

But, if I do a "delete from mailbox where id = 199"
mysql> delete from mailbox where id = 200;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mboxgroup100/mail_item`, CONSTRAINT `fk_mail_item_mailbox_id` FOREIGN KEY (`mailbox_id`) REFERENCES `zimbra`.`mailbox` (`id`))

So something is screwy...

My question is, how can I tell Zimbra to use an ID above all of these - as I think the problem is related to no free entry.

Here is a log entry that lead to this issue: (shortened to important line)

} catch (NameAlreadyBoundException e) {

Scott

[Rich Graves]

Assuming this is FOSS edition? The proper backups/redologs in NE would have prevented such a mismatch.

It's telling you what to do -- first, delete from mboxgroup100.mail_item where id=200, and to be really clean, trace those rows in mboxgroup100.mail_item to blobs in /opt/zimbra/store.

(Does mysql support the dangerous oracleism "on delete cascade"?)

Rather than chasing deletia, you could also try creating the LDAP entries manually. zimbra.mailbox tells you the accountid, with a comment telling the username.

Going forward, consider setting up an LDAP secondary. http://www.zimbra.com/docs/os/latest...ation.6.1.html

[scottp]

Yep the FOSS edition.

I am going to try the LDAP manual create first. Unfortunately the backup / restore was the problem. My fault really, but when I did the rsync, I did not delete old entries. Combine that with LDAP crash (well known problem on RC2) I am in this bind.

There seems to me three solutions to the problem:

1) LDAP manual create with old IDs
2) Remove old mysql entries with IDs and store/X, index/X etc
3) Tell something that the next free ID is bigger

This number 3 would be the best, but I can't figure out where Zimbra stores the next free ID. Anyone know?

Scott

[scottp]

It's telling you what to do -- first, delete from mboxgroup100.mail_item where id=200, and to be really clean, trace those rows in mboxgroup100.mail_item to blobs in /opt/zimbra/store.

Unfortunately it gets very hard to follow...

mysql> delete from mail_item where mailbox_id = 200;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mboxgroup100/mail_item`, CONSTRAINT `fk_mail_item_folder_id` FOREIGN KEY (`mailbox_id`, `folder_id`) REFERENCES `mail_item` (`mailbox_id`, `id`))

Which is a reference back to itself...

I think we are barking up the wrong tree.