EmailAddress.email is not UNIQUE

[Stuart Bishop]

lower(EmailAddress.email) should be UNIQUE but for some reason is not.

This makes some Launchpad code explode. I suspect the login service is not checking for duplicates as well as Launchpad did.

We need to clean up the data and rebuild the index as UNIQUE on the live system.

[Stuart Bishop (stub)]

There are 163 users affected (out of nearly 4 million). None of them have more than one Launchpad account.

[Stuart Bishop (stub)]

Bug #523346 describes the same issue. Perhaps the cleanup didn't clean everything up?

This time a constraint for sure.

[Stuart Bishop (stub)]

Looks like the SSO is allowing accounts to be created with dupe email addresses. Possibly forgetting to do a case sensitive check.

The column is now UNIQUE (lower(email)) on the master, so bad creations will now generate errors.

[Ursula Junque (ursinha)]

Fixed in db-stable r9160 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/9160>

[Ursula Junque (ursinha)]

Fixed in db-stable r9170 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/9170>

[Anthony Lenton (elachuni)]

Is this constraint still in place? We could probably leverage the index it created instead of using the new index on upper(email).

[Anthony Lenton (elachuni)]

Stuart B., what happened to the 163 emails that were in the DB that already had duplicates? Have they been removed or are they still there?

[Stuart Bishop (stub)]

Duplicate EmailAddress records where removed and the Accounts relinked to the correct Person record. I don't recall having to remove any Account records.