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

Matrix DB "sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id" #15845

Closed
Ruaneri-Portela opened this issue Jun 28, 2023 · 1 comment
Closed

Matrix DB "sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id" #15845

Ruaneri-Portela opened this issue Jun 28, 2023 · 1 comment
Labels
A-Database DB stuff like queries, migrations, new/remove columns, indexes, unexpected entries in the db T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues.

Comments

@Ruaneri-Portela
Copy link

Ruaneri-Portela commented Jun 28, 2023 •
edited
Loading

Description

The following error appeared after an upgrade, I use Ubuntu 22.04 on AMD64 using the official repo, the error is relative to the database, although I am also a programmer I don't know the topology of the matrix code, I fixed the following error by commenting out execpt to bypass this error.

The error has been a non-existent table in the db, at least what I understood, after commenting out the snippet the server worked, however this doesn't seem quite right to me

I got it to work after editing this

        try:
            with opentracing.start_active_span(
                "db.query",
                tags={
                    opentracing.tags.DATABASE_TYPE: "sql",
                    opentracing.tags.DATABASE_STATEMENT: one_line_sql,
                },
            ):
                return func(sql, *args, **kwargs)
        except Exception as e:
            pass
  -->        #sql_logger.debug("[SQL FAIL] {%s} %s", self.name, e)
  --->      #raise
        finally:
            secs = time.time() - start
            sql_logger.debug("[SQL time] {%s} %f sec", self.name, secs)
            sql_query_timer.labels(sql.split()[0]).observe(secs)

Steps to reproduce

unknown

Synapse Version

{"server_version":"1.86.0","python_version":"3.10.6"}

Installation Method

Debian packages from packages.matrix.org

Database

SQLite

Workers

Single process

Platform

Ubuntu 22.04 AMD64 on Oracle VPN at AMD EPYC 7551 (2) @ 1.996GHz and 1GB of RAM

Configuration

pid_file: "/var/run/matrix-synapse.pid"
listeners:
  - port: 8008
    tls: false
    type: http
    x_forwarded: true
    bind_addresses: ['::1', '127.0.0.1']
    resources:
      - names: [client, federation]
        compress: yes
database:
  name: sqlite3
  args:
    database: /var/lib/matrix-synapse/homeserver.db
log_config: "/etc/matrix-synapse/log.yaml"
media_store_path: /var/lib/matrix-synapse/media
max_upload_size: 2147483648
signing_key_path: "/etc/matrix-synapse/homeserver.signing.key"
trusted_key_servers:
  - server_name: "matrix.org"
enable_registration: true
suppress_key_server_warning: true
enable_registration_without_verification: true
url_preview_enabled: true
url_preview_ip_range_blacklist:
  - '127.0.0.0/8'
  - '10.0.0.0/8'
  - '172.16.0.0/12'
  - '192.168.0.0/16'
  - '100.64.0.0/10'
  - '192.0.0.0/24'
  - '169.254.0.0/16'
  - '192.88.99.0/24'
  - '198.18.0.0/15'
  - '192.0.2.0/24'
  - '198.51.100.0/24'
  - '203.0.113.0/24'
  - '224.0.0.0/4'
  - '::1/128'
  - 'fe80::/10'
  - 'fc00::/7'
  - '2001:db8::/32'
  - 'ff00::/8'
  - 'fec0::/10'
turn_uris: [ "**?transport=udp", "turn:**?transport=tcp" ]
turn_shared_secret: "************************"
turn_user_lifetime: 86400000
turn_allow_guests: true

Relevant log output

2023-06-28 00:10:22,683 - root - 348 - WARNING - main - ***** STARTING SERVER *****
2023-06-28 00:10:22,683 - root - 349 - WARNING - main - Server /opt/venvs/matrix-synapse/lib/python3.10/site-packages/synapse/app/homeserver.py version 1.86.0
2023-06-28 00:10:22,683 - root - 354 - INFO - main - Server hostname: **
2023-06-28 00:10:22,683 - root - 355 - INFO - main - Instance name: master
2023-06-28 00:10:22,683 - root - 356 - INFO - main - Twisted reactor: EPollReactor
2023-06-28 00:10:22,683 - synapse.app.homeserver - 349 - INFO - main - Setting up server
2023-06-28 00:10:22,683 - synapse.server - 337 - INFO - main - Setting up.
2023-06-28 00:10:22,686 - synapse.storage.databases - 66 - INFO - main - [database config 'master']: Checking database server
2023-06-28 00:10:22,687 - synapse.storage.databases - 69 - INFO - main - [database config 'master']: Preparing for databases ['main', 'state']
2023-06-28 00:10:22,688 - synapse.storage.prepare_database - 120 - INFO - main - ['main', 'state']: Checking existing schema version
2023-06-28 00:10:22,688 - synapse.storage.prepare_database - 124 - INFO - main - ['main', 'state']: Existing schema is 77 (+4 deltas)
2023-06-28 00:10:22,689 - synapse.storage.databases.main - 288 - INFO - main - Checking database for consistency with configuration...
2023-06-28 00:10:22,689 - synapse.storage.prepare_database - 418 - INFO - main - Applying schema deltas for v77
2023-06-28 00:10:22,689 - synapse.storage.prepare_database - 526 - INFO - main - Applying schema 77/14bg_indices_event_stream_ordering.sql
2023-06-28 00:10:22,695 - synapse.storage.prepare_database - 418 - INFO - main - Applying schema deltas for v78
2023-06-28 00:10:22,696 - synapse.storage.prepare_database - 513 - INFO - main - Running 78/01_validate_and_update_profiles.py:run_upgrade
2023-06-28 00:10:22,768 - synapse.storage.prepare_database - 513 - INFO - main - Running 78/02_validate_and_update_user_filters.py:run_upgrade
2023-06-28 00:10:22,769 - synapse.app._base - 215 - ERROR - main - Exception during startup
Traceback (most recent call last):
  File "/opt/venvs/matrix-synapse/lib/python3.10/site-packages/synapse/app/homeserver.py", line 352, in setup
    hs.setup()
  File "/opt/venvs/matrix-synapse/lib/python3.10/site-packages/synapse/server.py", line 339, in setup
    self.datastores = Databases(self.DATASTORE_CLASS, self)
  File "/opt/venvs/matrix-synapse/lib/python3.10/site-packages/synapse/storage/databases/__init__.py", line 74, in __init__
    prepare_database(
  File "/opt/venvs/matrix-synapse/lib/python3.10/site-packages/synapse/storage/prepare_database.py", line 141, in prepare_database
    _upgrade_existing_database(
  File "/opt/venvs/matrix-synapse/lib/python3.10/site-packages/synapse/storage/prepare_database.py", line 514, in _upgrade_existing_database
    module.run_upgrade(cur, database_engine, config=config)
  File "/opt/venvs/matrix-synapse/lib/python3.10/site-packages/synapse/storage/schema/main/delta/78/02_validate_and_update_user_filters.py", line 85, in run_upgrade
    cur.execute(copy_sql, (f"{hostname}",))
  File "/opt/venvs/matrix-synapse/lib/python3.10/site-packages/synapse/storage/database.py", line 417, in execute
    self._do_execute(self.txn.execute, sql, parameters)
  File "/opt/venvs/matrix-synapse/lib/python3.10/site-packages/synapse/storage/database.py", line 469, in _do_execute
    return func(sql, *args, **kwargs)
sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id`

Anything else that would be useful to know?

No response
@MadLittleMods MadLittleMods added T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues. A-Database DB stuff like queries, migrations, new/remove columns, indexes, unexpected entries in the db labels Jun 28, 2023
@MadLittleMods
Copy link
Contributor

Duplicate of #15809

@MadLittleMods MadLittleMods marked this as a duplicate of #15809 Jun 28, 2023
@MadLittleMods MadLittleMods closed this as not planned Won't fix, can't repro, duplicate, stale Jun 28, 2023
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
A-Database DB stuff like queries, migrations, new/remove columns, indexes, unexpected entries in the db T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@MadLittleMods @Ruaneri-Portela