Integrations do not work - Failed handle request via 'AccountDataServlet'

[danry25]

Description

When trying to use Integrations (whether provided dimension.t2bot.io or scalar.vector.im) "Cannot connect to integration manager" is shown to the user. This affects existing users as well as newly registered users on whomst.online.

The integration troubleshooter provided by matrix-dimension passes, as does the federation tester.

Steps to reproduce

• Open Riot-web
• Press the four squares in the upper right corner
• Check the Accept button for the Terms of Service and click Next
• User is presented with "Cannot connect to integration manager" and the error below is generated on the matrix-synapse server.

May 03 17:40:50 matrix authbind[24029]: 2020-05-03 17:40:50,204 - synapse.http.server - 110 - ERROR - PUT-2380028 - Failed handle request via 'AccountDataServlet': <SynapseRequest at 0x7f3f35074e48 method='PUT' uri='/_matrix/client/r0/user/%40dan%3Awhomst.online/account_data/m.accepted_terms' clientproto='HTTP/1.1' site=443>
May 03 17:40:50 matrix authbind[24029]: Traceback (most recent call last):
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/http/server.py", line 78, in wrapped_request_handler
May 03 17:40:50 matrix authbind[24029]:     await h(self, request)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/http/server.py", line 331, in _async_render
May 03 17:40:50 matrix authbind[24029]:     callback_return = await callback_return
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/rest/client/v2_alpha/account_data.py", line 50, in on_PUT
May 03 17:40:50 matrix authbind[24029]:     user_id, account_data_type, body
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
May 03 17:40:50 matrix authbind[24029]:     result = result.throwExceptionIntoGenerator(g)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
May 03 17:40:50 matrix authbind[24029]:     return g.throw(self.type, self.value, self.tb)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/data_stores/main/account_data.py", line 357, in add_account_data_for_user
May 03 17:40:50 matrix authbind[24029]:     lock=False,
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
May 03 17:40:50 matrix authbind[24029]:     result = result.throwExceptionIntoGenerator(g)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
May 03 17:40:50 matrix authbind[24029]:     return g.throw(self.type, self.value, self.tb)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/database.py", line 730, in simple_upsert
May 03 17:40:50 matrix authbind[24029]:     lock=lock,
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
May 03 17:40:50 matrix authbind[24029]:     result = result.throwExceptionIntoGenerator(g)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
May 03 17:40:50 matrix authbind[24029]:     return g.throw(self.type, self.value, self.tb)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/database.py", line 524, in runInteraction
May 03 17:40:50 matrix authbind[24029]:     **kwargs
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
May 03 17:40:50 matrix authbind[24029]:     result = result.throwExceptionIntoGenerator(g)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
May 03 17:40:50 matrix authbind[24029]:     return g.throw(self.type, self.value, self.tb)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/database.py", line 574, in runWithConnection
May 03 17:40:50 matrix authbind[24029]:     self._db_pool.runWithConnection(inner_func, *args, **kwargs)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/python/threadpool.py", line 250, in inContext
May 03 17:40:50 matrix authbind[24029]:     result = inContext.theWork()
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/python/threadpool.py", line 266, in <lambda>
May 03 17:40:50 matrix authbind[24029]:     inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/python/context.py", line 122, in callWithContext
May 03 17:40:50 matrix authbind[24029]:     return self.currentContext().callWithContext(ctx, func, *args, **kw)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/python/context.py", line 85, in callWithContext
May 03 17:40:50 matrix authbind[24029]:     return func(*args,**kw)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
May 03 17:40:50 matrix authbind[24029]:     compat.reraise(excValue, excTraceback)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/python/compat.py", line 464, in reraise
May 03 17:40:50 matrix authbind[24029]:     raise exception.with_traceback(traceback)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/lib/python3/dist-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
May 03 17:40:50 matrix authbind[24029]:     result = func(conn, *args, **kw)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/database.py", line 571, in inner_func
May 03 17:40:50 matrix authbind[24029]:     return func(conn, *args, **kwargs)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/database.py", line 407, in new_transaction
May 03 17:40:50 matrix authbind[24029]:     r = func(cursor, *args, **kwargs)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/database.py", line 767, in simple_upsert_txn
May 03 17:40:50 matrix authbind[24029]:     txn, table, keyvalues, values, insertion_values=insertion_values
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/database.py", line 880, in simple_upsert_txn_native_upsert
May 03 17:40:50 matrix authbind[24029]:     txn.execute(sql, list(allvalues.values()))
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/database.py", line 199, in execute
May 03 17:40:50 matrix authbind[24029]:     self._do_execute(self.txn.execute, sql, *args)
May 03 17:40:50 matrix authbind[24029]:   File "/usr/local/lib/python3.7/dist-packages/synapse/storage/database.py", line 225, in _do_execute
May 03 17:40:50 matrix authbind[24029]:     return func(sql, *args)
May 03 17:40:50 matrix authbind[24029]: psycopg2.ProgrammingError: there is no unique or exclusion constraint matching the ON CONFLICT specification
May 03 17:40:50 matrix authbind[24029]: 2020-05-03 17:40:50,206 - synapse.access.https.443 - 302 - INFO - PUT-2380028 - ::ffff:10.100.1.226 - 443 - {@dan:whomst.online} Processed request: 0.003sec/0.000sec (0.001sec, 0.000sec) (0.000sec/0.001sec/1) 67B 500 "PUT /_matrix/client/r0/user/%40dan%3Awhomst.online/account_data/m.accepted_terms HTTP/1.1" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Riot/1.5.15 Chrome/78.0.3904.130 Electron/7.1.14 Safari/537.36" [0 dbevts]
May 03 17:40:50 matrix authbind[24029]: 2020-05-03 17:40:50,221 - twisted - 192 - INFO -  - Timing out client: IPv6Address(type='TCP', host='::ffff:10.100.1.226', port=44732, flowInfo=0, scopeID=0)

Version information

• Homeserver: whomst.online
• Version: 1.12.3
• Install method: pip3
• Platform: Debian 10 Buster (inside KVM, atop a Ryzen CPU), matrix-synapse is directly exposed to the web using authbind, and Postgres is the backend database.

[babolivier]

Hi @danry25, and thanks for your report! Do you still see the issue? There were a few issues with the matrix.org IM over the weekend (though not sure why it would mean that dimension is also failing), so I just want to make sure it's not related.

[danry25]

I would be surprised if this was related @babolivier, seeing as we weren't interacting with matrix.org for our integrations, and testing with both dimension.t2bot.io and scalar.vector.im generated the same database error.

Seems like our database needs fixing, just not sure how to tackle that...

[danry25]

We've had this database for ~2 years IIRC and it has had other serious issues that caused persistent high CPU & ram use even when no users were connected. I corrected this and migrated the DB from sqlite to postgres when I took over management of whomst.online, but this appears to be a gremlin I overlooked.

Closing this issue since it seems to be an issue specific to our migration path over the years. We restarted from a fresh database and integrations work.

[babolivier]

Right, if you want to fix your database, then alter table account_data add constraint account_data_uniqueness unique (user_id, account_data_type); should do it.