`duplicate key value violates unique constraint "rooms_pkey"` error when rejoining a room

[turt2live]

homeserver - 2018-06-08 10:03:40,948 - synapse.storage.room - 214 - ERROR - store_room with room_id=!cURbafjkfsMDVwdRDQ:matrix.org failed: duplicate key value violates unique constraint "rooms_pkey"
DETAIL:  Key (room_id)=(!cURbafjkfsMDVwdRDQ:matrix.org) already exists.

This happened while during an instance of #2738 (specifically #2738 (comment) )

It doesn't seem to be harmful, but errors in logs can be scary.

Version information

• Homeserver: t2bot.io
• Version: 0.31.0 (at the time)
• Install method: pip

[elinorbgr]

I have the same error in my log (synapse 0.99.4) after I left #freenode_#wayland:matrix.org and now try to rejoin it.

Synapse fails to join the room (riot pops an "internal server error" message), and this line appears in my logs.

As a result I can no longer rejoin the room.

[elinorbgr]

The same thing occurred again with #irc:matrix.org, and I can have a full, more precise error:

2019-07-08 19:18:01,673 - synapse.storage.room - 245 - ERROR - POST-134 - store_room with room_id=!BAXLHOFjvDKUeLafmO:matrix.org failed: duplicate key value violates unique constraint "rooms_pkey"               
DETAIL:  Key (room_id)=(!BAXLHOFjvDKUeLafmO:matrix.org) already exists.

2019-07-08 19:18:03,562 - synapse.http.server - 108 - ERROR - POST-134 - Failed handle request via 'JoinRoomAliasServlet': <XForwardedForRequest at 0x7fe1b0d89748 method='POST' uri='/_matrix/client/r0/join/%23irc%3Amatrix.org' clientproto='HTTP/1.0' site=8008>
Traceback (most recent call last):
  File "/opt/matrix/synapse/lib/python3.6/site-packages/synapse/http/server.py", line 76, in wrapped_request_handler
    await h(self, request)
  File "/opt/matrix/synapse/lib/python3.6/site-packages/synapse/http/server.py", line 301, in _async_render
    callback_return = await callback_return
twisted.internet.defer.FirstError: FirstError[#0, [Failure instance: Traceback: <class 'psycopg2.ProgrammingError'>: more than one row returned by a subquery used as an expression

Again, this prevents me from rejoining the room (even though matrix.org homeserver seems to believe I have rejoined). Last time I "solved" it using the nuke_room_from_db.py script, which is hardly a good solution.

Should I keep this issue around to provide more precise diagnostics, and if so what kind of diagnostics would help?

[silkeh]

We see this error on the IRCnet bridge for the bridge user (@ircnet:irc.snt.utwente.nl). Because of the error the IRCnet user fails to complete bridging via the provisioning API.

[grinapo]

synapse v1.7.0

Results system error inviting user (and probably joining theinvite sent from the same server for a room). May be malevolent invite, though.

2019-12-15 16:20:25,390 - synapse.storage.data_stores.main.room - 492 - ERROR - POST-118966- store_room with room_id=!uvlCXdYDUAhpPoX4VP:zemos.net failed: duplicate key value violates unique constraint "rooms_pkey"
DETAIL:  Key (room_id)=(!uvlCXdYDUAhpPoX4VP:zemos.net) already exists.

2019-12-15 16:20:25,391 - synapse.http.server - 109 - ERROR - POST-118966- Failed handle request via 'JoinRoomAliasServlet': <XForwardedForRequest at 0x7efcfa86de10 method='POST' uri='/_matrix/client/r0/join/!uvlCXdYDUAhpPoX4VP%3Azemos.net' clientproto='HTTP/1.1' si
te=8008>
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/synapse/storage/data_stores/main/room.py", line 490, in store_room
    yield self.db.runInteraction("store_room_txn", store_room_txn, next_id)
  File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/lib/python3/dist-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 456, in runInteraction
    **kwargs
  File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/lib/python3/dist-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 504, in runWithConnection
    self._db_pool.runWithConnection(inner_func, *args, **kwargs)
  File "/usr/lib/python3/dist-packages/twisted/python/threadpool.py", line 250, in inContext
    result = inContext.theWork()
  File "/usr/lib/python3/dist-packages/twisted/python/threadpool.py", line 266, in <lambda>
    inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "/usr/lib/python3/dist-packages/twisted/python/context.py", line 122, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/usr/lib/python3/dist-packages/twisted/python/context.py", line 85, in callWithContext
    return func(*args,**kw)
  File "/usr/lib/python3/dist-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/usr/lib/python3/dist-packages/twisted/python/compat.py", line 464, in reraise
    raise exception.with_traceback(traceback)
  File "/usr/lib/python3/dist-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 501, in inner_func
    return func(conn, *args, **kwargs)
  File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 339, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "/usr/lib/python3/dist-packages/synapse/storage/data_stores/main/room.py", line 475, in store_room_txn
    "is_public": is_public,
  File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 582, in simple_insert_txn
    txn.execute(sql, vals)
  File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 142, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 168, in _do_execute
    return func(sql, *args)
psycopg2.IntegrityError: duplicate key value violates unique constraint "rooms_pkey"
DETAIL:  Key (room_id)=(!uvlCXdYDUAhpPoX4VP:zemos.net) already exists.


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/synapse/handlers/federation.py", line 1202, in do_invite_join
    room_id=room_id, room_creator_user_id="", is_public=False
  File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/lib/python3/dist-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/lib/python3/dist-packages/synapse/storage/data_stores/main/room.py", line 493, in store_room
    raise StoreError(500, "Problem creating room.")
synapse.api.errors.StoreError: 500: Problem creating room.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/synapse/events/__init__.py", line 125, in getter
    return self._event_dict[key]
KeyError: 'auth_events'

During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/synapse/http/server.py", line 77, in wrapped_request_handler
    await h(self, request)
  File "/usr/lib/python3/dist-packages/synapse/http/server.py", line 326, in _async_render
    callback_return = await callback_return
  File "/usr/lib/python3/dist-packages/synapse/rest/client/v1/room.py", line 309, in on_POST
    third_party_signed=content.get("third_party_signed", None),
AttributeError: auth_events

[grinapo]

Actually my last comment has a parent error, only when first trying to join the invite:

2019-12-22 10:02:33,778 - synapse.http.server - 109 - ERROR - POST-497953- Failed handle request via 'JoinRoomAliasServlet': <XForwardedForRequest at 0x7f7af8932208 method='POST' uri='/_matrix/client/r0/join/!6DBPGS7a3
qsI0sKcWB%3Azemos.net' clientproto='HTTP/1.1' site=8008>
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1418, in _inlineCallbacks
    result = g.send(result)
StopIteration

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1418, in _inlineCallbacks
    result = g.send(result)
StopIteration

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 1418, in _inlineCallbacks
    result = g.send(result)
StopIteration

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/synapse/events/__init__.py", line 125, in getter
    return self._event_dict[key]
KeyError: 'auth_events'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/synapse/http/server.py", line 77, in wrapped_request_handler
    await h(self, request)
  File "/usr/lib/python3/dist-packages/synapse/http/server.py", line 326, in _async_render
    callback_return = await callback_return
  File "/usr/lib/python3/dist-packages/synapse/rest/client/v1/room.py", line 309, in on_POST
    third_party_signed=content.get("third_party_signed", None),
AttributeError: auth_events

[richvdh]

If you can't join the room, that's a separate problem: please open a new issue. This issue is about the harmless duplicate key value violates unique constraint "rooms_pkey" error.