portdb from SQLite to Postgres fails if Sliding Sync has been used: `sliding_sync_connections.connection_key`: cannot insert a non-DEFAULT value #18053

nbabenko · 2024-12-27T06:40:58Z

Description

Hi,

I have an old version of the synapse server that I installed myself with SQLite database. I have set up a new one with the https://github.com/spantaleev/matrix-docker-ansible-deploy and trying to migrate my old SQLite to Postgres.

Getting error when it tries to insert values in identity column.

Steps to reproduce

Install a fresh server with a script https://github.com/spantaleev/matrix-docker-ansible-deploy:
ansible-playbook -i inventory/hosts setup.yml --tags=install-all
Have a SQLite database from existing server and import it to the server.
Execute migration script:

docker run --rm -it \
  --name synapse-migration \
  --network matrix-postgres \
  --mount type=bind,src=/matrix/synapse/storage/media-store/homeserver.db,dst=/homeserver.db \
  --mount type=bind,src=/matrix/synapse/config/homeserver.yaml,dst=/homeserver.yaml \
  --mount type=bind,src=/matrix/synapse/config/matrix.example.com.signing.key,dst=/data/matrix.example.com.signing.key \
  python:3.12 bash -c "
    pip install matrix-synapse lxml psycopg2-binary && \
    python3 -m py_compile /usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py && \
    python3 -m synapse._scripts.synapse_port_db \
      --sqlite-database /homeserver.db \
      --postgres-config /homeserver.yaml
  "

Receive an error:

********
2024-12-27 06:20:20,269 - synapse_port_db - 428 - INFO - Table user_stats_current: 0/10 (rows 1-0) already ported
2024-12-27 06:20:20,270 - synapse_port_db - 428 - INFO - Table e2e_cross_signing_signatures: 0/37 (rows 1-0) already ported
2024-12-27 06:20:20,271 - synapse_port_db - 428 - INFO - Table room_retention: 0/0 (rows 1-0) already ported
2024-12-27 06:20:20,271 - synapse_port_db - 428 - INFO - Table server_keys_json: 0/9 (rows 1-0) already ported
2024-12-27 06:20:20,272 - synapse_port_db - 428 - INFO - Table refresh_tokens: 0/0 (rows 1-0) already ported
2024-12-27 06:20:20,274 - synapse_port_db - 428 - INFO - Table event_labels: 0/0 (rows 1-0) already ported
2024-12-27 06:20:20,275 - synapse_port_db - 428 - INFO - Table federation_inbound_events_staging: 0/0 (rows 1-0) already ported
2024-12-27 06:20:20,275 - synapse_port_db - 428 - INFO - Table user_daily_visits: 0/734 (rows 1-0) already ported
user_directory_stream_pos: 100% (1/1)
federation_stream_position: 100% (2/2)
server_signature_keys: 100% (8/8)
users: 100% (10/10)
e2e_room_keys_versions: 100% (10/10)
open_id_tokens: 100% (22/22)
timeline_gaps: 100% (26/26)
user_signature_stream: 100% (11/11)
device_lists_remote_cache: 100% (31/31)
stream_positions: 100% (12/12)
pushers: 100% (8/8)
room_stats_current: 100% (55/55)
e2e_fallback_keys_json: 100% (35/35)
event_push_actions: 100% (69/69)
user_filters: 100% (18/18)
remote_media_cache_thumbnails: 100% (131/131)
event_search: 100% (302/302)
room_tags: 100% (2/2)
device_lists_outbound_last_success: 100% (13/13)
state_group_edges: 100% (859/859)
current_state_delta_stream: 100% (844/844)
event_auth_chains: 100% (906/906)
state_groups_state: 74% (1000/1346)
device_lists_changes_converted_stream_position: 100% (1/1)
e2e_device_keys_json: 100% (35/35)
2024-12-27 06:20:21,554 - synapse_port_db - 283 - ERROR - Failed to insert: sliding_sync_connections
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 281, in insert_many_txn
    txn.executemany(sql, rows)
  File "/usr/local/lib/python3.12/site-packages/synapse/storage/database.py", line 443, in executemany
    self._do_execute(self.txn.executemany, sql, *args)
  File "/usr/local/lib/python3.12/site-packages/synapse/storage/database.py", line 488, in _do_execute
    return func(sql, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.GeneratedAlways: cannot insert a non-DEFAULT value into column "connection_key"
DETAIL:  Column "connection_key" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

2024-12-27 06:20:21,562 - synapse_port_db - 931 - ERROR - 
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 908, in run
    await make_deferred_yieldable(
  File "/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py", line 1183, in __iter__
    yield self
twisted.internet.defer.FirstError: FirstError[#47, [Failure instance: Traceback: <class 'psycopg2.errors.GeneratedAlways'>: cannot insert a non-DEFAULT value into column "connection_key"
DETAIL:  Column "connection_key" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:532:handle_table
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:952:runInteraction
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:918:_runInteraction
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1047:runWithConnection
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:269:inContext
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:285:<lambda>
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:117:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:82:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/enterprise/adbapi.py:282:_runWithConnection
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1040:inner_func
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:780:new_transaction
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:520:insert
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:281:insert_many_txn
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:443:executemany
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:488:_do_execute
]]
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 908, in run
    await make_deferred_yieldable(
  File "/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py", line 1183, in __iter__
    yield self
twisted.internet.defer.FirstError: FirstError[#47, [Failure instance: Traceback: <class 'psycopg2.errors.GeneratedAlways'>: cannot insert a non-DEFAULT value into column "connection_key"
DETAIL:  Column "connection_key" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:532:handle_table
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:952:runInteraction
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:918:_runInteraction
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1047:runWithConnection
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:269:inContext
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:285:<lambda>
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:117:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:82:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/enterprise/adbapi.py:282:_runWithConnection
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1040:inner_func
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:780:new_transaction
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:520:insert
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:281:insert_many_txn
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:443:executemany
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:488:_do_execute
]]
FirstError[#47, [Failure instance: Traceback: <class 'psycopg2.errors.GeneratedAlways'>: cannot insert a non-DEFAULT value into column "connection_key"
DETAIL:  Column "connection_key" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:532:handle_table
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:952:runInteraction
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:2013:_inlineCallbacks
/usr/local/lib/python3.12/site-packages/twisted/python/failure.py:467:throwExceptionIntoGenerator
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:918:_runInteraction
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1047:runWithConnection
/usr/local/lib/python3.12/site-packages/twisted/internet/defer.py:1183:__iter__
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:269:inContext
/usr/local/lib/python3.12/site-packages/twisted/python/threadpool.py:285:<lambda>
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:117:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/python/context.py:82:callWithContext
/usr/local/lib/python3.12/site-packages/twisted/enterprise/adbapi.py:282:_runWithConnection
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:1040:inner_func
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:780:new_transaction
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:520:insert
/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py:281:insert_many_txn
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:443:executemany
/usr/local/lib/python3.12/site-packages/synapse/storage/database.py:488:_do_execute
]]root@localhost:/matrix/synapse/config#

Workaround attempts:

I also tried to adjust the migration script:

docker run --rm -it \
  --name synapse-migration \
  --network matrix-postgres \
  --mount type=bind,src=/matrix-restore/homeserver.db,dst=/homeserver.db \
  --mount type=bind,src=/matrix/synapse/config/homeserver.yaml,dst=/homeserver.yaml \
  --mount type=bind,src=/matrix/synapse/config/matrix.example.com.signing.key,dst=/data/matrix.example.com.signing.key \
  python:3.12 bash -c "
    pip install matrix-synapse lxml psycopg2-binary && \
    sed -i \"/def insert_many_txn/,/raise/ {
      s/INSERT INTO %s (%s) VALUES (%s)/INSERT INTO %s (%s) OVERRIDING SYSTEM VALUE VALUES (%s)/g
    }\" /usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py && \
    python3 -m py_compile /usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py && \
    python3 -m synapse._scripts.synapse_port_db \
      --sqlite-database /homeserver.db \
      --postgres-config /homeserver.yaml
  "

The migration itself went well but then my synapse wont start with it and I've got a database with an issue:

journalctl -xeu matrix-synapse.service
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/pusher.py", line 82, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/roommember.py", line 99, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/pusher.py", line 578, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/receipts.py", line 149, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/stream.py", line 595, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/receipts.py", line 1164, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/client_ips.py", line 414, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/client_ips.py", line 84, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          super().__init__(database, db_conn, hs)
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/deviceinbox.py", line 92, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          self._to_device_msg_id_gen: MultiWriterIdGenerator = MultiWriterIdGenerator(
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:                                                               ^^^^^^^^^^^^^^^^^^^^^^^
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/id_generators.py", line 295, in __init__
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          self._sequence_gen.check_consistency(
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:        File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/sequence.py", line 192, in check_consistency
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          raise IncorrectDatabaseSetup(
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      synapse.storage.engines._base.IncorrectDatabaseSetup:
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      Postgres sequence 'device_inbox_sequence' is inconsistent with associated stream position
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      of 'to_device' in the 'stream_positions' table.
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      This is likely a programming error and should be reported at
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      https://github.com/matrix-org/synapse.
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      A temporary workaround to fix this error is to shut down Synapse (including
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      any and all workers) and run the following SQL:
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:          DELETE FROM stream_positions WHERE stream_name = 'to_device';
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:      This will need to be done every time the server is restarted.
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]:  There may be more information in the logs.
Dec 26 16:23:10 matrix-example-com matrix-synapse[328980]: **********************************************************************************
Dec 26 16:23:11 matrix-example-com systemd[1]: matrix-synapse.service: Main process exited, code=exited, status=1/FAILURE
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░ 
░░ An ExecStart= process belonging to unit matrix-synapse.service has exited.
░░ 
░░ The process' exit code is 'exited' and its exit status is 1.
Dec 26 16:23:13 matrix-example-com systemd[1]: matrix-synapse.service: Failed with result 'exit-code'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░ 
░░ The unit matrix-synapse.service has entered the 'failed' state with result 'exit-code'.
Dec 26 16:23:13 matrix-example-com systemd[1]: Failed to start matrix-synapse.service - Synapse server.
░░ Subject: A start job for unit matrix-synapse.service has failed
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░ 
░░ A start job for unit matrix-synapse.service has finished with a failure.
░░ 
░░ The job identifier is 55830 and the job result is failed.
lines 1086-1147/1147 (END)

Homeserver

personal homeserver

Synapse Version

1.121.1

Installation Method

Default configuration using https://github.com/spantaleev/matrix-docker-ansible-deploy

Database

PostgreSQL

Workers

I don't know

Platform

Ubuntu 24.04

Configuration

Standard configuration set up by the default setup of https://github.com/spantaleev/matrix-docker-ansible-deploy

Relevant log output

see above

Anything else that would be useful to know?

No response

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

nbabenko · 2025-01-06T05:45:59Z

After some research I have added sliding_sync_connections to the IGNORED TABLES list and it worked (although often migration process was getting stuck for me on the last steps). And now the instance more or less works with a new database. But I nevertheless lost some media (avatars etc) and order of the messages that were just before migration got distorted. Otherwise works more or less stable now.
The script I have used:

docker run --rm -it
--name synapse-migration
--network matrix-postgres
--mount type=bind,src=/matrix/synapse/storage/homeserver.db,dst=/homeserver.db
--mount type=bind,src=/matrix/synapse/config/homeserver.yaml,dst=/homeserver.yaml
--mount type=bind,src=/matrix/synapse/config/matrix.mytrunk.org.signing.key,dst=/data/matrix.mytrunk.org.signing.key
python:3.12 bash -c "
pip install matrix-synapse lxml psycopg2-binary &&
sed -i '/^IGNORED_TABLES = {/a\ "sliding_sync_connections",' /usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py &&
python3 -m py_compile /usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py &&
python3 -m synapse._scripts.synapse_port_db
--sqlite-database /homeserver.db
--postgres-config /homeserver.yaml
"

reivilibre · 2025-01-07T11:33:38Z

Seems to have been introduced by e43c2b0 introduced in 1.115.0

c.f.:

https://github.com/element-hq/synapse/blob/9fb7333a7cda5e7bf288ca8b044878749f0c74a5/synapse/storage/schema/main/delta/87/02_per_connection_state.sql#L23C22-L23C48
synapse/synapse/storage/engines/_base.py

Line 33 in 9fb7333

AUTO_INCREMENT_PRIMARY_KEYPLACEHOLDER = "$%AUTO_INCREMENT_PRIMARY_KEY%$"
https://github.com/element-hq/synapse/blob/develop/synapse/storage/engines/sqlite.py#L174
https://github.com/element-hq/synapse/blame/develop/synapse/storage/engines/postgres.py#L263

foegra · 2025-01-15T00:00:50Z

Running this command:

docker compose run --rm --entrypoint /usr/local/bin/synapse_port_db synapse --sqlite-database=/data/homeserver.db --postgres-config=/data/homeserver-postgres.yaml

This is what i get:

`Preparing sqlite3...
2025-01-15 00:53:04,753 - synapse.storage.prepare_database - 135 - INFO - ('main', 'state'): Checking existing schema version
2025-01-15 00:53:04,754 - synapse.storage.prepare_database - 139 - INFO - ('main', 'state'): Existing schema is 88 (+7 deltas)
2025-01-15 00:53:04,754 - synapse.storage.databases.main - 395 - INFO - Checking database for consistency with configuration...
2025-01-15 00:53:04,754 - synapse.storage.prepare_database - 433 - INFO - Applying schema deltas for v88
2025-01-15 00:53:04,755 - synapse.storage.prepare_database - 568 - INFO - Schema now up to date
2025-01-15 00:53:04,924 - synapse.storage.util.id_generators - 98 - INFO - Initialising stream generator for push_rules(id): 7
2025-01-15 00:53:04,925 - synapse.storage.util.id_generators - 98 - INFO - Initialising stream generator for push_rules_enable(id): 8
2025-01-15 00:53:06,129 - synapse.storage.databases.main.event_push_actions - 1242 - INFO - Searching for stream ordering 1 month ago
2025-01-15 00:53:06,130 - synapse.storage.databases.main.event_push_actions - 1246 - INFO - Found stream ordering 1 month ago: it's 0
2025-01-15 00:53:06,130 - synapse.storage.databases.main.event_push_actions - 1249 - INFO - Searching for stream ordering 1 day ago
2025-01-15 00:53:06,131 - synapse.storage.databases.main.event_push_actions - 1253 - INFO - Found stream ordering 1 day ago: it's 4466
Preparing psycopg2...
2025-01-15 00:53:06,171 - synapse.storage.prepare_database - 135 - INFO - ('main', 'state'): Checking existing schema version
2025-01-15 00:53:06,177 - synapse.storage.prepare_database - 139 - INFO - ('main', 'state'): Existing schema is 88 (+7 deltas)
2025-01-15 00:53:06,177 - synapse.storage.databases.main - 395 - INFO - Checking database for consistency with configuration...
2025-01-15 00:53:06,180 - synapse.storage.prepare_database - 433 - INFO - Applying schema deltas for v88
2025-01-15 00:53:06,182 - synapse.storage.prepare_database - 568 - INFO - Schema now up to date
2025-01-15 00:53:06,186 - synapse.metrics - 121 - WARNING - synapse_background_update_status already registered, reregistering
2025-01-15 00:53:06,231 - synapse.storage.util.id_generators - 98 - INFO - Initialising stream generator for push_rules(id): 7
2025-01-15 00:53:06,232 - synapse.storage.util.id_generators - 98 - INFO - Initialising stream generator for push_rules_enable(id): 8
2025-01-15 00:53:06,239 - synapse_port_db - 931 - ERROR -
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 731, in run
self.postgres_store = self.build_db_store(
^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 649, in build_db_store
store = Store(DatabasePool(hs, db_config, engine), db_conn, hs) # type: ignore[arg-type]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/event_push_actions.py", line 1894, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/event_push_actions.py", line 263, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/client_ips.py", line 84, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/deviceinbox.py", line 975, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/devices.py", line 1611, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/events_bg_updates.py", line 117, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/media_repository.py", line 114, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/registration.py", line 2102, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/registration.py", line 183, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/room.py", line 1801, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/room.py", line 152, in init
self._un_partial_stated_rooms_stream_id_gen = MultiWriterIdGenerator(
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/id_generators.py", line 295, in init
self._sequence_gen.check_consistency(
File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/sequence.py", line 192, in check_consistency
raise IncorrectDatabaseSetup(
synapse.storage.engines._base.IncorrectDatabaseSetup:
Postgres sequence 'un_partial_stated_room_stream_sequence' is inconsistent with associated stream position
of 'un_partial_stated_room_stream' in the 'stream_positions' table.

This is likely a programming error and should be reported at
https://github.com/matrix-org/synapse.

A temporary workaround to fix this error is to shut down Synapse (including
any and all workers) and run the following SQL:

DELETE FROM stream_positions WHERE stream_name = 'un_partial_stated_room_stream';

This will need to be done every time the server is restarted.

Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 731, in run
self.postgres_store = self.build_db_store(
^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/synapse/_scripts/synapse_port_db.py", line 649, in build_db_store
store = Store(DatabasePool(hs, db_config, engine), db_conn, hs) # type: ignore[arg-type]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/event_push_actions.py", line 1894, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/event_push_actions.py", line 263, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/client_ips.py", line 84, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/deviceinbox.py", line 975, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/devices.py", line 1611, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/events_bg_updates.py", line 117, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/media_repository.py", line 114, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/registration.py", line 2102, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/registration.py", line 183, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/room.py", line 1801, in init
super().init(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/room.py", line 152, in init
self._un_partial_stated_rooms_stream_id_gen = MultiWriterIdGenerator(
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/id_generators.py", line 295, in init
self._sequence_gen.check_consistency(
File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/sequence.py", line 192, in check_consistency
raise IncorrectDatabaseSetup(
synapse.storage.engines._base.IncorrectDatabaseSetup:
Postgres sequence 'un_partial_stated_room_stream_sequence' is inconsistent with associated stream position
of 'un_partial_stated_room_stream' in the 'stream_positions' table.

This is likely a programming error and should be reported at
https://github.com/matrix-org/synapse.

A temporary workaround to fix this error is to shut down Synapse (including
any and all workers) and run the following SQL:

DELETE FROM stream_positions WHERE stream_name = 'un_partial_stated_room_stream';

This will need to be done every time the server is restarted.

Postgres sequence 'un_partial_stated_room_stream_sequence' is inconsistent with associated stream position
of 'un_partial_stated_room_stream' in the 'stream_positions' table.

This is likely a programming error and should be reported at
https://github.com/matrix-org/synapse.

A temporary workaround to fix this error is to shut down Synapse (including
any and all workers) and run the following SQL:

DELETE FROM stream_positions WHERE stream_name = 'un_partial_stated_room_stream';

This will need to be done every time the server is restarted.`

reivilibre added A-Portdb S-Critical T-Defect A-Sync labels Jan 7, 2025

reivilibre changed the title ~~Error: cannot insert a non-DEFAULT value into column "connection_key" during migration from SQLite to Postgres~~ portdb from SQLite to Postgres fails if Sliding Sync has been used: sliding_sync_connections.connection_key: cannot insert a non-DEFAULT value Jan 7, 2025

Videothek mentioned this issue Jan 23, 2025

synapse_port_db throws python module error #18105

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

portdb from SQLite to Postgres fails if Sliding Sync has been used: `sliding_sync_connections.connection_key`: cannot insert a non-DEFAULT value #18053

portdb from SQLite to Postgres fails if Sliding Sync has been used: `sliding_sync_connections.connection_key`: cannot insert a non-DEFAULT value #18053

nbabenko commented Dec 27, 2024 •

edited

Loading

nbabenko commented Jan 6, 2025

reivilibre commented Jan 7, 2025 •

edited

Loading

foegra commented Jan 15, 2025

portdb from SQLite to Postgres fails if Sliding Sync has been used: sliding_sync_connections.connection_key: cannot insert a non-DEFAULT value #18053

portdb from SQLite to Postgres fails if Sliding Sync has been used: sliding_sync_connections.connection_key: cannot insert a non-DEFAULT value #18053

Comments

nbabenko commented Dec 27, 2024 • edited Loading

Description

Steps to reproduce

Workaround attempts:

Homeserver

Synapse Version

Installation Method

Database

Workers

Platform

Configuration

Relevant log output

Anything else that would be useful to know?

nbabenko commented Jan 6, 2025

reivilibre commented Jan 7, 2025 • edited Loading

foegra commented Jan 15, 2025

portdb from SQLite to Postgres fails if Sliding Sync has been used: `sliding_sync_connections.connection_key`: cannot insert a non-DEFAULT value #18053

portdb from SQLite to Postgres fails if Sliding Sync has been used: `sliding_sync_connections.connection_key`: cannot insert a non-DEFAULT value #18053

nbabenko commented Dec 27, 2024 •

edited

Loading

reivilibre commented Jan 7, 2025 •

edited

Loading