Getting duplicate openid errors when using django-openid-auth with SSO

[John O'Brien]

In Ubuntu One we are getting the OOPS errors below. We are using django-openid-auth with login.ubuntu.com.

Traceback (most recent call last):
  Module /srv/ubuntuone.com/production/ubunet-rev-4787/utilities/../lib/django/db/models/query.py, line 343, in get
    % (self.model._meta.object_name, num, kwargs))
  Module /srv/ubuntuone.com/production/ubunet-rev-4787/utilities/../lib/django/db/models/manager.py, line 132, in get
    return self.get_query_set().get(*args, **kwargs)
  Module /usr/lib/python2.6/dist-packages/django_openid_auth/auth.py, line 242, in associate_openid
    claimed_id__exact=openid_response.identity_url)
  Module /usr/lib/python2.6/dist-packages/django_openid_auth/auth.py, line 232, in create_user_from_openid
    self.associate_openid(user, openid_response)
  Module /usr/lib/python2.6/dist-packages/django_openid_auth/auth.py, line 77, in authenticate
    user = self.create_user_from_openid(openid_response)
  Module /srv/ubuntuone.com/production/ubunet-rev-4787/utilities/../lib/django/contrib/auth/__init__.py, line 50, in authenticate
    user = backend.authenticate(**credentials)
  Module /usr/lib/python2.6/dist-packages/django_openid_auth/views.py, line 267, in login_complete
    user = authenticate(openid_response=openid_response)
  Module /srv/ubuntuone.com/production/ubunet-rev-4787/utilities/../lib/django/views/decorators/csrf.py, line 36, in wrapped_view
    return view_func(*args, **kwargs)
  Module /srv/ubuntuone.com/production/ubunet-rev-4787/utilities/../lib/django/views/decorators/csrf.py, line 23, in wrapped_view
    resp = view_func(*args, **kwargs)
  Module /srv/ubuntuone.com/production/ubunet-rev-4787/utilities/../lib/django/core/handlers/base.py, line 100, in get_response
    response = callback(request, *callback_args, **callback_kwargs)
  Module /srv/ubuntuone.com/production/ubunet-rev-4787/utilities/../lib/django/core/handlers/wsgi.py, line 241, in __call__
    response = self.get_response(request)
  Module /usr/lib/pymodules/python2.6/paste/translogger.py, line 68, in __call__
    return self.application(environ, replacement_start_response)
MultipleObjectsReturned: ' get() returned more than one UserOpenID -- it returned 2! Lookup parameters were {'claimed_id__exact': 'https://login.ubuntu.com/+id/H8RBhTt'}'

[James Henstridge (jamesh)]

This sounds like a problem with the database schema for U1's installation of django-openid-auth. The database schema should have a unique constraint on UserOpenID.claimed_id, so this particular error should never have occurred.

It does leave open the question about how duplicate claimed_id values got into the database in the first place: the code paths that create UserOpenID records only do so after checking that there are no other records with the given claimed_id value, so I would only expect to see this in a race condition in a new user logging in twice at the same time. The constraint is designed to cause that code path to fail rather than corrupt the database.

[James Henstridge (jamesh)]

I got Tom to run the following query on the U1 account database:

SELECT claimed_id, count(*), max(user_id) - min(user_id) AS user_id_spread
  FROM django_openid_auth_useropenid
  GROUP BY claimed_id having count(*) > 1;

The results are here: https://pastebin.canonical.com/56463/

The user_id_spread was usually only 1 (i.e. claimed_id held by two sequentially created accounts), and at most was 3. So it most likely represents race conditions on account creation (maybe people hitting reload in the browser). With the constraint in place, the second account creation should fail, but the user should still be able to log in to the first account created.

[James Henstridge (jamesh)]

One open question is how to go about resolving this problem. From the exception, it seems obvious that affected users can't log in via the web. We should check to see if there is evidence of these people logging in via the desktop client (OAuth).

If users can log in via OAuth, then we should remove the other account entry (or even just the django_openid_auth_useropenid record). If users can't log in via either method, removing one of the django_openid_auth_useropenid records at random should suffice.

Once the problem data is cleaned up, we really need to add the missing constraint.