SQLITE3 error "UNIQUE constraint failed: temp_user_filters.user_id" after update to 1.86.0 #15809

brunofontes · 2023-06-20T23:19:48Z

Description

Hi,

I am using a Ubuntu server and had installed the matrix-synapse-py3. The update to version 1.86.0 appeared Today and I updated it as normal (via apt).

After the update the server was not getting back.

I've tried to rollback to version 1.71.0+focal1, but it complains the database has a newer version.

Steps to reproduce

sudo apt upgrade

After that, server did not started.

Homeserver

matrix.brunofontes.net

Synapse Version

1.86.0

Installation Method

Debian packages from packages.matrix.org

Database

SQLite

Workers

Single process

Platform

Linux mx 5.4.0-152-generic #169-Ubuntu SMP x86_64

Configuration

No response

Relevant log output

2023-06-20 19:44:04,875 - root - 348 - WARNING - main - ***** STARTING SERVER *****                                                                                                     
2023-06-20 19:44:04,875 - root - 349 - WARNING - main - Server /opt/venvs/matrix-synapse/lib/python3.8/site-packages/synapse/app/homeserver.py version 1.86.0                           
2023-06-20 19:44:04,875 - root - 354 - INFO - main - Server hostname: matrix.brunofontes.net                                                                                            
2023-06-20 19:44:04,876 - root - 355 - INFO - main - Instance name: master                                                                                                              
2023-06-20 19:44:04,876 - root - 356 - INFO - main - Twisted reactor: EPollReactor                                                                                                      
2023-06-20 19:44:04,876 - synapse.app.homeserver - 349 - INFO - main - Setting up server                                                                                                
2023-06-20 19:44:04,876 - synapse.server - 337 - INFO - main - Setting up.                                                                                                              
2023-06-20 19:44:04,880 - synapse.storage.databases - 66 - INFO - main - [database config 'master']: Checking database server                                                           
2023-06-20 19:44:04,880 - synapse.storage.databases - 69 - INFO - main - [database config 'master']: Preparing for databases ['main', 'state']                                          
2023-06-20 19:44:04,881 - synapse.storage.prepare_database - 120 - INFO - main - ['main', 'state']: Checking existing schema version                                                    
2023-06-20 19:44:04,882 - synapse.storage.prepare_database - 124 - INFO - main - ['main', 'state']: Existing schema is 77 (+5 deltas)                                                   
2023-06-20 19:44:04,882 - synapse.storage.databases.main - 288 - INFO - main - Checking database for consistency with configuration...                                                  
2023-06-20 19:44:04,882 - synapse.storage.prepare_database - 418 - INFO - main - Applying schema deltas for v77                                                                         
2023-06-20 19:44:04,883 - synapse.storage.prepare_database - 418 - INFO - main - Applying schema deltas for v78                                                                         
2023-06-20 19:44:04,883 - synapse.storage.prepare_database - 513 - INFO - main - Running 78/01_validate_and_update_profiles.py:run_upgrade                                              
2023-06-20 19:44:04,899 - synapse.storage.prepare_database - 513 - INFO - main - Running 78/02_validate_and_update_user_filters.py:run_upgrade                                          
2023-06-20 19:44:04,901 - synapse.app._base - 215 - ERROR - main - Exception during startup                                                                                             
Traceback (most recent call last):                                                                                                                                                      
  File "/opt/venvs/matrix-synapse/lib/python3.8/site-packages/synapse/app/homeserver.py", line 352, in setup hs.setup()
  File "/opt/venvs/matrix-synapse/lib/python3.8/site-packages/synapse/server.py", line 339, in setup self.datastores = Databases(self.DATASTORE_CLASS, self)
  File "/opt/venvs/matrix-synapse/lib/python3.8/site-packages/synapse/storage/databases/__init__.py", line 74, in __init__ prepare_database(
  File "/opt/venvs/matrix-synapse/lib/python3.8/site-packages/synapse/storage/prepare_database.py", line 141, in prepare_database _upgrade_existing_database(
  File "/opt/venvs/matrix-synapse/lib/python3.8/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.8/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.8/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.8/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

csnover · 2023-06-20T23:48:39Z

I also encountered this issue. It was introduced by PR #15649.

The workaround I used was to delete the UNIQUE (full_user_id), UNIQUE (user_id) constraints from the CREATE TABLE temp_user_filters statement in 02_validate_and_update_user_filters.py. The correct composite unique index on (user_id, filter_id) is added later. I guess that the extra keys that are causing the problem could be copy–paste errors since this PR also updates the temp_profiles table which does the same thing (and for that table those unique keys look like they are correct).

benjistokman · 2023-06-21T00:51:27Z

And how do I do that?

SiskoUrso · 2023-06-21T01:09:21Z

having this same issue as well after the 1.86.0 update, I rolled back to v1.85.2 for now and its working.

benjistokman · 2023-06-21T01:33:58Z

That's working for me.

ShadowTux · 2023-06-21T01:38:32Z

I seem to encounter similar issues. I also rolled back for the moment and started working again.

andreas-glaser · 2023-06-21T04:40:54Z

Same issue here

zerobulfa · 2023-06-21T04:59:15Z

I also encountered this issue. It was introduced by PR #15649.

The workaround I used was to delete the UNIQUE (full_user_id), UNIQUE (user_id) constraints from the CREATE TABLE temp_user_filters statement in 02_validate_and_update_user_filters.py. The correct composite unique index on (user_id, filter_id) is added later. I guess that the extra keys that are causing the problem could be copy–paste errors since this PR also updates the temp_profiles table which does the same thing (and for that table those unique keys look like they are correct).

helps me also - I think it´s a good time to migrate to Postgres... thanks @csnover

FlyveHest · 2023-06-21T07:14:04Z

Had the same issue, and I am running the docker image.

Temporarily fixed by rolling back to tag matrixdotorg/synapse:v1.85.2

Editiing files internally in the docker image is not really a viable solution, so a fix for this would be nice.

renaudallard · 2023-06-21T07:16:02Z

Same issue when building the OpenBSD port. I will wait for a fix before creating a new OpenBSD package.

Boldairdev · 2023-06-21T08:43:32Z

And how do I do that?

nano (or vi)
/opt/venvs/matrix-synapse/lib/python3.8/site-packages/synapse/storage/schema/main/delta/78/02_validate_and_update_user_filters.py

find:

       CREATE TABLE temp_user_filters (
            full_user_id text NOT NULL,
            user_id text NOT NULL,
            filter_id bigint NOT NULL,
            filter_json bytea NOT NULL,
            UNIQUE (full_user_id), 
            UNIQUE (user_id)

replace with:

     CREATE TABLE temp_user_filters (
           full_user_id text NOT NULL,
           user_id text NOT NULL,
           filter_id bigint NOT NULL,
           filter_json bytea NOT NULL
       )

(do not forget to remove the comma at the end of "filter_json bytea NOT NULL"..

do a systemctl restart matrix-synapse

that did it for me.

That being said, a proper fix would be nice :)

githubber4ever · 2023-06-21T10:12:59Z

Got the same issue since last upgrade to the latest version, had to roll-back to matrixdotorg/synapse:v1.85.2 docker image.

apinter · 2023-06-21T17:20:39Z

Got the same error on 2 nodes using the container image. Might indeed need to look into migrating to psql. Rollback solved the issue tho.

neilschuerch · 2023-06-22T15:17:55Z

Same issue here.
I will stay on v1.85.2 for now.
Will this be fixed in v1.56.1 or do we need to do the workaround?

Eirikr70 · 2023-06-23T00:39:50Z

Same issue with docker. Same resolution rolling back to v1.85.2.

renaudallard · 2023-06-23T06:42:25Z

Same issue here. I will stay on v1.85.2 for now. Will this be fixed in v1.56.1 or do we need to do the workaround?

I assume they will solve it in the next version, otherwise sqlite support is broken.

alyaeanyx · 2023-06-23T08:43:04Z

Same issue on NixOS 23.05, also fixed it by downgrading to 1.85.2.

FlyveHest · 2023-06-23T10:44:02Z

I assume they will solve it in the next version, otherwise sqlite support is broken.

There is an ongoing PR on this, so a fix is underway

ThatCoffeeGuy · 2023-06-23T12:19:24Z

Same here. What was the testing method used before releasing this version? If there wasn't any, this could be a great time to implement testing. This was released in a completely broken state.

I understand it's a large bump from 1.71.0, but since it was the next release in apt, this exact scenario could have been tested.

clokep · 2023-06-23T12:59:07Z

Please do not run random SQL against your database. #15817 looks like it should fix this in the next version.

reivilibre · 2023-06-23T13:11:12Z

I understand it's a large bump from 1.71.0, but since it was the next release in apt, this exact scenario could have been tested.

— @ThatCoffeeGuy

Every version is released with Debian/apt packages, so in principle there was no need for this large bump. (With that said, we do aim to make it so that any upgrade will work, but it can be quite complex to do so.)

alyaeanyx · 2023-06-23T16:50:47Z

In my case, the upgrade from 1.85.2 to 1.86.0 also caused this issue, so it doesn't seem like @ThatCoffeeGuy 's large bump is the cause of it in their case.

mattcaron · 2023-06-24T01:07:28Z

I can confirm that this is not limited to big jumps. My dpkg.log reports that I went from 1.84.0 to 1.85.0 on 06 June and from 1.85.0 to 1.86.0 today. The previous upgrade went without issue. This latest one blew up as described above.

mat-l · 2023-06-24T12:49:14Z

Same here on Docker v 1.86.0. v1.85.2 is working for me:

Starting synapse with args -m synapse.app.homeserver --config-path /data/homeserver.yaml

2023-06-24 12:38:49,933 - root - 348 - WARNING - main - ***** STARTING SERVER *****

2023-06-24 12:38:49,933 - root - 349 - WARNING - main - Server /usr/local/lib/python3.11/site-packages/synapse/app/homeserver.py version 1.86.0

2023-06-24 12:38:49,933 - root - 354 - INFO - main - Server hostname: chat.mnethome.de

2023-06-24 12:38:49,933 - root - 355 - INFO - main - Instance name: master

2023-06-24 12:38:49,933 - root - 356 - INFO - main - Twisted reactor: EPollReactor

2023-06-24 12:38:49,933 - synapse.app.homeserver - 349 - INFO - main - Setting up server

2023-06-24 12:38:49,933 - synapse.server - 337 - INFO - main - Setting up.

2023-06-24 12:38:49,935 - synapse.storage.databases - 66 - INFO - main - [database config 'master']: Checking database server

2023-06-24 12:38:49,935 - synapse.storage.databases - 69 - INFO - main - [database config 'master']: Preparing for databases ['main', 'state']

2023-06-24 12:38:49,935 - synapse.storage.prepare_database - 120 - INFO - main - ['main', 'state']: Checking existing schema version

2023-06-24 12:38:49,936 - synapse.storage.prepare_database - 124 - INFO - main - ['main', 'state']: Existing schema is 77 (+4 deltas)

2023-06-24 12:38:49,936 - synapse.storage.databases.main - 288 - INFO - main - Checking database for consistency with configuration...

2023-06-24 12:38:49,936 - synapse.storage.prepare_database - 418 - INFO - main - Applying schema deltas for v77

2023-06-24 12:38:49,936 - synapse.storage.prepare_database - 526 - INFO - main - Applying schema 77/14bg_indices_event_stream_ordering.sql

2023-06-24 12:38:49,994 - synapse.storage.prepare_database - 418 - INFO - main - Applying schema deltas for v78

2023-06-24 12:38:49,995 - synapse.storage.prepare_database - 513 - INFO - main - Running 78/01_validate_and_update_profiles.py:run_upgrade

2023-06-24 12:38:50,003 - synapse.storage.prepare_database - 513 - INFO - main - Running 78/02_validate_and_update_user_filters.py:run_upgrade

2023-06-24 12:38:50,004 - 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 352, in setup

    hs.setup()

  File "/usr/local/lib/python3.11/site-packages/synapse/server.py", line 339, 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 141, in prepare_database

    _upgrade_existing_database(

  File "/usr/local/lib/python3.11/site-packages/synapse/storage/prepare_database.py", line 514, in _upgrade_existing_database

    module.run_upgrade(cur, database_engine, config=config)

  File "/usr/local/lib/python3.11/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 "/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)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^

sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id

**********************************************************************************

 Error during initialisation:

     Traceback (most recent call last):

       File "/usr/local/lib/python3.11/site-packages/synapse/app/homeserver.py", line 352, in setup

         hs.setup()

       File "/usr/local/lib/python3.11/site-packages/synapse/server.py", line 339, 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 141, in prepare_database

         _upgrade_existing_database(

       File "/usr/local/lib/python3.11/site-packages/synapse/storage/prepare_database.py", line 514, in _upgrade_existing_database

         module.run_upgrade(cur, database_engine, config=config)

       File "/usr/local/lib/python3.11/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 "/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)

                ^^^^^^^^^^^^^^^^^^^^^^^^^^

     sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id

GregOriol · 2023-06-25T08:07:56Z

The solution from @Boldairdev did work.

However, by looking at #15817, it could be necessary to also run
CREATE UNIQUE INDEX IF NOT EXISTS user_filters_full_user_id_unique ON user_filters (full_user_id, filter_id);

DMRobertson · 2023-06-25T10:34:43Z

Everyone who has encountered this: please wait for a release with a fix, and in the meantime rollback to a working version.

You should NOT manually make edits to the Synapse database at all. Doing so without knowing what you're doing is highly unsafe and we cannot support anyone who has done so without our guidance.

kenmickles · 2023-06-26T16:19:20Z

Also ran into this on Ubuntu 20.04 with the apt package.

This is how I rolled back, in case any future Ubuntu users are confused by the process like I was:

Download the debs.tar.xz file from the release page and extract it
Copy matrix-synapse-py3_1.85.2+focal1_amd64.deb to your server
Install it with dpkg -i matrix-synapse-py3_1.85.2+focal1_amd64.deb

DMRobertson · 2023-06-26T16:22:20Z

This is how I rolled back

I would try follow the advice in https://askubuntu.com/a/138327/610524 to downgrade to the deb in the repository itself. (I'm not sure if the debs in debs.tar.xz have been signed by the matrix.org signing key?)

kenmickles · 2023-06-26T17:33:31Z

@DMRobertson I tried that first and it didn't work. Are the old versions still on the package server?

DMRobertson · 2023-06-26T17:35:10Z

I'm not familiar with the intricacies of Debian packaging, but I believe so: https://packages.matrix.org/debian/pool/main/m/matrix-synapse-py3/

blzbrg · 2023-06-26T17:53:46Z

I tried to rollback on debian using apt get and the = syntax to select a particular version, but I gave up and downloaded the debs by hand from that pool link. I don't understand apt packaging very well, but I perhaps the issue is that the old versions are missing from the Packages.

My debian is on bullseye, so I looked at https://packages.matrix.org/debian/dists/bullseye/main/binary-amd64/Packages and saw that the only two versions for matrix-synapse-py3 were 1.86.0+bullseye1 and 1.71.0+bullseye1. 1.85, 1.83, and other releases in-between were not there.

Edit: looks like this is tracked by this is #14082

H-Shay mentioned this issue Jun 21, 2023

Fix sqlite user_filters upgrade #15817

Merged

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 21, 2023

JonBoyleCoding mentioned this issue Jun 25, 2023

matrix-synapse 1.86.0 unable to launch: sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id NixOS/nixpkgs#239833

Closed

erikjohnston closed this as completed in #15817 Jun 27, 2023

MadLittleMods mentioned this issue Jun 28, 2023

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

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQLITE3 error "UNIQUE constraint failed: temp_user_filters.user_id" after update to 1.86.0 #15809

SQLITE3 error "UNIQUE constraint failed: temp_user_filters.user_id" after update to 1.86.0 #15809

brunofontes commented Jun 20, 2023 •

edited by MadLittleMods

Loading

csnover commented Jun 20, 2023 •

edited

Loading

benjistokman commented Jun 21, 2023

SiskoUrso commented Jun 21, 2023 •

edited

Loading

benjistokman commented Jun 21, 2023

ShadowTux commented Jun 21, 2023

andreas-glaser commented Jun 21, 2023

zerobulfa commented Jun 21, 2023

FlyveHest commented Jun 21, 2023

renaudallard commented Jun 21, 2023

Boldairdev commented Jun 21, 2023 •

edited

Loading

githubber4ever commented Jun 21, 2023

apinter commented Jun 21, 2023

neilschuerch commented Jun 22, 2023

Eirikr70 commented Jun 23, 2023

renaudallard commented Jun 23, 2023

alyaeanyx commented Jun 23, 2023 •

edited

Loading

FlyveHest commented Jun 23, 2023

ThatCoffeeGuy commented Jun 23, 2023 •

edited

Loading

clokep commented Jun 23, 2023 •

edited

Loading

reivilibre commented Jun 23, 2023

alyaeanyx commented Jun 23, 2023 •

edited

Loading

mattcaron commented Jun 24, 2023

mat-l commented Jun 24, 2023 •

edited

Loading

GregOriol commented Jun 25, 2023

DMRobertson commented Jun 25, 2023 •

edited

Loading

kenmickles commented Jun 26, 2023 •

edited

Loading

DMRobertson commented Jun 26, 2023

kenmickles commented Jun 26, 2023

DMRobertson commented Jun 26, 2023 •

edited

Loading

blzbrg commented Jun 26, 2023 •

edited

Loading

SQLITE3 error "UNIQUE constraint failed: temp_user_filters.user_id" after update to 1.86.0 #15809

SQLITE3 error "UNIQUE constraint failed: temp_user_filters.user_id" after update to 1.86.0 #15809

Comments

brunofontes commented Jun 20, 2023 • edited by MadLittleMods Loading

Description

Steps to reproduce

Homeserver

Synapse Version

Installation Method

Database

Workers

Platform

Configuration

Relevant log output

Anything else that would be useful to know?

csnover commented Jun 20, 2023 • edited Loading

benjistokman commented Jun 21, 2023

SiskoUrso commented Jun 21, 2023 • edited Loading

benjistokman commented Jun 21, 2023

ShadowTux commented Jun 21, 2023

andreas-glaser commented Jun 21, 2023

zerobulfa commented Jun 21, 2023

FlyveHest commented Jun 21, 2023

renaudallard commented Jun 21, 2023

Boldairdev commented Jun 21, 2023 • edited Loading

githubber4ever commented Jun 21, 2023

apinter commented Jun 21, 2023

neilschuerch commented Jun 22, 2023

Eirikr70 commented Jun 23, 2023

renaudallard commented Jun 23, 2023

alyaeanyx commented Jun 23, 2023 • edited Loading

FlyveHest commented Jun 23, 2023

ThatCoffeeGuy commented Jun 23, 2023 • edited Loading

clokep commented Jun 23, 2023 • edited Loading

reivilibre commented Jun 23, 2023

alyaeanyx commented Jun 23, 2023 • edited Loading

mattcaron commented Jun 24, 2023

mat-l commented Jun 24, 2023 • edited Loading

GregOriol commented Jun 25, 2023

DMRobertson commented Jun 25, 2023 • edited Loading

kenmickles commented Jun 26, 2023 • edited Loading

DMRobertson commented Jun 26, 2023

kenmickles commented Jun 26, 2023

DMRobertson commented Jun 26, 2023 • edited Loading

blzbrg commented Jun 26, 2023 • edited Loading

brunofontes commented Jun 20, 2023 •

edited by MadLittleMods

Loading

csnover commented Jun 20, 2023 •

edited

Loading

SiskoUrso commented Jun 21, 2023 •

edited

Loading

Boldairdev commented Jun 21, 2023 •

edited

Loading

alyaeanyx commented Jun 23, 2023 •

edited

Loading

ThatCoffeeGuy commented Jun 23, 2023 •

edited

Loading

clokep commented Jun 23, 2023 •

edited

Loading

alyaeanyx commented Jun 23, 2023 •

edited

Loading

mat-l commented Jun 24, 2023 •

edited

Loading

DMRobertson commented Jun 25, 2023 •

edited

Loading

kenmickles commented Jun 26, 2023 •

edited

Loading

DMRobertson commented Jun 26, 2023 •

edited

Loading

blzbrg commented Jun 26, 2023 •

edited

Loading