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

Startup Crash psycopg2.errors.DuplicateTable: relation "background_updates" already exists #16286

Closed
Y0ngg4n opened this issue Sep 9, 2023 · 18 comments
Closed

Startup Crash psycopg2.errors.DuplicateTable: relation "background_updates" already exists #16286

Y0ngg4n opened this issue Sep 9, 2023 · 18 comments
Labels
X-Needs-Info This issue is blocked awaiting information from the reporter

Comments

@Y0ngg4n
Copy link

Y0ngg4n commented Sep 9, 2023

Description

So i restarted my synapse container and it somehow throws this error now.
If i delete the table and restart it it recreates the table, but throws the error again. I did not change anything on configuration side. There was also a discussion on matrix:

https://matrix.to/#/!ehXvUhWNASUkSLvAGP:matrix.org/$cCeo3dJyaLOVDruxDQ85KtLf8Pf5HWl6Y9XC6O8MDUs?via=matrix.org&via=libera.chat&via=matrix.breakpointingbad.com

Steps to reproduce

Hard to reproduce.
Restart container.

Homeserver

https://hs.obco.pro

Synapse Version

v1.91.2

Installation Method

Other (please mention below)

Database

postgresql with Kubegres, single database, not ported, not restored

Workers

Single process

Platform

Kubernetes with Kubegres, the deployment is mentioned in the chat and below.

Configuration

apiVersion: apps/v1
kind: Deployment
metadata:
  name: synapse
  namespace: matrix
spec:
  replicas: 1
  selector:
    matchLabels:
      app: synapse
  template:
    metadata:
      labels:
        app: synapse
    spec:
      containers:
        - image: ghcr.io/matrix-org/synapse:v1.91.2 # {"$imagepolicy": "flux-system:synapse"}
          imagePullSecrets:
            - name: image-pull-secret
          imagePullPolicy: IfNotPresent
          name: synapse
          ports:
            - containerPort: 8008
              protocol: TCP
          volumeMounts:
            - mountPath: /data
              name: synapse-config
          livenessProbe:
            httpGet:
              path: /health
              port: 8008
            initialDelaySeconds: 20
            periodSeconds: 10
            failureThreshold: 10
      volumes:
        - name: synapse-config
          persistentVolumeClaim:
            claimName: synapse-config

Relevant log output

Starting synapse with args -m synapse.app.homeserver --config-path /data/homeserver.yaml
This server is configured to use 'matrix.org' as its trusted key server via the
'trusted_key_servers' config option. 'matrix.org' is a good choice for a key
server since it is long-lived, stable and trusted. However, some admins may
wish to use another server for this purpose.

To suppress this warning and continue using 'matrix.org', admins should set
'suppress_key_server_warning' to 'true' in homeserver.yaml.
--------------------------------------------------------------------------------
2023-09-09 14:17:46,202 - root - 348 - WARNING - main - ***** STARTING SERVER *****
2023-09-09 14:17:46,209 - root - 349 - WARNING - main - Server /usr/local/lib/python3.11/site-packages/synapse/app/homeserver.py version 1.91.2
2023-09-09 14:17:46,209 - root - 354 - INFO - main - Server hostname: obco.pro
2023-09-09 14:17:46,209 - root - 355 - INFO - main - Instance name: master
2023-09-09 14:17:46,210 - root - 356 - INFO - main - Twisted reactor: EPollReactor
2023-09-09 14:17:46,210 - synapse.app.homeserver - 350 - INFO - main - Setting up server
2023-09-09 14:17:46,210 - synapse.server - 338 - INFO - main - Setting up.
2023-09-09 14:17:46,220 - synapse.storage.databases - 66 - INFO - main - [database config 'master']: Checking database server
2023-09-09 14:17:46,222 - synapse.storage.databases - 69 - INFO - main - [database config 'master']: Preparing for databases ['main', 'state']
2023-09-09 14:17:46,222 - synapse.storage.prepare_database - 128 - INFO - main - ['main', 'state']: Checking existing schema version
2023-09-09 14:17:46,223 - synapse.storage.prepare_database - 158 - INFO - main - ['main', 'state']: Initialising new database
2023-09-09 14:17:46,224 - synapse.app._base - 215 - ERROR - main - Exception during startup
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/synapse/app/homeserver.py", line 353, in setup
    hs.setup()
  File "/usr/local/lib/python3.11/site-packages/synapse/server.py", line 340, in setup
    self.datastores = Databases(self.DATASTORE_CLASS, self)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/databases/__init__.py", line 74, in __init__
    prepare_database(
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/prepare_database.py", line 165, in prepare_database
    _setup_new_database(cur, database_engine, databases=databases)
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/prepare_database.py", line 284, in _setup_new_database
    database_engine.execute_script_file(cur, entry.absolute_path)
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/engines/_base.py", line 149, in execute_script_file
    cls.executescript(cursor, f.read())
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/engines/postgres.py", line 241, in executescript
    cursor.execute(f"COMMIT; BEGIN TRANSACTION; {script}")
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 417, in execute
    self._do_execute(self.txn.execute, sql, parameters)
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 469, in _do_execute
    return func(sql, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.DuplicateTable: relation "background_updates" already exists

**********************************************************************************
 Error during initialisation:
     Traceback (most recent call last):
       File "/usr/local/lib/python3.11/site-packages/synapse/app/homeserver.py", line 353, in setup
         hs.setup()
       File "/usr/local/lib/python3.11/site-packages/synapse/server.py", line 340, in setup
         self.datastores = Databases(self.DATASTORE_CLASS, self)
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
       File "/usr/local/lib/python3.11/site-packages/synapse/storage/databases/__init__.py", line 74, in __init__
         prepare_database(
       File "/usr/local/lib/python3.11/site-packages/synapse/storage/prepare_database.py", line 165, in prepare_database
         _setup_new_database(cur, database_engine, databases=databases)
       File "/usr/local/lib/python3.11/site-packages/synapse/storage/prepare_database.py", line 284, in _setup_new_database
         database_engine.execute_script_file(cur, entry.absolute_path)
       File "/usr/local/lib/python3.11/site-packages/synapse/storage/engines/_base.py", line 149, in execute_script_file
         cls.executescript(cursor, f.read())
       File "/usr/local/lib/python3.11/site-packages/synapse/storage/engines/postgres.py", line 241, in executescript
         cursor.execute(f"COMMIT; BEGIN TRANSACTION; {script}")
       File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 417, in execute
         self._do_execute(self.txn.execute, sql, parameters)
       File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 469, in _do_execute
         return func(sql, *args, **kwargs)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^
     psycopg2.errors.DuplicateTable: relation "background_updates" already exists
 
 
 There may be more information in the logs.
**********************************************************************************

Anything else that would be useful to know?

No response
@clokep
Copy link
Member

clokep commented Sep 11, 2023

This code should only run if Synapse thinks you don't have a database setup, see here.

What does SELECT * FROM schema_version; return?

@clokep clokep added the X-Needs-Info This issue is blocked awaiting information from the reporter label Sep 11, 2023
@Y0ngg4n
Copy link
Author

Y0ngg4n commented Sep 11, 2023

@clokep is empty:

SELECT * FROM schema_version;
 lock | version | upgraded 
------+---------+----------
(0 rows)

@clokep
Copy link
Member

clokep commented Sep 11, 2023

Are the other expected tables there, e.g. does events exist?

I'm not sure what could have blanked schema_version. What does SELECT * FROM applied_schema_deltas; return?

@Y0ngg4n
Copy link
Author

Y0ngg4n commented Sep 11, 2023 •
edited by clokep
Loading

@clokep any idea how i can fix that?

SELECT * FROM applied_schema_deltas;
 version |                                    file                                    
---------+----------------------------------------------------------------------------
      55 | 55/access_token_expiry.sql
      55 | 55/track_threepid_validations.sql
      55 | 55/users_alter_deactivated.sql
      56 | 56/add_spans_to_device_lists.sql
      56 | 56/current_state_events_membership.sql
      56 | 56/current_state_events_membership_mk2.sql
      56 | 56/delete_keys_from_deleted_backups.sql
      56 | 56/destinations_failure_ts.sql
      56 | 56/destinations_retry_interval_type.sql.postgres
      56 | 56/device_stream_id_insert.sql
      56 | 56/devices_last_seen.sql
      56 | 56/drop_unused_event_tables.sql
      56 | 56/event_expiry.sql
      56 | 56/event_labels.sql
      56 | 56/event_labels_background_update.sql
      56 | 56/fix_room_keys_index.sql
      56 | 56/hidden_devices.sql
      56 | 56/nuke_empty_communities_from_db.sql
      56 | 56/public_room_list_idx.sql
      56 | 56/redaction_censor.sql
      56 | 56/redaction_censor2.sql
      56 | 56/redaction_censor3_fix_update.sql.postgres
      56 | 56/redaction_censor4.sql
      56 | 56/remove_tombstoned_rooms_from_directory.sql
      56 | 56/room_key_etag.sql
      56 | 56/room_membership_idx.sql
      56 | 56/room_retention.sql
      56 | 56/signing_keys.sql
      56 | 56/signing_keys_nonunique_signatures.sql
      56 | 56/state_group_room_idx.sql
      56 | 56/stats_separated.sql
      56 | 56/unique_user_filter_index.py
      56 | 56/user_external_ids.sql
      56 | 56/users_in_public_rooms_idx.sql
      57 | 57/delete_old_current_state_events.sql
      57 | 57/device_list_remote_cache_stale.sql
      57 | 57/local_current_membership.py
      57 | 57/remove_sent_outbound_pokes.sql
      57 | 57/rooms_version_column.sql
      57 | 57/rooms_version_column_2.sql.postgres
      57 | 57/rooms_version_column_3.sql.postgres
      58 | 58/00background_update_ordering.sql
      58 | 58/02remove_dup_outbound_pokes.sql
      58 | 58/03persist_ui_auth.sql
      58 | 58/05cache_instance.sql.postgres
      58 | 58/06dlols_unique_idx.py
      58 | 58/07add_method_to_thumbnail_constraint.sql.postgres
      58 | 58/07persist_ui_auth_ips.sql
      58 | 58/08_media_safe_from_quarantine.sql.postgres
      58 | 58/09shadow_ban.sql
      58 | 58/10_pushrules_enabled_delete_obsolete.sql
      58 | 58/10drop_local_rejections_stream.sql

...skipping 1 line
      58 | 58/11dehydration.sql
      58 | 58/11fallback.sql
      58 | 58/11user_id_seq.py
      58 | 58/12room_stats.sql
      58 | 58/13remove_presence_allow_inbound.sql
      58 | 58/14events_instance_name.sql
      58 | 58/14events_instance_name.sql.postgres
      58 | 58/15_catchup_destination_rooms.sql
      58 | 58/15unread_count.sql
      58 | 58/16populate_stats_process_rooms_fix.sql
      58 | 58/17_catchup_last_successful.sql
      58 | 58/18stream_positions.sql
      58 | 58/19instance_map.sql.postgres
      58 | 58/19txn_id.sql
      58 | 58/20instance_name_event_tables.sql
      58 | 58/20user_daily_visits.sql
      58 | 58/21as_device_stream.sql
      58 | 58/21drop_device_max_stream_id.sql
      58 | 58/22puppet_token.sql
      58 | 58/22users_have_local_media.sql
      58 | 58/23e2e_cross_signing_keys_idx.sql
      58 | 58/24drop_event_json_index.sql
      58 | 58/25user_external_ids_user_id_idx.sql
      58 | 58/26access_token_last_validated.sql
      58 | 58/27local_invites.sql
      58 | 58/28drop_last_used_column.sql.postgres
      59 | 59/01ignored_user.py
      59 | 59/02shard_send_to_device.sql
      59 | 59/03shard_send_to_device_sequence.sql.postgres
      59 | 59/04_event_auth_chains.sql
      59 | 59/04_event_auth_chains.sql.postgres
      59 | 59/04drop_account_data.sql
      59 | 59/05cache_invalidation.sql
      59 | 59/06chain_cover_index.sql
      59 | 59/06shard_account_data.sql
      59 | 59/06shard_account_data.sql.postgres
      59 | 59/07shard_account_data_fix.sql
      59 | 59/08delete_pushers_for_deactivated_accounts.sql
      59 | 59/08delete_stale_pushers.sql
      59 | 59/09rejected_events_metadata.sql
      59 | 59/10delete_purged_chain_cover.sql
      59 | 59/11drop_thumbnail_constraint.sql.postgres
      59 | 59/12account_validity_token_used_ts_ms.sql
      59 | 59/12presence_stream_instance.sql
      59 | 59/12presence_stream_instance_seq.sql.postgres
      59 | 59/13users_to_send_full_presence_to.sql
      59 | 59/11add_knock_members_to_stats.sql
      59 | 59/15locks.sql
      59 | 59/16federation_inbound_staging.sql
      59 | 59/14refresh_tokens.sql
      60 | 60/01recreate_stream_ordering.sql.postgres
      60 | 60/02change_stream_ordering_columns.sql.postgres
      61 | 61/01change_appservices_txns.sql.postgres
      61 | 61/02drop_redundant_room_depth_index.sql

...skipping 1 line
      61 | 61/03recreate_min_depth.py
      61 | 61/01insertion_event_lookups.sql
      62 | 62/01insertion_event_extremities.sql
      63 | 63/01create_registration_tokens.sql
      63 | 63/02delete_unlinked_email_pushers.sql
      63 | 63/02populate-rooms-creator.sql
      63 | 63/03session_store.sql
      63 | 63/04add_presence_stream_not_offline_index.sql
      64 | 64/01msc2716_chunk_to_batch_rename.sql.postgres
      65 | 65/01msc2716_insertion_event_edges.sql
      65 | 65/02_thread_relations.sql
      65 | 65/03remove_hidden_devices_from_device_inbox.sql
      65 | 65/04_local_group_updates.sql
      65 | 65/06remove_deleted_devices_from_device_inbox.sql
      65 | 65/05_remove_room_stats_historical_and_user_stats_historical.sql
      65 | 65/07_arbitrary_relations.sql
      65 | 65/08_device_inbox_background_updates.sql
      65 | 65/10_expirable_refresh_tokens.sql
      65 | 65/11_devices_auth_provider_session.sql
      67 | 67/01drop_public_room_list_stream.sql
      68 | 68/01event_columns.sql
      68 | 68/02_msc2409_add_device_id_appservice_stream_type.sql
      68 | 68/03_delete_account_data_for_deactivated_accounts.sql
      68 | 68/04_refresh_tokens_index_next_token_id.sql
      68 | 68/04partial_state_rooms.sql
      68 | 68/05partial_state_rooms_triggers.py
      68 | 68/06_msc3202_add_device_list_appservice_stream_type.sql
      69 | 69/01as_txn_seq.py
      69 | 69/01device_list_oubound_by_room.sql
      69 | 69/02cache_invalidation_index.sql
      70 | 70/08_state_group_edges_unique.sql
      70 | 70/01clean_table_purged_rooms.sql
      71 | 71/01rebuild_event_edges.sql.postgres
      71 | 71/01remove_noop_background_updates.sql
      71 | 71/02event_push_summary_unique.sql
      72 | 72/01event_push_summary_receipt.sql
      72 | 72/02event_push_actions_index.sql
      72 | 72/01add_room_type_to_state_stats.sql
      72 | 72/03bg_populate_events_columns.py
      72 | 72/03drop_event_reference_hashes.sql
      72 | 72/03remove_groups.sql
      72 | 72/04drop_column_application_services_state_last_txn.sql.postgres
      72 | 72/05remove_unstable_private_read_receipts.sql
      72 | 72/05receipts_event_stream_ordering.sql
      72 | 72/06add_consent_ts_to_users.sql
      72 | 72/06thread_notifications.sql
      72 | 72/07force_update_current_state_events_membership.py
      72 | 72/07thread_receipts.sql.postgres
      72 | 72/08begin_cache_invalidation_seq_at_2.sql.postgres
      72 | 72/08thread_receipts.sql
      73 | 73/01event_failed_pull_attempts.sql
      73 | 73/02room_id_indexes_for_purging.sql
      73 | 73/02add_pusher_enabled.sql
      73 | 73/03pusher_device_id.sql

...skipping 1 line
      73 | 73/04partial_join_details.sql
      73 | 73/04pending_device_list_updates.sql
      73 | 73/05old_push_actions.sql.postgres
      73 | 73/06thread_notifications_thread_id_idx.sql
      73 | 73/08thread_receipts_non_null.sql.postgres
      73 | 73/09partial_joined_via_destination.sql
      73 | 73/09threads_table.sql
      73 | 73/10_update_sqlite_fts4_tokenizer.py
      73 | 73/10login_tokens.sql
      73 | 73/11event_search_room_id_n_distinct.sql.postgres
      73 | 73/12refactor_device_list_outbound_pokes.sql
      73 | 73/13add_device_lists_index.sql
      73 | 73/20_un_partial_stated_room_stream.sql
      73 | 73/21_un_partial_stated_room_stream_seq.sql.postgres
      73 | 73/22_rebuild_user_dir_stats.sql
      73 | 73/22_un_partial_stated_event_stream.sql
      73 | 73/23_fix_thread_index.sql
      73 | 73/23_un_partial_stated_room_stream_seq.sql.postgres
      73 | 73/24_events_jump_to_date_index.sql
      73 | 73/25drop_presence.sql
      74 | 74/01_user_directory_stale_remote_users.sql
      74 | 74/90COMMENTS_destinations.sql.postgres
      74 | 74/02_set_device_id_for_pushers_bg_update.sql
      74 | 74/03_room_membership_index.sql
      74 | 74/04_delete_e2e_backup_keys_for_deactivated_users.sql
      74 | 74/03_membership_tables_event_stream_ordering.sql.postgres
      74 | 74/04_membership_tables_event_stream_ordering_triggers.py
      74 | 74/05_events_txn_id_device_id.sql
      76 | 76/01_add_profiles_full_user_id_column.sql
      76 | 76/02_add_user_filters_full_user_id_column.sql
      76 | 76/03_per_user_experimental_features.sql
      76 | 76/04_add_room_forgetter.sql
      77 | 77/01_add_profiles_not_valid_check.sql.postgres
      77 | 77/02_add_user_filters_not_valid_check.sql.postgres
      77 | 77/03bg_populate_full_user_id_profiles.sql
      77 | 77/04bg_populate_full_user_id_user_filters.sql
      77 | 77/05thread_notifications_backfill.sql
      77 | 77/06thread_notifications_not_null_event_push_actions.sql.postgres
      77 | 77/06thread_notifications_not_null_event_push_actions_staging.sql.postgres
      77 | 77/06thread_notifications_not_null_event_push_summary.sql.postgres
      77 | 77/14bg_indices_event_stream_ordering.sql
      78 | 78/01_validate_and_update_profiles.py
      78 | 78/02_validate_and_update_user_filters.py
      78 | 78/03_remove_unused_indexes_user_filters.py
      78 | 78/04_add_full_user_id_index_user_filters.py
      78 | 78/03event_extremities_constraints.py
      78 | 78/04_read_write_locks_triggers.sql.postgres
      79 | 79/03_read_write_locks_triggers.sql.postgres
      79 | 79/04_mitigate_stream_ordering_update_race.py
      79 | 79/05_read_write_locks_triggers.sql.postgres
      80 | 80/01_users_alter_locked.sql
      80 | 80/02_read_write_locks_unlogged.sql.postgres
      80 | 80/02_scheduled_tasks.sql
      80 | 80/03_read_write_locks_triggers.sql.postgres

...skipping 1 line
      55 | 55/access_token_expiry.sql
      55 | 55/track_threepid_validations.sql
      55 | 55/users_alter_deactivated.sql
      56 | 56/add_spans_to_device_lists.sql
      56 | 56/current_state_events_membership.sql
      56 | 56/current_state_events_membership_mk2.sql
      56 | 56/delete_keys_from_deleted_backups.sql
      56 | 56/destinations_failure_ts.sql
      56 | 56/destinations_retry_interval_type.sql.postgres
      56 | 56/device_stream_id_insert.sql
      56 | 56/devices_last_seen.sql
      56 | 56/drop_unused_event_tables.sql
      56 | 56/event_expiry.sql
      56 | 56/event_labels.sql
      56 | 56/event_labels_background_update.sql
      56 | 56/fix_room_keys_index.sql
      56 | 56/hidden_devices.sql
      56 | 56/nuke_empty_communities_from_db.sql
      56 | 56/public_room_list_idx.sql
      56 | 56/redaction_censor.sql
      56 | 56/redaction_censor2.sql
      56 | 56/redaction_censor3_fix_update.sql.postgres
      56 | 56/redaction_censor4.sql
      56 | 56/remove_tombstoned_rooms_from_directory.sql
      56 | 56/room_key_etag.sql
      56 | 56/room_membership_idx.sql
      56 | 56/room_retention.sql
      56 | 56/signing_keys.sql
      56 | 56/signing_keys_nonunique_signatures.sql
      56 | 56/state_group_room_idx.sql
      56 | 56/stats_separated.sql
      56 | 56/unique_user_filter_index.py
      56 | 56/user_external_ids.sql
      56 | 56/users_in_public_rooms_idx.sql
      57 | 57/delete_old_current_state_events.sql
      57 | 57/device_list_remote_cache_stale.sql
      57 | 57/local_current_membership.py
      57 | 57/remove_sent_outbound_pokes.sql
      57 | 57/rooms_version_column.sql
      57 | 57/rooms_version_column_2.sql.postgres
      57 | 57/rooms_version_column_3.sql.postgres
      58 | 58/00background_update_ordering.sql
      58 | 58/02remove_dup_outbound_pokes.sql
      58 | 58/03persist_ui_auth.sql
      58 | 58/05cache_instance.sql.postgres
      58 | 58/06dlols_unique_idx.py
      58 | 58/07add_method_to_thumbnail_constraint.sql.postgres
      58 | 58/07persist_ui_auth_ips.sql
      58 | 58/08_media_safe_from_quarantine.sql.postgres
      58 | 58/09shadow_ban.sql
      58 | 58/10_pushrules_enabled_delete_obsolete.sql
      58 | 58/10drop_local_rejections_stream.sql
      58 | 58/10federation_pos_instance_name.sql
      58 | 58/11dehydration.sql

...skipping 1 line
      58 | 58/11user_id_seq.py
      58 | 58/12room_stats.sql
      58 | 58/13remove_presence_allow_inbound.sql
      58 | 58/14events_instance_name.sql
      58 | 58/14events_instance_name.sql.postgres
      58 | 58/15_catchup_destination_rooms.sql
      58 | 58/15unread_count.sql
      58 | 58/16populate_stats_process_rooms_fix.sql
      58 | 58/17_catchup_last_successful.sql
      58 | 58/18stream_positions.sql
      58 | 58/19instance_map.sql.postgres
      58 | 58/19txn_id.sql
      58 | 58/20instance_name_event_tables.sql
      58 | 58/20user_daily_visits.sql
      58 | 58/21as_device_stream.sql
      58 | 58/21drop_device_max_stream_id.sql
      58 | 58/22puppet_token.sql
      58 | 58/22users_have_local_media.sql
      58 | 58/23e2e_cross_signing_keys_idx.sql
      58 | 58/24drop_event_json_index.sql
      58 | 58/25user_external_ids_user_id_idx.sql
      58 | 58/26access_token_last_validated.sql
      58 | 58/27local_invites.sql
      58 | 58/28drop_last_used_column.sql.postgres
      59 | 59/01ignored_user.py
      59 | 59/02shard_send_to_device.sql
      59 | 59/03shard_send_to_device_sequence.sql.postgres
      59 | 59/04_event_auth_chains.sql
      59 | 59/04_event_auth_chains.sql.postgres
      59 | 59/04drop_account_data.sql
      59 | 59/05cache_invalidation.sql
      59 | 59/06chain_cover_index.sql
      59 | 59/06shard_account_data.sql
      59 | 59/06shard_account_data.sql.postgres
      59 | 59/07shard_account_data_fix.sql
      59 | 59/08delete_pushers_for_deactivated_accounts.sql
      59 | 59/08delete_stale_pushers.sql
      59 | 59/09rejected_events_metadata.sql
      59 | 59/10delete_purged_chain_cover.sql
      59 | 59/11drop_thumbnail_constraint.sql.postgres
      59 | 59/12account_validity_token_used_ts_ms.sql
      59 | 59/12presence_stream_instance.sql
      59 | 59/12presence_stream_instance_seq.sql.postgres
      59 | 59/13users_to_send_full_presence_to.sql
      59 | 59/11add_knock_members_to_stats.sql
      59 | 59/15locks.sql
      59 | 59/16federation_inbound_staging.sql
      59 | 59/14refresh_tokens.sql
      60 | 60/01recreate_stream_ordering.sql.postgres
      60 | 60/02change_stream_ordering_columns.sql.postgres
      61 | 61/01change_appservices_txns.sql.postgres
      61 | 61/02drop_redundant_room_depth_index.sql
      61 | 61/02state_groups_state_n_distinct.sql.postgres
      61 | 61/03recreate_min_depth.py

...skipping 1 line
      62 | 62/01insertion_event_extremities.sql
      63 | 63/01create_registration_tokens.sql
      63 | 63/02delete_unlinked_email_pushers.sql
      63 | 63/02populate-rooms-creator.sql
      63 | 63/03session_store.sql
      63 | 63/04add_presence_stream_not_offline_index.sql
      64 | 64/01msc2716_chunk_to_batch_rename.sql.postgres
      65 | 65/01msc2716_insertion_event_edges.sql
      65 | 65/02_thread_relations.sql
      65 | 65/03remove_hidden_devices_from_device_inbox.sql
      65 | 65/04_local_group_updates.sql
      65 | 65/06remove_deleted_devices_from_device_inbox.sql
      65 | 65/05_remove_room_stats_historical_and_user_stats_historical.sql
      65 | 65/07_arbitrary_relations.sql
      65 | 65/08_device_inbox_background_updates.sql
      65 | 65/10_expirable_refresh_tokens.sql
      65 | 65/11_devices_auth_provider_session.sql
      67 | 67/01drop_public_room_list_stream.sql
      68 | 68/01event_columns.sql
      68 | 68/02_msc2409_add_device_id_appservice_stream_type.sql
      68 | 68/03_delete_account_data_for_deactivated_accounts.sql
      68 | 68/04_refresh_tokens_index_next_token_id.sql
      68 | 68/04partial_state_rooms.sql
      68 | 68/05partial_state_rooms_triggers.py
      68 | 68/06_msc3202_add_device_list_appservice_stream_type.sql
      69 | 69/01as_txn_seq.py
      69 | 69/01device_list_oubound_by_room.sql
      69 | 69/02cache_invalidation_index.sql
      70 | 70/08_state_group_edges_unique.sql
      70 | 70/01clean_table_purged_rooms.sql
      71 | 71/01rebuild_event_edges.sql.postgres
      71 | 71/01remove_noop_background_updates.sql
      71 | 71/02event_push_summary_unique.sql
      72 | 72/01event_push_summary_receipt.sql
      72 | 72/02event_push_actions_index.sql
      72 | 72/01add_room_type_to_state_stats.sql
      72 | 72/03bg_populate_events_columns.py
      72 | 72/03drop_event_reference_hashes.sql
      72 | 72/03remove_groups.sql
      72 | 72/04drop_column_application_services_state_last_txn.sql.postgres
      72 | 72/05remove_unstable_private_read_receipts.sql
      72 | 72/05receipts_event_stream_ordering.sql
      72 | 72/06add_consent_ts_to_users.sql
      72 | 72/06thread_notifications.sql
      72 | 72/07force_update_current_state_events_membership.py
      72 | 72/07thread_receipts.sql.postgres
      72 | 72/08begin_cache_invalidation_seq_at_2.sql.postgres
      72 | 72/08thread_receipts.sql
      73 | 73/01event_failed_pull_attempts.sql
      73 | 73/02room_id_indexes_for_purging.sql
      73 | 73/02add_pusher_enabled.sql
      73 | 73/03pusher_device_id.sql
      73 | 73/03users_approved_column.sql
      73 | 73/04partial_join_details.sql

...skipping 1 line
      73 | 73/05old_push_actions.sql.postgres
      73 | 73/06thread_notifications_thread_id_idx.sql
      73 | 73/08thread_receipts_non_null.sql.postgres
      73 | 73/09partial_joined_via_destination.sql
      73 | 73/09threads_table.sql
      73 | 73/10_update_sqlite_fts4_tokenizer.py
      73 | 73/10login_tokens.sql
      73 | 73/11event_search_room_id_n_distinct.sql.postgres
      73 | 73/12refactor_device_list_outbound_pokes.sql
      73 | 73/13add_device_lists_index.sql
      73 | 73/20_un_partial_stated_room_stream.sql
      73 | 73/21_un_partial_stated_room_stream_seq.sql.postgres
      73 | 73/22_rebuild_user_dir_stats.sql
      73 | 73/22_un_partial_stated_event_stream.sql
      73 | 73/23_fix_thread_index.sql
      73 | 73/23_un_partial_stated_room_stream_seq.sql.postgres
      73 | 73/24_events_jump_to_date_index.sql
      73 | 73/25drop_presence.sql
      74 | 74/01_user_directory_stale_remote_users.sql
      74 | 74/90COMMENTS_destinations.sql.postgres
      74 | 74/02_set_device_id_for_pushers_bg_update.sql
      74 | 74/03_room_membership_index.sql
      74 | 74/04_delete_e2e_backup_keys_for_deactivated_users.sql
      74 | 74/03_membership_tables_event_stream_ordering.sql.postgres
      74 | 74/04_membership_tables_event_stream_ordering_triggers.py
      74 | 74/05_events_txn_id_device_id.sql
      76 | 76/01_add_profiles_full_user_id_column.sql
      76 | 76/02_add_user_filters_full_user_id_column.sql
      76 | 76/03_per_user_experimental_features.sql
      76 | 76/04_add_room_forgetter.sql
      77 | 77/01_add_profiles_not_valid_check.sql.postgres
      77 | 77/02_add_user_filters_not_valid_check.sql.postgres
      77 | 77/03bg_populate_full_user_id_profiles.sql
      77 | 77/04bg_populate_full_user_id_user_filters.sql
      77 | 77/05thread_notifications_backfill.sql
      77 | 77/06thread_notifications_not_null_event_push_actions.sql.postgres
      77 | 77/06thread_notifications_not_null_event_push_actions_staging.sql.postgres
      77 | 77/06thread_notifications_not_null_event_push_summary.sql.postgres
      77 | 77/14bg_indices_event_stream_ordering.sql
      78 | 78/01_validate_and_update_profiles.py
      78 | 78/02_validate_and_update_user_filters.py
      78 | 78/03_remove_unused_indexes_user_filters.py
      78 | 78/04_add_full_user_id_index_user_filters.py
      78 | 78/03event_extremities_constraints.py
      78 | 78/04_read_write_locks_triggers.sql.postgres
      79 | 79/03_read_write_locks_triggers.sql.postgres
      79 | 79/04_mitigate_stream_ordering_update_race.py
      79 | 79/05_read_write_locks_triggers.sql.postgres
      80 | 80/01_users_alter_locked.sql
      80 | 80/02_read_write_locks_unlogged.sql.postgres
      80 | 80/02_scheduled_tasks.sql
      80 | 80/03_read_write_locks_triggers.sql.postgres
      80 | 80/04_read_write_locks_deadlock.sql.postgres
(436 rows)

...skipping 1 line

Yes events exists:

\dt
                               List of relations
 Schema |                      Name                      | Type  |    Owner     
--------+------------------------------------------------+-------+--------------
 public | access_tokens                                  | table | synapse_user
 public | account_data                                   | table | synapse_user
 public | account_validity                               | table | synapse_user
 public | application_services_state                     | table | synapse_user
 public | application_services_txns                      | table | synapse_user
 public | applied_module_schemas                         | table | synapse_user
 public | applied_schema_deltas                          | table | synapse_user
 public | appservice_room_list                           | table | synapse_user
 public | appservice_stream_position                     | table | synapse_user
 public | background_updates                             | table | synapse_user
 public | batch_events                                   | table | synapse_user
 public | blocked_rooms                                  | table | synapse_user
 public | cache_invalidation_stream_by_instance          | table | synapse_user
 public | current_state_delta_stream                     | table | synapse_user
 public | current_state_events                           | table | synapse_user
 public | dehydrated_devices                             | table | synapse_user
 public | deleted_pushers                                | table | synapse_user
 public | destination_rooms                              | table | synapse_user
 public | destinations                                   | table | synapse_user
 public | device_auth_providers                          | table | synapse_user
 public | device_federation_inbox                        | table | synapse_user
 public | device_federation_outbox                       | table | synapse_user
 public | device_inbox                                   | table | synapse_user
 public | device_lists_changes_converted_stream_position | table | synapse_user
 public | device_lists_changes_in_room                   | table | synapse_user
 public | device_lists_outbound_last_success             | table | synapse_user
 public | device_lists_outbound_pokes                    | table | synapse_user
 public | device_lists_remote_cache                      | table | synapse_user
 public | device_lists_remote_extremeties                | table | synapse_user
 public | device_lists_remote_pending                    | table | synapse_user
 public | device_lists_remote_resync                     | table | synapse_user
 public | device_lists_stream                            | table | synapse_user
 public | devices                                        | table | synapse_user
 public | e2e_cross_signing_keys                         | table | synapse_user
 public | e2e_cross_signing_signatures                   | table | synapse_user
 public | e2e_device_keys_json                           | table | synapse_user
 public | e2e_fallback_keys_json                         | table | synapse_user
 public | e2e_one_time_keys_json                         | table | synapse_user
 public | e2e_room_keys                                  | table | synapse_user
 public | e2e_room_keys_versions                         | table | synapse_user
 public | erased_users                                   | table | synapse_user
 public | event_auth                                     | table | synapse_user
 public | event_auth_chain_links                         | table | synapse_user
 public | event_auth_chain_to_calculate                  | table | synapse_user
 public | event_auth_chains                              | table | synapse_user
 public | event_backward_extremities                     | table | synapse_user
 public | event_edges                                    | table | synapse_user
 public | event_expiry                                   | table | synapse_user
 public | event_failed_pull_attempts                     | table | synapse_user
 public | event_forward_extremities                      | table | synapse_user
 public | event_json                                     | table | synapse_user

...skipping 1 line
 public | event_push_actions                             | table | synapse_user
 public | event_push_actions_staging                     | table | synapse_user
 public | event_push_summary                             | table | synapse_user
 public | event_push_summary_last_receipt_stream_id      | table | synapse_user
 public | event_push_summary_stream_ordering             | table | synapse_user
 public | event_relations                                | table | synapse_user
 public | event_reports                                  | table | synapse_user
 public | event_search                                   | table | synapse_user
 public | event_to_state_groups                          | table | synapse_user
 public | event_txn_id                                   | table | synapse_user
 public | event_txn_id_device_id                         | table | synapse_user
 public | events                                         | table | synapse_user
 public | ex_outlier_stream                              | table | synapse_user
 public | federation_inbound_events_staging              | table | synapse_user
 public | federation_stream_position                     | table | synapse_user
 public | ignored_users                                  | table | synapse_user
 public | insertion_event_edges                          | table | synapse_user
 public | insertion_event_extremities                    | table | synapse_user
 public | insertion_events                               | table | synapse_user
 public | instance_map                                   | table | synapse_user
 public | local_current_membership                       | table | synapse_user
 public | local_media_repository                         | table | synapse_user
 public | local_media_repository_thumbnails              | table | synapse_user
 public | local_media_repository_url_cache               | table | synapse_user
 public | login_tokens                                   | table | synapse_user
 public | monthly_active_users                           | table | synapse_user
 public | open_id_tokens                                 | table | synapse_user
 public | partial_state_events                           | table | synapse_user
 public | partial_state_rooms                            | table | synapse_user
 public | partial_state_rooms_servers                    | table | synapse_user
 public | per_user_experimental_features                 | table | synapse_user
 public | presence_stream                                | table | synapse_user
 public | profiles                                       | table | synapse_user
 public | push_rules                                     | table | synapse_user
 public | push_rules_enable                              | table | synapse_user
 public | push_rules_stream                              | table | synapse_user
 public | pusher_throttle                                | table | synapse_user
 public | pushers                                        | table | synapse_user
 public | ratelimit_override                             | table | synapse_user
 public | receipts_graph                                 | table | synapse_user
 public | receipts_linearized                            | table | synapse_user
 public | received_transactions                          | table | synapse_user
 public | redactions                                     | table | synapse_user
 public | refresh_tokens                                 | table | synapse_user
 public | registration_tokens                            | table | synapse_user
 public | rejections                                     | table | synapse_user
 public | remote_media_cache                             | table | synapse_user
 public | remote_media_cache_thumbnails                  | table | synapse_user
 public | room_account_data                              | table | synapse_user
 public | room_alias_servers                             | table | synapse_user
 public | room_aliases                                   | table | synapse_user
 public | room_depth                                     | table | synapse_user
 public | room_forgetter_stream_pos                      | table | synapse_user
 public | room_memberships                               | table | synapse_user

...skipping 1 line
 public | room_stats_current                             | table | synapse_user
 public | room_stats_earliest_token                      | table | synapse_user
 public | room_stats_state                               | table | synapse_user
 public | room_tags                                      | table | synapse_user
 public | room_tags_revisions                            | table | synapse_user
 public | rooms                                          | table | synapse_user
 public | scheduled_tasks                                | table | synapse_user
 public | schema_compat_version                          | table | synapse_user
 public | schema_version                                 | table | synapse_user
 public | server_keys_json                               | table | synapse_user
 public | server_signature_keys                          | table | synapse_user
 public | sessions                                       | table | synapse_user
 public | state_events                                   | table | synapse_user
 public | state_group_edges                              | table | synapse_user
 public | state_groups                                   | table | synapse_user
 public | state_groups_state                             | table | synapse_user
 public | stats_incremental_position                     | table | synapse_user
 public | stream_ordering_to_exterm                      | table | synapse_user
 public | stream_positions                               | table | synapse_user
 public | threads                                        | table | synapse_user
 public | threepid_guest_access_tokens                   | table | synapse_user
 public | threepid_validation_session                    | table | synapse_user
 public | threepid_validation_token                      | table | synapse_user
 public | ui_auth_sessions                               | table | synapse_user
 public | ui_auth_sessions_credentials                   | table | synapse_user
 public | ui_auth_sessions_ips                           | table | synapse_user
 public | un_partial_stated_event_stream                 | table | synapse_user
 public | un_partial_stated_room_stream                  | table | synapse_user
 public | user_daily_visits                              | table | synapse_user
 public | user_directory                                 | table | synapse_user
 public | user_directory_search                          | table | synapse_user
 public | user_directory_stale_remote_users              | table | synapse_user
 public | user_directory_stream_pos                      | table | synapse_user
 public | user_external_ids                              | table | synapse_user
 public | user_filters                                   | table | synapse_user
 public | user_ips                                       | table | synapse_user
 public | user_signature_stream                          | table | synapse_user
 public | user_stats_current                             | table | synapse_user
 public | user_threepid_id_server                        | table | synapse_user
 public | user_threepids                                 | table | synapse_user
 public | users                                          | table | synapse_user
 public | users_in_public_rooms                          | table | synapse_user
 public | users_pending_deactivation                     | table | synapse_user
 public | users_to_send_full_presence_to                 | table | synapse_user
 public | users_who_share_private_rooms                  | table | synapse_user
 public | worker_locks                                   | table | synapse_user
 public | worker_read_write_locks                        | table | synapse_user
 public | worker_read_write_locks_mode                   | table | synapse_user
(155 rows)

@aukfood
Copy link

aukfood commented Sep 22, 2023

Hello,

I have the same problem with a deployment with synapse and postgresql with docker swarm with a storage on glusterfs.
An idea ?

@Y0ngg4n
Copy link
Author

Y0ngg4n commented Sep 22, 2023

@aukfood do you have a backup? maybe try to insert the data from schema_versions manually from your backup into the table. It fixed that issue for me, but other issues spawned and i could not fix them so i had to start over.

@clokep
Copy link
Member

clokep commented Sep 22, 2023

I looked a bit into this and that table is updated in a transaction so I can't really see how it would get cleared out. Is it possible that multiple Synapse (main) containers started at the same time?

Note that deleting the table manually is likely just to break the database more. If you previously had v1.91.2 installed then the proper data for that table should be:

INSERT INTO schema_version (version, upgraded) VALUES (80, true);

@aukfood
Copy link

aukfood commented Sep 22, 2023

@Y0ngg4n @clokep is a new installation so perhaps there was a double write with swarm error on processing first start.

I'll try to remove all and retry to deploy.

@aukfood
Copy link

aukfood commented Sep 22, 2023

Idem with a new deploy :(

@clokep
Copy link
Member

clokep commented Sep 22, 2023

@Y0ngg4n @clokep is a new installation so perhaps there was a double write with swarm error on processing first start.

I'll try to remove all and retry to deploy.

This sounds not quite like the same issue then -- I'd really double check that only a single main Synapse process is running at once though. This is a recipe for database corruption and will not work.

@aukfood
Copy link

aukfood commented Sep 22, 2023

hum hum very strange, it's works with local storage but not with glusterfs storage ...

@Y0ngg4n
Copy link
Author

Y0ngg4n commented Sep 23, 2023

@aukfood are you using some kind of replication?

@aukfood
Copy link

aukfood commented Sep 25, 2023

@Y0ngg4n yes glusterfs storage replicated in 3 servers

@clokep
Copy link
Member

clokep commented Sep 25, 2023

@Y0ngg4n Are you also using some sort of storage replication? I can't quite figure out how kubegres works. If so, it sounds like there might be some sort of misconfiguration / incompatibility when using storage replication.

@aukfood
Copy link

aukfood commented Sep 26, 2023

@Y0ngg4n yes glusterfs is a replicated storage

@Y0ngg4n
Copy link
Author

Y0ngg4n commented Sep 27, 2023

@aukfood i meant database replication. @clokep yes i am using cephfs.

@clokep
Copy link
Member

clokep commented Sep 27, 2023

@aukfood It looks like glusterfs is known to have incompatibilies with PostgreSQL: gluster/glusterfs#2056

@Y0ngg4n I'm guessing something weird happened with your cephfs, it is probably better to use the PostgreSQL application-level replication instead of file system level replication in this case.

Regardless, neither of these feel like a bug in Synapse, but something broken in your file system.

@clokep clokep closed this as completed Sep 27, 2023
@Y0ngg4n
Copy link
Author

Y0ngg4n commented Sep 28, 2023

@clokep my postgresql has a ceph replication and streaming postgresql replication.
thanks for your support :)

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
X-Needs-Info This issue is blocked awaiting information from the reporter
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@clokep @Y0ngg4n @aukfood