SSO fails with sqlite

[dbkr]

Trying to test SSO with a local synapse running sqlite, I got an internal server error when first logging in (ie. when synapse had to create the user). Attempting to log in again worked, so it looks like it's creating the user successfully but then failing to do something else that causes the whole request to fail.

Given the log below this look like it may be sqlite specific?

2019-01-24 13:51:30,022 - synapse.http.client - 286 - INFO - GET-173- Received response to GET http://dbkr.me:8080/proxyValidate?ticket=ST-e766bb44-298b-431b-8a95-4f3d4bbe36ef&service=http%3A%2F%2Flocalhost%3A8008: 200
2019-01-24 13:51:30,023 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext GET-173@107371de8 without calling start
2019-01-24 13:51:30,025 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext GET-173@107371de8 without calling start
2019-01-24 13:51:30,040 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext runWithConnection@107371ed8 without calling start
2019-01-24 13:51:30,040 - synapse.handlers.auth - 591 - WARNING - GET-173- Attempted to login as @john:localhost but they do not exist
2019-01-24 13:51:30,041 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext GET-173@107371de8 without calling start
2019-01-24 13:51:30,041 - synapse.metrics - 374 - INFO - - Collecting gc 0
2019-01-24 13:51:30,043 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext runWithConnection@1072bb140 without calling start
2019-01-24 13:51:30,044 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext GET-173@107371de8 without calling start
2019-01-24 13:51:30,049 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext runWithConnection@107224d70 without calling start
2019-01-24 13:51:30,050 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext GET-173@107371de8 without calling start
2019-01-24 13:51:30,050 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext runWithConnection@1073632a8 without calling start
2019-01-24 13:51:30,051 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext GET-173@107371de8 without calling start
2019-01-24 13:51:30,051 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext GET-173@107371de8 without calling start
2019-01-24 13:51:30,051 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext runWithConnection@1073718c0 without calling start
2019-01-24 13:51:30,052 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext GET-173@107371de8 without calling start
2019-01-24 13:51:30,052 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext GET-173@107371de8 without calling start
2019-01-24 13:51:30,055 - synapse.storage._base - 290 - WARNING - GET-173- [TXN OPERROR] {update_profile_in_user_dir-399} ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint 0/5
2019-01-24 13:51:30,056 - synapse.storage._base - 290 - WARNING - GET-173- [TXN OPERROR] {update_profile_in_user_dir-399} ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint 1/5
2019-01-24 13:51:30,058 - synapse.storage._base - 290 - WARNING - GET-173- [TXN OPERROR] {update_profile_in_user_dir-399} ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint 2/5
2019-01-24 13:51:30,060 - synapse.storage._base - 290 - WARNING - GET-173- [TXN OPERROR] {update_profile_in_user_dir-399} ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint 3/5
2019-01-24 13:51:30,061 - synapse.storage._base - 290 - WARNING - GET-173- [TXN OPERROR] {update_profile_in_user_dir-399} ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint 4/5
2019-01-24 13:51:30,063 - synapse.storage._base - 290 - WARNING - GET-173- [TXN OPERROR] {update_profile_in_user_dir-399} ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint 5/5
2019-01-24 13:51:30,063 - synapse.util.logcontext - 321 - WARNING - GET-173- Called stop on logcontext runWithConnection@107371938 without calling start
2019-01-24 13:51:30,072 - synapse.http.server - 112 - ERROR - GET-173- Failed handle request via <function _async_render at 0x105c397d0>: <SynapseRequest at 0x1073665a8 method=u'GET' uri=u'/_matrix/client/api/v1/login/cas/ticket?redirectUrl=http%3A%2F%2Flocalhost%3A8080%2F%3Fhomeserver%3Dhttp%253A%252F%252Flocalhost%253A8008%26identityServer%3Dhttps%253A%252F%252Fvector.im&ticket=ST-e766bb44-298b-431b-8a95-4f3d4bbe36ef' clientproto=u'HTTP/1.1' site=8008>: Traceback (most recent call last):
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 654, in _runCallbacks
    current.result = callback(current.result, *args, **kw)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 1475, in gotResult
    _inlineCallbacks(r, g, status)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
--- <exception caught here> ---
  File "synapse/http/server.py", line 81, in wrapped_request_handler
    yield h(self, request)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/http/server.py", line 316, in _async_render
    callback_return = yield callback(request, **kwargs)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/rest/client/v1/login.py", line 386, in on_GET
    result = yield self.handle_cas_response(request, body, client_redirect_url)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/rest/client/v1/login.py", line 489, in on_successful_auth
    default_display_name=user_display_name,
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/handlers/register.py", line 202, in register
    user_id, profile
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/handlers/user_directory.py", line 133, in handle_local_profile_change
    user_id, profile.display_name, profile.avatar_url, None,
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/storage/_base.py", line 361, in runInteraction
    *args, **kwargs
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/storage/_base.py", line 410, in runWithConnection
    inner_func, *args, **kwargs
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/threadpool.py", line 250, in inContext
    result = inContext.theWork()
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/threadpool.py", line 266, in <lambda>
    inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/context.py", line 122, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/python/context.py", line 85, in callWithContext
    return func(*args,**kw)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/Users/dave/matrix/synapse/venv/lib/python2.7/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
    result = func(conn, *args, **kw)
  File "synapse/storage/_base.py", line 406, in inner_func
    return func(conn, *args, **kwargs)
  File "synapse/storage/_base.py", line 282, in _new_transaction
    r = func(txn, *args, **kwargs)
  File "synapse/storage/user_directory.py", line 234, in _update_profile_in_user_dir_txn
    lock=False,  # We're only inserter
  File "synapse/storage/_base.py", line 625, in _simple_upsert_txn
    insertion_values=insertion_values,
  File "synapse/storage/_base.py", line 710, in _simple_upsert_txn_native_upsert
    txn.execute(sql, list(allvalues.values()))
  File "synapse/storage/_base.py", line 88, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "synapse/storage/_base.py", line 118, in _do_execute
    sql, *args
sqlite3.OperationalError: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

[erikjohnston]

Fixed by #4471