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

psycopg2.IntegrityError (duplicated key device_lists_remote_cache_unique_id) #4389

Closed
sy6sy2 opened this issue Jan 15, 2019 · 10 comments
Closed

psycopg2.IntegrityError (duplicated key device_lists_remote_cache_unique_id) #4389

sy6sy2 opened this issue Jan 15, 2019 · 10 comments

Comments

@sy6sy2
Copy link

sy6sy2 commented Jan 15, 2019

Description

Synapse seems working but I get an error in log since my last update:

Jan 15 08:42:01 cecchetto python[26519]: Traceback (most recent call last):
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/synapse/storage/background_updates.py", line 106, in _run_background_updates
Jan 15 08:42:01 cecchetto python[26519]:     self.BACKGROUND_UPDATE_DURATION_MS
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Jan 15 08:42:01 cecchetto python[26519]:     result = result.throwExceptionIntoGenerator(g)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
Jan 15 08:42:01 cecchetto python[26519]:     return g.throw(self.type, self.value, self.tb)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/synapse/storage/background_updates.py", line 181, in do_next_background_update
Jan 15 08:42:01 cecchetto python[26519]:     res = yield self._do_background_update(update_name, desired_duration_ms)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Jan 15 08:42:01 cecchetto python[26519]:     result = result.throwExceptionIntoGenerator(g)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
Jan 15 08:42:01 cecchetto python[26519]:     return g.throw(self.type, self.value, self.tb)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/synapse/storage/background_updates.py", line 215, in _do_background_update
Jan 15 08:42:01 cecchetto python[26519]:     items_updated = yield update_handler(progress, batch_size)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Jan 15 08:42:01 cecchetto python[26519]:     result = result.throwExceptionIntoGenerator(g)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
Jan 15 08:42:01 cecchetto python[26519]:     return g.throw(self.type, self.value, self.tb)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/synapse/storage/background_updates.py", line 362, in updater
Jan 15 08:42:01 cecchetto python[26519]:     yield self.runWithConnection(runner)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
Jan 15 08:42:01 cecchetto python[26519]:     result = result.throwExceptionIntoGenerator(g)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator
Jan 15 08:42:01 cecchetto python[26519]:     return g.throw(self.type, self.value, self.tb)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/synapse/storage/_base.py", line 374, in runWithConnection
Jan 15 08:42:01 cecchetto python[26519]:     inner_func, *args, **kwargs
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/python/threadpool.py", line 250, in inContext
Jan 15 08:42:01 cecchetto python[26519]:     result = inContext.theWork()
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/python/threadpool.py", line 266, in <lambda>
Jan 15 08:42:01 cecchetto python[26519]:     inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/python/context.py", line 122, in callWithContext
Jan 15 08:42:01 cecchetto python[26519]:     return self.currentContext().callWithContext(ctx, func, *args, **kw)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/python/context.py", line 85, in callWithContext
Jan 15 08:42:01 cecchetto python[26519]:     return func(*args,**kw)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
Jan 15 08:42:01 cecchetto python[26519]:     compat.reraise(excValue, excTraceback)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
Jan 15 08:42:01 cecchetto python[26519]:     result = func(conn, *args, **kw)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/synapse/storage/_base.py", line 370, in inner_func
Jan 15 08:42:01 cecchetto python[26519]:     return func(conn, *args, **kwargs)
Jan 15 08:42:01 cecchetto python[26519]:   File "/opt/yunohost/matrix-synapse/local/lib/python2.7/site-packages/synapse/storage/background_updates.py", line 322, in create_index_psql
Jan 15 08:42:01 cecchetto python[26519]:     c.execute(sql)
Jan 15 08:42:01 cecchetto python[26519]: IntegrityError: ERREUR:  n'a pas pu créer l'index unique « device_lists_remote_cache_unique_id »
Jan 15 08:42:01 cecchetto python[26519]: DETAIL:  La clé (user_id, device_id)=(@rafi:srvmaison.nohost.me, GGRWAIQCHD) est dupliquée.

This error happen again and again.

Version information
@sy6sy2 sy6sy2 changed the title The key (user_id, device_id)=(@rafi:srvmaison.nohost.me, GGRWAIQCHD) is duplicated psycopg2.IntegrityError (duplicated key device_lists_remote_cache_unique_id) Jan 27, 2019
@ArchangeGabriel
Copy link
Contributor

I have dozens of those too. This + #4379 are filling my logs.

@ArchangeGabriel
Copy link
Contributor

Seems to be #2182 btw.

@sy6sy2
Copy link
Author

sy6sy2 commented Feb 6, 2019

I hope someone can resolve this :-/

@ArchangeGabriel ArchangeGabriel mentioned this issue Mar 19, 2019
Closed
@sy6sy2
Copy link
Author

sy6sy2 commented Apr 12, 2019

Still have this error in my log every one second.
I think that a "magic SQL command" can resolve this but don't know which :-/

@ArchangeGabriel
Copy link
Contributor

@SylvainCecchetto The “magic SQL command” you are looking for is here.

@sy6sy2
Copy link
Author

sy6sy2 commented Apr 12, 2019 •
edited
Loading

This one does not work for me :-/

My error is on device_lists_remote_cache_unique_id

@sy6sy2 sy6sy2 mentioned this issue Apr 16, 2019
Closed
@sy6sy2
Copy link
Author

sy6sy2 commented Apr 23, 2019 •
edited
Loading

Don't know if it's the correct way but it seems that I was finally able to fix this error with:

ALTER TABLE device_lists_remote_cache DROP COLUMN user_id;
ALTER TABLE device_lists_remote_cache DROP COLUMN device_id;
ALTER TABLE device_lists_remote_cache DROP COLUMN content;
ALTER TABLE device_lists_remote_cache ADD COLUMN user_id text;
ALTER TABLE device_lists_remote_cache ADD COLUMN device_id text;
ALTER TABLE device_lists_remote_cache ADD COLUMN content text;
ALTER TABLE device_lists_remote_extremeties DROP COLUMN user_id;
ALTER TABLE device_lists_remote_extremeties DROP COLUMN stream_id;
ALTER TABLE device_lists_remote_extremeties ADD COLUMN user_id text;
ALTER TABLE device_lists_remote_extremeties ADD COLUMN stream_id text;

BACKUP YOUR DATABASE BEFORE DOING ANYTHING!!

@sy6sy2 sy6sy2 closed this as completed Apr 23, 2019
@peterhoeg
Copy link

Don't know if it's the correct way

Have you had any problems since doing that as I am getting this every second as well.

@sy6sy2
Copy link
Author

sy6sy2 commented May 30, 2019

No problem since this fix ;-)

@peterhoeg
Copy link

Instead of modifying the table structure, I simply deleted the contents of the affected tables (3 in my case) and then synapse managed to create the indices.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@ArchangeGabriel @peterhoeg @sy6sy2