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

turt2live · 2018-06-08T16:07:32Z

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

The text was updated successfully, but these errors were encountered:

elinorbgr · 2019-05-17T10:34:26Z

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 · 2019-07-08T17:57:09Z

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 · 2019-10-24T17:46:34Z

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 · 2019-12-15T21:54:51Z

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 · 2019-12-22T09:08:11Z

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 · 2020-01-10T12:56:45Z

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.

This is intended as a precursor to storing room versions when we receive an invite over federation, but has the happy side-effect of fixing #3374 at last. In short: change the store_room with try/except to a proper upsert which updates the right columns.

richvdh · 2020-03-19T11:03:29Z

fixed by #6968

This is intended as a precursor to storing room versions when we receive an invite over federation, but has the happy side-effect of fixing matrix-org#3374 at last. In short: change the store_room with try/except to a proper upsert which updates the right columns.

turt2live mentioned this issue Jun 8, 2018

workers stop working after elevated traffic #2738

Closed

neilisfragile added z-p3 (Deprecated Label) z-minor (Deprecated Label) labels Jun 22, 2018

richvdh mentioned this issue Dec 19, 2019

UniqueViolation: duplicate key value violates unique constraint "event_reference_hashes_event_id_algorithm_key" #6561

Closed

richvdh changed the title ~~Synapse tries to store rooms that already exist~~ duplicate key value violates unique constraint "rooms_pkey" error when rejoining a room Jan 10, 2020

grinapo mentioned this issue Jan 10, 2020

exception Failed handle request via 'JoinRoomAliasServlet' when attempting to join !6DBPGS7a3qsI0sKcWB:zemos.net #6680

Closed

richvdh mentioned this issue Feb 21, 2020

Upsert room version when we join over federation #6968

Merged

richvdh closed this as completed Mar 19, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

turt2live commented Jun 8, 2018 •

edited

Loading

elinorbgr commented May 17, 2019 •

edited

Loading

elinorbgr commented Jul 8, 2019

silkeh commented Oct 24, 2019

grinapo commented Dec 15, 2019

grinapo commented Dec 22, 2019

richvdh commented Jan 10, 2020

richvdh commented Mar 19, 2020

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

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

Comments

turt2live commented Jun 8, 2018 • edited Loading

Version information

elinorbgr commented May 17, 2019 • edited Loading

elinorbgr commented Jul 8, 2019

silkeh commented Oct 24, 2019

grinapo commented Dec 15, 2019

grinapo commented Dec 22, 2019

richvdh commented Jan 10, 2020

richvdh commented Mar 19, 2020

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

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

turt2live commented Jun 8, 2018 •

edited

Loading

elinorbgr commented May 17, 2019 •

edited

Loading