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. #15736
Description
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