synapse_port_db - Failed to insert: event_edges

[ToeiRei]

Description

Migrating the database from Sqlite3 to Postgres using the synapse_port_db script I run into an error as follows:

2022-07-05 23:32:18,631 - synapse_port_db - 245 - ERROR - Failed to insert: event_edges

Steps to reproduce

• start with a sqlite 3 instance on debian 11
• try to run the converter
• watch the converter do its job until it hits the error
• cry in pain

Homeserver

stargazer.at

Synapse Version

{"server":{"name":"Synapse","version":"1.62.0"}}

Installation Method

Debian packages from packages.matrix.org

Platform

Debian 11 in a VM, minimum install

Relevant log output

2022-07-05 23:32:18,631 - synapse_port_db - 245 - ERROR - Failed to insert: event_edges
Traceback (most recent call last):
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 243, in insert_many_txn
    txn.executemany(sql, rows)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 356, in executemany
    self._do_execute(self.txn.executemany, sql, *args)
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py", line 395, in _do_execute
    return func(sql, *args, **kwargs)
psycopg2.errors.ForeignKeyViolation: insert or update on table "event_edges" violates foreign key constraint "event_edges_event_id_fkey"
DETAIL:  Key (event_id)=($70n7eZhMwy80aMWDetgskT9nZ4OnAMVb_NVARjMOHpM) is not present in table "events".

2022-07-05 23:32:18,635 - synapse_port_db - 786 - ERROR -
Traceback (most recent call last):
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 763, in run
    await make_deferred_yieldable(
twisted.internet.defer.FirstError: FirstError[#6, [Failure instance: Traceback: <class 'psycopg2.errors.ForeignKeyViolation'>: insert or update on table "event_edges" violates foreign key constraint "event_edges_event_id_fkey"
DETAIL:  Key (event_id)=($70n7eZhMwy80aMWDetgskT9nZ4OnAMVb_NVARjMOHpM) is not present in table "events".

/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:700:errback
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:763:_startRunCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:857:_runCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:1750:gotResult
--- <exception caught here> ---
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:1656:_inlineCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/failure.py:514:throwExceptionIntoGenerator
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py:483:handle_table
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:835:runInteraction
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:1656:_inlineCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/failure.py:514:throwExceptionIntoGenerator
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:807:_runInteraction
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:930:runWithConnection
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py:244:inContext
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py:260:<lambda>
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py:117:callWithContext
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py:82:callWithContext
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/enterprise/adbapi.py:282:_runWithConnection
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:923:inner_func
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:671:new_transaction
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py:471:insert
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py:243:insert_many_txn
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:356:executemany
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:395:_do_execute
]]
Traceback (most recent call last):
  File "/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 763, in run
    await make_deferred_yieldable(
twisted.internet.defer.FirstError: FirstError[#6, [Failure instance: Traceback: <class 'psycopg2.errors.ForeignKeyViolation'>: insert or update on table "event_edges" violates foreign key constraint "event_edges_event_id_fkey"
DETAIL:  Key (event_id)=($70n7eZhMwy80aMWDetgskT9nZ4OnAMVb_NVARjMOHpM) is not present in table "events".

/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:700:errback
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:763:_startRunCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:857:_runCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:1750:gotResult
--- <exception caught here> ---
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:1656:_inlineCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/failure.py:514:throwExceptionIntoGenerator
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py:483:handle_table
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:835:runInteraction
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:1656:_inlineCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/failure.py:514:throwExceptionIntoGenerator
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:807:_runInteraction
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:930:runWithConnection
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py:244:inContext
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py:260:<lambda>
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py:117:callWithContext
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py:82:callWithContext
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/enterprise/adbapi.py:282:_runWithConnection
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:923:inner_func
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:671:new_transaction
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py:471:insert
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py:243:insert_many_txn
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:356:executemany
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:395:_do_execute
]]
FirstError[#6, [Failure instance: Traceback: <class 'psycopg2.errors.ForeignKeyViolation'>: insert or update on table "event_edges" violates foreign key constraint "event_edges_event_id_fkey"
DETAIL:  Key (event_id)=($70n7eZhMwy80aMWDetgskT9nZ4OnAMVb_NVARjMOHpM) is not present in table "events".

/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:700:errback
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:763:_startRunCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:857:_runCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:1750:gotResult
--- <exception caught here> ---
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:1656:_inlineCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/failure.py:514:throwExceptionIntoGenerator
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py:483:handle_table
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:835:runInteraction
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/internet/defer.py:1656:_inlineCallbacks
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/failure.py:514:throwExceptionIntoGenerator
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:807:_runInteraction
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:930:runWithConnection
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py:244:inContext
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/threadpool.py:260:<lambda>
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py:117:callWithContext
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/python/context.py:82:callWithContext
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/twisted/enterprise/adbapi.py:282:_runWithConnection
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:923:inner_func
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:671:new_transaction
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py:471:insert
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py:243:insert_many_txn
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:356:executemany
/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/database.py:395:_do_execute

### Anything else that would be useful to know?

_No response_

[DMRobertson]

I'm not sure if this is a bug in portdb or if your sqlite database is corrupted.

What happens if you stop Synapse and run this query against sqlite?

SELECT * from events where event_id='$70n7eZhMwy80aMWDetgskT9nZ4OnAMVb_NVARjMOHpM';

[ToeiRei]

$ sqlite3 homeserver.db
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> SELECT * from events where event_id='$70n7eZhMwy80aMWDetgskT9nZ4OnAMVb_NVARjMOHpM';
-8558|2|$70n7eZhMwy80aMWDetgskT9nZ4OnAMVb_NVARjMOHpM|m.room.member|!UpbiadsgRndwvYKPLK:neko.dev|||1|1|2|1636376074991|1656923521381|@deepbluev7:neko.dev|0|master|@deepbluev7:neko.dev|
sqlite>

[DMRobertson]

Thanks. Presumably a portdb bug then. I would guess that we don't make any effort to port over tables that are cited by foreign key constraints before the tables that cite them.

[ToeiRei]

Feel free to use me as a crash test dummy on debugging. I run the importer again on a more recent snapshot and the event_id is the same.

[DMRobertson]

Proposed (short term) fix:

• have port db maintain a list of foreign key dependencies between tables
• ensure we port cited tables before citing tables
• need to ensure this works in incremental mode: can't have a append-only table refer to a non-append only table

[DMRobertson]

Although we seemingly already try to do this?

synapse/synapse/_scripts/synapse_port_db.py

Lines 754 to 780 in b036685

	constraints = await self.get_table_constraints()
	tables_ported = set() # type: Set[str]
	while tables_to_port_info_map:
	# Pulls out all tables that are still to be ported and which
	# only depend on tables that are already ported (if any).
	tables_to_port = [
	table
	for table in tables_to_port_info_map
	if not constraints.get(table, set()) - tables_ported
	]
	await make_deferred_yieldable(
	defer.gatherResults(
	[
	run_in_background(
	self.handle_table,
	table,
	*tables_to_port_info_map.pop(table),
	)
	for table in tables_to_port
	],
	consumeErrors=True,
	)
	)
	tables_ported.update(tables_to_port)

[ToeiRei]

What info can I provide to help you out further?

I run this against a PostgreSQL (cluster) V12

[DMRobertson]

A few questions:

• Is Synapse running while you run the portdb script?

• Have you ran the script before? Were there any other errors?

• Could you also run this query against the postgres DB in its current state and let me know what you get?

              SELECT DISTINCT
                  tc.table_name,
                  ccu.table_name AS foreign_table_name
              FROM
                  information_schema.table_constraints AS tc
                  INNER JOIN information_schema.constraint_column_usage AS ccu
                  USING (table_schema, constraint_name)
              WHERE tc.constraint_type = 'FOREIGN KEY'
                AND tc.table_name != ccu.table_name;

• Do you get the same error if you rerun the edit: portdb script?

[ToeiRei]

• Synapse was shut down and I worked on a copy of the database
• Tried the script before onto an empty database -> same error
• Tried to rerun the script into the database from before -> same error
• Tried to run it against a newer database -> same error
• Query:
  
• Script: where do I get it from?