duplicate key value violates unique constraint "event_push_summary_unique_index2" DETAIL: Key (user_id, room_id, thread_id)=(@myuser:myserver.com, !XJcwvojEyyhGBJngjo:matrix.org, main) already exists.

[Cknight70]

Description

When upgrading from synapse 1.84 to 1.85. synapse fails to start with the error
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "event_push_summary_unique_index2" DETAIL: Key (user_id, room_id, thread_id)=(@myuser:myserver.com, !XJcwvojEyyhGBJngjo:matrix.org, main) already exists.

I had 2 other instances of this error, which I deleted like so

Looking at the db, there is only one corresponding row, I found this with
select * from event_push_summary where user_id = '@myuser:myserver.com' and room_id = '!XJcwvojEyyhGBJngjo:matrix.org' and thread_id = 'main';

I attempted to delete the row like so
delete from event_push_summary where user_id = '@myuser:myserver.com' and room_id = '!XJcwvojEyyhGBJngjo:matrix.org' and thread_id = 'main';

Then I attempted to restart synapse, which gave me a new username and room error that already exists.

However, for this final one, the database reports there are no rows for this, but synapse still gives the same startup error.

I have had to rollback to 1.84.

My setup, I'm running Ubuntu 22.04, running synapse with official docker container. Running official postgres docker image 13.10-alpine

Steps to reproduce

• upgrade to 1.85 using docker for synapse and docker for postgres 13.10
• deleting row still showed the same error

Homeserver

myhomeserver.com

Synapse Version

1.85

Installation Method

Docker (matrixdotorg/synapse)

Database

13.10-alpine

Workers

Multiple workers

Platform

Ubuntu 22.04 with docker containers

Configuration

No response

Relevant log output

full error

 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 338, 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 527, in _upgrade_existing_database
         database_engine.execute_script_file(cur, 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 228, 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.UniqueViolation: duplicate key value violates unique constraint "event_push_summary_unique_index2"
     DETAIL:  Key (user_id, room_id, thread_id)=(@myuser:myserver.com, !XJcwvojEyyhGBJngjo:matrix.org, main) already exists.

Anything else that would be useful to know?

Possibly related #15597

[erikjohnston]

I think this is erroring at:

synapse/synapse/storage/schema/main/delta/77/05thread_notifications_backfill.sql

Line 24 in 1a54953

UPDATE event_push_summary SET thread_id = 'main' WHERE thread_id IS NULL;

[erikjohnston]

Hopefully: #15738 should fix it.

Running the following query manually before upgrade should also allow you to upgrade:

DELETE FROM event_push_summary AS a WHERE thread_id IS NULL AND EXISTS (
    SELECT 1 FROM event_push_summary AS b
    WHERE b.thread_id = 'main' AND a.user_id = b.user_id AND a.room_id = b.room_id
);

[Cknight70]

Hi, running this, I got
DELETE 5052671

Unfortunately trying to upgrade to synapse 1.85 still did not work.

I'm not sure if its due to my own tinkering as I said in my original post, but this is the error I got trying to run synapse 1.85

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 338, 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 527, in _upgrade_existing_database
         database_engine.execute_script_file(cur, 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 228, 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.UniqueViolation: duplicate key value violates unique constraint "event_push_summary_unique_index2"
     DETAIL:  Key (user_id, room_id, thread_id)=(@myuser:myserver.com, !XJcwvojEyyhGBJngjo:matrix.org, main) already exists.

running select * from event_push_summary where user_id = '@myuser:myserver.com' and room_id = '!XJcwvojEyyhGBJngjo:matrix.org' and thread_id = 'main';

returns 0 rows

[erikjohnston]

DELETE 5052671

Huh, that is way more rows than I would expect to see. And if it's deleted rows then why is it still failing the unique constraint :/

Can you run \d event_push_summary to see what state the schema is in please?

[erikjohnston]

running select * from event_push_summary where user_id = '@myuser:myserver.com' and room_id = '!XJcwvojEyyhGBJngjo:matrix.org' and thread_id = 'main';

Hmm, can you run:

select * from event_push_summary where user_id = '@myuser:myserver.com' and room_id = '!XJcwvojEyyhGBJngjo:matrix.org' and thread_id IS NULL;

I wonder if you've managed to get multiple rows with a null thread ID somehow

[Cknight70]

running
select * from event_push_summary where user_id = '@myuser:myserver.com' and room_id = '!XJcwvojEyyhGBJngjo:matrix.org' and thread_id IS NULL;
returns 247 rows

synapse=# \d event_push_summary
                   Table "public.event_push_summary"
            Column            |  Type  | Collation | Nullable | Default 
------------------------------+--------+-----------+----------+---------
 user_id                      | text   |           | not null | 
 room_id                      | text   |           | not null | 
 notif_count                  | bigint |           | not null | 
 stream_ordering              | bigint |           | not null | 
 unread_count                 | bigint |           |          | 
 last_receipt_stream_ordering | bigint |           |          | 
 thread_id                    | text   |           |          | 
Indexes:
    "event_push_summary_thread_id_null" btree (thread_id) WHERE thread_id IS NULL
    "event_push_summary_unique_index2" UNIQUE, btree (user_id, room_id, thread_id)

[erikjohnston]

select * from event_push_summary where user_id = '@myuser:myserver.com' and room_id = '!XJcwvojEyyhGBJngjo:matrix.org' and thread_id IS NULL;
returns 247 rows

Ouch. How did that happen. Annoyingly postgres unique indexes (by default) treat nulls as distinct.

I think the following queries should fix it, though it might give the wrong notification counts for some of those rooms:

-- Copy the NULL threads to have a 'main' thread ID.
--
-- Note: Some people seem to have duplicate rows with a `NULL` thread ID, in
-- which case we just fudge it with using MAX of the values. The counts *may* be
-- wrong for such rooms, but a) its an edge case, and b) they'll be fixed when
-- the user reads the room.
INSERT INTO event_push_summary (user_id, room_id, notif_count, stream_ordering, unread_count, last_receipt_stream_ordering, thread_id)
    SELECT user_id, room_id, MAX(notif_count), MAX(stream_ordering), MAX(unread_count), MAX(last_receipt_stream_ordering), 'main'
    FROM event_push_summary
    WHERE thread_id IS NULL
    GROUP BY user_id, room_id, thread_id;

DELETE FROM event_push_summary AS a WHERE thread_id IS NULL;

[Cknight70]

Hi, I ran this and it seems to have worked, deleting a few thousand rows. I'm now running synapse 1.85, thank you

[erikjohnston]

Thanks for testing the queries out! We'll put out a 1.85.1 that fixes up the schema deltas to do this automatically.