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

Purge room fails with foreign key constraint (on destination_rooms table) #12787

Closed
richvdh opened this issue May 18, 2022 · 7 comments · Fixed by #12889
Closed

Purge room fails with foreign key constraint (on destination_rooms table) #12787

richvdh opened this issue May 18, 2022 · 7 comments · Fixed by #12889
Labels
S-Major Major functionality / product severely impaired, no satisfactory workaround. T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues.

Comments

@richvdh
Copy link
Member

richvdh commented May 18, 2022 

2022-05-18 18:16:32,993 - synapse.handlers.pagination - 662 - ERROR - shutdown_and_purge_room-0 - failed
Traceback (most recent call last):
  File "/home/rav/work/synapse/synapse/handlers/pagination.py", line 656, in _shutdown_and_purge_room
    await self.storage.purge_events.purge_room(room_id)
  File "/home/rav/work/synapse/synapse/storage/purge_events.py", line 36, in purge_room
    state_groups_to_delete = await self.stores.main.purge_room(room_id)
  File "/home/rav/work/synapse/synapse/storage/databases/main/purge_events.py", line 320, in purge_room
    return await self.db_pool.runInteraction(
  File "/home/rav/work/synapse/synapse/storage/database.py", line 832, in runInteraction
    return await delay_cancellation(_runInteraction())
  File "/home/rav/work/synapse/env3/lib/python3.8/site-packages/twisted/internet/defer.py", line 1656, in _inlineCallbacks
    result = current_context.run(
  File "/home/rav/work/synapse/env3/lib/python3.8/site-packages/twisted/python/failure.py", line 489, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/home/rav/work/synapse/synapse/storage/database.py", line 804, in _runInteraction
    result = await self.runWithConnection(
  File "/home/rav/work/synapse/synapse/storage/database.py", line 927, in runWithConnection
    return await make_deferred_yieldable(
  File "/home/rav/work/synapse/env3/lib/python3.8/site-packages/twisted/python/threadpool.py", line 244, in inContext
    result = inContext.theWork()  # type: ignore[attr-defined]
  File "/home/rav/work/synapse/env3/lib/python3.8/site-packages/twisted/python/threadpool.py", line 260, in <lambda>
    inContext.theWork = lambda: context.call(  # type: ignore[attr-defined]
  File "/home/rav/work/synapse/env3/lib/python3.8/site-packages/twisted/python/context.py", line 117, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/home/rav/work/synapse/env3/lib/python3.8/site-packages/twisted/python/context.py", line 82, in callWithContext
    return func(*args, **kw)
  File "/home/rav/work/synapse/env3/lib/python3.8/site-packages/twisted/enterprise/adbapi.py", line 282, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/home/rav/work/synapse/synapse/storage/database.py", line 920, in inner_func
    return func(db_conn, *args, **kwargs)
  File "/home/rav/work/synapse/synapse/storage/database.py", line 668, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "/home/rav/work/synapse/synapse/storage/databases/main/purge_events.py", line 328, in _purge_room_txn
    txn.execute("DELETE FROM rooms WHERE room_id = ?", (room_id,))
  File "/home/rav/work/synapse/synapse/storage/database.py", line 350, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/home/rav/work/synapse/synapse/storage/database.py", line 392, in _do_execute
    return func(sql, *args, **kwargs)
sqlite3.IntegrityError: FOREIGN KEY constraint failed
@richvdh
Copy link
Member Author

richvdh commented May 18, 2022

The problem is that there are still rows in destination_rooms.

This seems to have been introduced by #12594. I can't really see how that's meant to work :-S

@squahtx
Copy link
Contributor

squahtx commented May 18, 2022

My fault for not checking for foreign key constraints 🤦

@babolivier babolivier added S-Major Major functionality / product severely impaired, no satisfactory workaround. T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues. labels May 19, 2022
@TheNamelessWonderer
Copy link

I’ve got the same issue it seems but with PostgreSQL instead of SQLite:

2022-05-20 11:45:30,967 - synapse.handlers.room - 1614 - INFO - DELETE-33851 - Shutting down room '!tzZhrUKZsHknWcFJzf:matrix.org'
2022-05-20 11:45:30,971 - synapse.http.server - 120 - ERROR - DELETE-33851 - Failed handle request via 'RoomRestServlet': <XForwardedForRequest at 0x7fb2384a3b50 method='DELETE' uri='/_synapse/admin/v1/rooms/!tzZhrUKZsHknWcFJzf:matrix.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)=(!tzZhrUKZsHknWcFJzf:matrix.org) is still referenced from table "destination_rooms".

Should I open a new issue about this?

@squahtx
Copy link
Contributor

squahtx commented May 20, 2022

I’ve got the same issue it seems but with PostgreSQL instead of SQLite:

Should I open a new issue about this?

Looks like the same issue, with the same root cause.
As a workaround, you could try running DELETE FROM destination_rooms WHERE room_id = '!tzZhrUKZsHknWcFJzf:matrix.org'; before starting the purge.

@reivilibre reivilibre changed the title purge room fails with foreign key constraint Purge room fails with foreign key constraint (on destination_rooms table) May 20, 2022
@richvdh richvdh mentioned this issue May 26, 2022
Merged
@TheNamelessWonderer
Copy link

Thank you! Will the fix be in 1.60 or 1.61?

@clokep
Copy link
Member

clokep commented May 27, 2022

Thank you! Will the fix be in 1.60 or 1.61?

It will be in v1.61.

@dklimpel dklimpel mentioned this issue May 29, 2022
Closed
@schildbach
Copy link

Just to double check: When running into this issue, is there a risk of database corruption?

@clokep clokep mentioned this issue Jun 2, 2022
Closed
@Friskygote Friskygote mentioned this issue Jun 12, 2022
Closed
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
S-Major Major functionality / product severely impaired, no satisfactory workaround. T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix room deletion matrix-org/synapse
6 participants
@clokep @schildbach @richvdh @babolivier @squahtx @TheNamelessWonderer