Skip to content
This repository was archived by the owner on Apr 26, 2024. It is now read-only.

Synapse admin api delete room fails with error: roomid is still referenced from table "destination_rooms" #12907

Closed
luilegeant opened this issue May 29, 2022 · 2 comments
Closed

Synapse admin api delete room fails with error: roomid is still referenced from table "destination_rooms" #12907

luilegeant opened this issue May 29, 2022 · 2 comments

Comments

@luilegeant
Copy link

Description

When using the synapse admin api to delete (and purge) rooms that do not contain any local members, the request fails.

Steps to reproduce

  • Join a room (old fosdem.org one for me)
  • Leave that room the next day
  • Wait a little (over 90 days) and let the synapse purge work
  • Try to "clean up" using curl to delete old rooms (with purge set to true) that do not have any local member

The following two queries fail:

Expected results

I would expect the room to be deleted and purged.

Version information

  • Homeserver: matrix.luilegeant.com

Details:

  • Version: 1.59.1
  • Install method: docker hub: docker pull matrixdotorg/synapse:v1.59.1
  • Platform:
    • container
    • synapse is using db postgres: container postgres:12.6-alpine
    • behind nginx as reverse proxy

Extra details (logs & exception)

Synapse logs (when using delete v1):

2022-05-29 10:10:08,501 - synapse.http.server - 120 - ERROR - DELETE-120063 - Failed handle request via 'RoomRestServlet': <XForwardedForRequest at 0x7fb63ca50250 method='DELETE' uri='/_synapse/admin/v1/rooms/!BlToLWACcXqAvpatvj:fosdem.org' clientproto='HTTP/1.1' site='8008'>
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/synapse/http/server.py", line 298, in _async_render_wrapper
    callback_return = await self._async_render(request)
  File "/usr/local/lib/python3.9/site-packages/synapse/http/server.py", line 500, in _async_render
    callback_return = await raw_callback_return
  File "/usr/local/lib/python3.9/site-packages/synapse/rest/admin/rooms.py", line 311, in on_DELETE
    return await self._delete_room(
  File "/usr/local/lib/python3.9/site-packages/synapse/rest/admin/rooms.py", line 368, in _delete_room
    await pagination_handler.purge_room(room_id, force=force_purge)
  File "/usr/local/lib/python3.9/site-packages/synapse/handlers/pagination.py", line 417, in purge_room
    await self.storage.purge_events.purge_room(room_id)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/purge_events.py", line 36, in purge_room
    state_groups_to_delete = await self.stores.main.purge_room(room_id)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/databases/main/purge_events.py", line 322, in purge_room
    return await self.db_pool.runInteraction(
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 814, in runInteraction
    return await delay_cancellation(_runInteraction())
  File "/usr/local/lib/python3.9/site-packages/twisted/internet/defer.py", line 1656, in _inlineCallbacks
    result = current_context.run(
  File "/usr/local/lib/python3.9/site-packages/twisted/python/failure.py", line 514, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 786, in _runInteraction
    result = await self.runWithConnection(
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 909, in runWithConnection
    return await make_deferred_yieldable(
  File "/usr/local/lib/python3.9/site-packages/twisted/python/threadpool.py", line 244, in inContext
    result = inContext.theWork()  # type: ignore[attr-defined]
  File "/usr/local/lib/python3.9/site-packages/twisted/python/threadpool.py", line 260, in <lambda>
    inContext.theWork = lambda: context.call(  # type: ignore[attr-defined]
  File "/usr/local/lib/python3.9/site-packages/twisted/python/context.py", line 117, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/usr/local/lib/python3.9/site-packages/twisted/python/context.py", line 82, in callWithContext
    return func(*args, **kw)
  File "/usr/local/lib/python3.9/site-packages/twisted/enterprise/adbapi.py", line 282, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 902, in inner_func
    return func(db_conn, *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 650, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/databases/main/purge_events.py", line 330, in _purge_room_txn
    txn.execute("DELETE FROM rooms WHERE room_id = ?", (room_id,))
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 337, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 379, in _do_execute
    return func(sql, *args, **kwargs)
psycopg2.errors.ForeignKeyViolation: update or delete on table "rooms" violates foreign key constraint "destination_rooms_room_id_fkey" on table "destination_rooms"
DETAIL:  Key (room_id)=(!BlToLWACcXqAvpatvj:fosdem.org) is still referenced from table "destination_rooms".

Synapse logs (when using delete v2):

2022-05-29 10:23:51,244 - synapse.handlers.pagination - 744 - INFO - DELETE-121887 - starting shutdown room_id !BlToLWACcXqAvpatvj:fosdem.org with delete_id RqkfCekZGKDZTmPZ
2022-05-29 10:23:51,247 - synapse.access.http.8008 - 427 - INFO - DELETE-121887 - 192.168.1.244 - 8008 - {@luilegeant:luilegeant.com} Processed request: 0.005sec/0.001sec (0.001sec, 0.000sec) (0.001sec/0.001sec/1) 32B 200 "DELETE /_synapse/admin/v2/rooms/%21BlToLWACcXqAvpatvj%3Afosdem.org HTTP/1.1" "curl/7.68.0" [0 dbevts]
2022-05-29 10:23:51,249 - synapse.handlers.room - 1614 - INFO - shutdown_and_purge_room-2 - Shutting down room '!BlToLWACcXqAvpatvj:fosdem.org'
2022-05-29 10:23:51,249 - synapse.handlers.pagination - 644 - INFO - shutdown_and_purge_room-2 - starting purge room_id !BlToLWACcXqAvpatvj:fosdem.org
2022-05-29 10:23:51,254 - synapse.handlers.pagination - 662 - ERROR - shutdown_and_purge_room-2 - failed
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/synapse/handlers/pagination.py", line 656, in _shutdown_and_purge_room
    await self.storage.purge_events.purge_room(room_id)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/purge_events.py", line 36, in purge_room
    state_groups_to_delete = await self.stores.main.purge_room(room_id)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/databases/main/purge_events.py", line 322, in purge_room
    return await self.db_pool.runInteraction(
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 814, in runInteraction
    return await delay_cancellation(_runInteraction())
  File "/usr/local/lib/python3.9/site-packages/twisted/internet/defer.py", line 1656, in _inlineCallbacks
    result = current_context.run(
  File "/usr/local/lib/python3.9/site-packages/twisted/python/failure.py", line 514, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 786, in _runInteraction
    result = await self.runWithConnection(
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 909, in runWithConnection
    return await make_deferred_yieldable(
  File "/usr/local/lib/python3.9/site-packages/twisted/python/threadpool.py", line 244, in inContext
    result = inContext.theWork()  # type: ignore[attr-defined]
  File "/usr/local/lib/python3.9/site-packages/twisted/python/threadpool.py", line 260, in <lambda>
    inContext.theWork = lambda: context.call(  # type: ignore[attr-defined]
  File "/usr/local/lib/python3.9/site-packages/twisted/python/context.py", line 117, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/usr/local/lib/python3.9/site-packages/twisted/python/context.py", line 82, in callWithContext
    return func(*args, **kw)
  File "/usr/local/lib/python3.9/site-packages/twisted/enterprise/adbapi.py", line 282, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 902, in inner_func
    return func(db_conn, *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 650, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/databases/main/purge_events.py", line 330, in _purge_room_txn
    txn.execute("DELETE FROM rooms WHERE room_id = ?", (room_id,))
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 337, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 379, in _do_execute
    return func(sql, *args, **kwargs)
psycopg2.errors.ForeignKeyViolation: update or delete on table "rooms" violates foreign key constraint "destination_rooms_room_id_fkey" on table "destination_rooms"
DETAIL:  Key (room_id)=(!BlToLWACcXqAvpatvj:fosdem.org) is still referenced from table "destination_rooms".

Executed command (delete v2):

curl -H "Authorization: Bearer ¯\_(ツ)_/¯" -H "Content-Type: application/json" --data '{"purge":true}' -X "DELETE" "https://matrix.luilegeant.com/_synapse/admin/v2/rooms/%21BlToLWACcXqAvpatvj%3Afosdem.org"

Executed command (delete status v2) & result:

curl -H "Authorization: Bearer ¯\_(ツ)_/¯" "https://matrix.luilegeant.com/_synapse/admin/v2/rooms/%21BlToLWACcXqAvpatvj%3Afosdem.org/delete_status"
{"results":[{"delete_id":"pelnGCmxXwDCjMSs","status":"failed","shutdown_room":{"kicked_users":[],"failed_to_kick_users":[],"local_aliases":[],"new_room_id":null},"error":"update or delete on table \"rooms\" violates foreign key constraint \"destination_rooms_room_id_fkey\" on table \"destination_rooms\"\nDETAIL:  Key (room_id)=(!BlToLWACcXqAvpatvj:fosdem.org) is still referenced from table \"destination_rooms\".\n"},{"delete_id":"RqkfCekZGKDZTmPZ","status":"failed","shutdown_room":{"kicked_users":[],"failed_to_kick_users":[],"local_aliases":[],"new_room_id":null},"error":"update or delete on table \"rooms\" violates foreign key constraint \"destination_rooms_room_id_fkey\" on table \"destination_rooms\"\nDETAIL:  Key (room_id)=(!BlToLWACcXqAvpatvj:fosdem.org) is still referenced from table \"destination_rooms\".\n"}]}
@dklimpel
Copy link
Contributor

This is a duplicate to #12787

@luilegeant
Copy link
Author

My mistake I didn't realise. I was searching for "delete room" as keyword in the bug tracker not "purge".

@luilegeant luilegeant closed this as not planned Won't fix, can't repro, duplicate, stale May 29, 2022
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@dklimpel @luilegeant