Synapse: 500 errors with "duplicate key value" on remote_media_cache_thumbnails when dynamic_thumbnails enabled

[turt2live]

Error is at the end of this issue (it's a long one).

The following URLs produce similar errors to the one included here:

• https://t2bot.io/_matrix/media/v1/thumbnail/matrix.org/HtRqOXgQyGkcePuSgsuBrLcx?width=128&height=128&method=crop
• https://t2bot.io/_matrix/media/v1/thumbnail/matrix.org/ARfYZMTdGlRCAGXurhYSZGwT?width=128&height=128&method=crop
• https://t2bot.io/_matrix/media/v1/thumbnail/matrix.org/pFUfshTtyiLsZAODtmHOOLop?width=128&height=128&method=crop

There may be more, however those 3 are easily noticed.

Changing the method to scale does not result in an error. This does not appear to affect local media.

I tried to delete the files and records from the database in varying combinations (delete files & delete records, only delete records, only delete files) in various running states of synapse (running, shut down, restart after). I tried various combinations of deleting data from the two tables (from both remote_media_cache and remote_media_cache_thumbnails, only _thumbnails, only _cache). No luck.

The error continues and does not go away. If I'm missing a step, please let me know. Otherwise, this appears to be a very specific and strange bug.

Synapse version: v0.20.0

2017-05-02 20:35:23,067 - synapse.access.http.8008 - 59 - INFO - GET-753- <IP REDACTED> - 8008 - Received request: GET /_matrix/media/v1/thumbnail/matrix.org/ARfYZMTdGlRCAGXurhYSZGwT?width=128&height=128&method=crop
2017-05-02 20:35:23,068 - synapse.util.async - 201 - INFO - GET-753- Acquired linearizer lock 'media_remote' for key ('matrix.org', 'ARfYZMTdGlRCAGXurhYSZGwT')
2017-05-02 20:35:23,079 - synapse.util.async - 208 - INFO - GET-753- Releasing linearizer lock 'media_remote' for key ('matrix.org', 'ARfYZMTdGlRCAGXurhYSZGwT')
2017-05-02 20:35:23,083 - synapse.metrics - 162 - INFO - - Collecting gc 0
2017-05-02 20:35:23,106 - synapse.rest.media.v1.thumbnailer - 93 - INFO - - Stored thumbnail in file u'/root/synapse/media_store/remote_thumbnail/matrix.org/Sg/El/RAnfzhTBOJHxWANIWpBP/128-128-image-png'
2017-05-02 20:35:23,118 - synapse.metrics - 162 - INFO - - Collecting gc 0
2017-05-02 20:35:23,120 - synapse.http.server - 139 - ERROR - GET-753- Failed handle request synapse.rest.media.v1.thumbnail_resource._async_render_GET on <synapse.rest.media.v1.thumbnail_resource.ThumbnailResource instance at 0x7f3e179dabd8>: <XForwardedForRequest at 0x7f3dcf04b6c8 method=GET uri=/_matrix/media/v1/thumbnail/matrix.org/ARfYZMTdGlRCAGXurhYSZGwT?width=128&height=128&method=crop clientproto=HTTP/1.0 site=8008>
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/synapse/http/server.py", line 118, in wrapped_request_handler
    yield request_handler(self, request)
  File "/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1299, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py", line 393, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/local/lib/python2.7/dist-packages/synapse/rest/media/v1/thumbnail_resource.py", line 71, in _async_render_GET
    width, height, method, m_type
  File "/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1299, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py", line 393, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/local/lib/python2.7/dist-packages/synapse/rest/media/v1/thumbnail_resource.py", line 194, in _select_or_generate_remote_thumbnail
    desired_height, desired_method, desired_type
  File "/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1299, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py", line 393, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/local/lib/python2.7/dist-packages/synapse/rest/media/v1/media_repository.py", line 318, in generate_remote_exact_thumbnail
    t_width, t_height, t_type, t_method, t_len
  File "/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1299, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py", line 393, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py", line 406, in _simple_insert
    self._simple_insert_txn, table, values,
  File "/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1299, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py", line 393, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py", line 319, in runInteraction
    inner_func, *args, **kwargs
  File "/usr/local/lib/python2.7/dist-packages/twisted/python/threadpool.py", line 250, in inContext
    result = inContext.theWork()
  File "/usr/local/lib/python2.7/dist-packages/twisted/python/threadpool.py", line 266, in <lambda>
    inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "/usr/local/lib/python2.7/dist-packages/twisted/python/context.py", line 122, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/usr/local/lib/python2.7/dist-packages/twisted/python/context.py", line 85, in callWithContext
    return func(*args,**kw)
  File "/usr/local/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py", line 307, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/usr/local/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py", line 298, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py", line 313, in inner_func
    func, *args, **kwargs
  File "/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py", line 242, in _new_transaction
    r = func(txn, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py", line 426, in _simple_insert_txn
    txn.execute(sql, vals)
  File "/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py", line 80, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py", line 110, in _do_execute
    sql, *args
IntegrityError: duplicate key value violates unique constraint "remote_media_cache_thumbnails_media_origin_media_id_thumbna_key"
DETAIL:  Key (media_origin, media_id, thumbnail_width, thumbnail_height, thumbnail_type)=(matrix.org, ARfYZMTdGlRCAGXurhYSZGwT, 128, 128, image/png) already exists.

[jkolo]

This is the reason I made pull request #1946

[Half-Shot]

This continues to bite me all the time on riot-android and the toasts that keep popping up are getting annoying.

[TildeSlashC0re]

I can confirm that I experience the same bug!

Seems to only apply to specific remote thumnails.

EDIT:

DELETE FROM remote_media_cache_thumbnails;

during stopped state, then restarting did the trick for me!

[Half-Shot]

Still experiencing this. It's a pretty awful experience for some avatars to load and some to fail.

[tleydxdy]

Same in 0.31.1

[ananace]

The issue can be solved without doing full table migrations - at least on PostgreSQL, technically without even requiring downtime.

ALTER TABLE remote_media_cache_thumbnails DROP CONSTRAINT remote_media_cache_thumbnails_media_origin_media_id_thumbna_key;
CREATE INDEX remote_media_cache_thumbnails_media_origin_media_id_thumbna_key ON remote_media_cache_thumbnails (media_origin, media_id, thumbnail_width, thumbnail_height, thumbnail_type, thumbnail_method);

[zzottel]

Just had the same after enabling dynamic thumbnails on my Synapse.

Can confirm that the SQL posted by ananace helps, but what is the consequence? That now multiple thumbs of the same size are allowed, right?

But why does Synapse even try to create its own dynamically if there's already one present for the desired resolution?

[TildeSlashC0re]

Dropping by to confirm that @ananace workaround did the trick!

[console-cowboy]

This remains an issue as of the 0.33.4 Release. The @ananace workaround provides the relief others have mentioned though.

[ArchangeGabriel]

Could some dev comment on @ananace workaround consequences? Should we just do that?

[msrd0]

Still an issue on 0.99.1.1, @ananace's workaround still works. Would be nice to know how that affects my server though. Does that mean I store a new thumbnail every time a client asks for one?

[richvdh]

Another report of this at #4915. Logs are:

2019-03-22 10:42:58,815 - synapse.access.http.8008 - 233 - INFO - GET-3590- 131.246.146.10 - 8008 - Received request: GET /_matrix/media/r0/thumbnail/t2bot.io/193408b58f5e1eb72d9bea13f23914e6?width=120&height=120&method=scale&animated=true
2019-03-22 10:42:58,816 - synapse.util.async_helpers - 205 - DEBUG - GET-3590- Acquired uncontended linearizer lock 'media_remote' for key ('t2bot.io', '193408b58f5e1eb72d9bea13f23914e6')
2019-03-22 10:42:58,817 - synapse.storage.txn - 317 - DEBUG - GET-3590- [TXN START] {get_cached_remote_media-764a}
2019-03-22 10:42:58,818 - synapse.storage.txn - 373 - DEBUG - GET-3590- [TXN END] {get_cached_remote_media-764a} 0.001629 sec
2019-03-22 10:42:58,822 - synapse.util.async_helpers - 218 - DEBUG - GET-3590- Releasing linearizer lock 'media_remote' for key ('t2bot.io', '193408b58f5e1eb72d9bea13f23914e6')
2019-03-22 10:42:58,822 - synapse.storage.txn - 317 - DEBUG - GET-3590- [TXN START] {get_remote_media_thumbnails-764b}
2019-03-22 10:42:58,823 - synapse.storage.txn - 373 - DEBUG - GET-3590- [TXN END] {get_remote_media_thumbnails-764b} 0.000840 sec
2019-03-22 10:42:58,832 - synapse.rest.media.v1.thumbnail_resource - 216 - DEBUG - GET-3590- We don't have a thumbnail of that size. Generating
2019-03-22 10:42:59,012 - synapse.rest.media.v1.media_repository - 534 - INFO - GET-3590- Stored thumbnail in file '/var/lib/matrix-synapse/media/remote_thumbnail/t2bot.io/19/34/08b58f5e1eb72d9bea13f23914e6/120-120-image-png'
2019-03-22 10:42:59,013 - synapse.storage.txn - 317 - DEBUG - GET-3590- [TXN START] {store_remote_media_thumbnail-764c}
2019-03-22 10:42:59,015 - synapse.storage._base - 152 - DEBUG - GET-3590- [SQL FAIL] {store_remote_media_thumbnail-764c} duplicate key value violates unique constraint "remote_media_cache_thumbnails_media_origin_media_id_thumbna_key"
DETAIL:  Key (media_origin, media_id, thumbnail_width, thumbnail_height, thumbnail_type)=(t2bot.io, 193408b58f5e1eb72d9bea13f23914e6, 120, 120, image/png) already exists.

2019-03-22 10:42:59,015 - synapse.storage._base - 365 - DEBUG - GET-3590- [TXN FAIL] {store_remote_media_thumbnail-764c} duplicate key value violates unique constraint "remote_media_cache_thumbnails_media_origin_media_id_thumbna_key"
DETAIL:  Key (media_origin, media_id, thumbnail_width, thumbnail_height, thumbnail_type)=(t2bot.io, 193408b58f5e1eb72d9bea13f23914e6, 120, 120, image/png) already exists.

2019-03-22 10:42:59,015 - synapse.storage.txn - 373 - DEBUG - GET-3590- [TXN END] {store_remote_media_thumbnail-764c} 0.001923 sec
2019-03-22 10:42:59,016 - synapse.http.server - 112 - ERROR - GET-3590- Failed handle request via 'ThumbnailResource': <XForwardedForRequest at 0x7f5490213d68 method='GET' uri='/_matrix/media/r0/thumbnail/t2bot.io/193408b58f5e1eb72d9bea13f23914e6?width=120&height=120&method=scale&animated=true' clientproto='HTTP/1.1' site=8008>
Traceback (most recent call last):
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/http/server.py", line 81, in wrapped_request_handler yield h(self, request)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks result = result.throwExceptionIntoGenerator(g)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator return g.throw(self.type, self.value, self.tb)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/rest/media/v1/thumbnail_resource.py", line 78, in _async_render_GET width, height, method, m_type
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks result = result.throwExceptionIntoGenerator(g)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator return g.throw(self.type, self.value, self.tb)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/rest/media/v1/thumbnail_resource.py", line 221, in _select_or_generate_remote_thumbnail desired_height, desired_method, desired_type
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks result = result.throwExceptionIntoGenerator(g)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator return g.throw(self.type, self.value, self.tb)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/rest/media/v1/media_repository.py", line 540, in generate_remote_exact_thumbnail t_width, t_height, t_type, t_method, t_len
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks result = result.throwExceptionIntoGenerator(g)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator return g.throw(self.type, self.value, self.tb)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/storage/_base.py", line 516, in _simple_insert self._simple_insert_txn, table, values,
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks result = result.throwExceptionIntoGenerator(g)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator return g.throw(self.type, self.value, self.tb)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/storage/_base.py", line 408, in runInteraction *args, **kwargs
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks result = result.throwExceptionIntoGenerator(g)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/failure.py", line 491, in throwExceptionIntoGenerator return g.throw(self.type, self.value, self.tb)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/storage/_base.py", line 457, in runWithConnection inner_func, *args, **kwargs
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/threadpool.py", line 250, in inContext result = inContext.theWork()
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/threadpool.py", line 266, in <lambda> inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/context.py", line 122, in callWithContext return self.currentContext().callWithContext(ctx, func, *args, **kw)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/context.py", line 85, in callWithContext return func(*args,**kw)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection compat.reraise(excValue, excTraceback)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/python/compat.py", line 464, in reraise raise exception.with_traceback(traceback)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection result = func(conn, *args, **kw)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/storage/_base.py", line 453, in inner_func return func(conn, *args, **kwargs)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/storage/_base.py", line 329, in _new_transaction r = func(txn, *args, **kwargs)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/storage/_base.py", line 536, in _simple_insert_txn txn.execute(sql, vals)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/storage/_base.py", line 119, in execute self._do_execute(self.txn.execute, sql, *args)
 File "/opt/venvs/matrix-synapse/lib/python3.5/site-packages/synapse/storage/_base.py", line 149, in _do_execute sql, *args
psycopg2.IntegrityError: duplicate key value violates unique constraint "remote_media_cache_thumbnails_media_origin_media_id_thumbna_key"

[vmario89]

thanks alot for sql statement. helped instantly!

[sy6sy2]

I have a similar error here https://github.com/matrix-org/synapse/issues/4389 (device_lists_remote_cache_unique_id)
But on another table I think.
Do you think that is possible to adapt the command given here https://github.com/matrix-org/synapse/issues/2182#issuecomment-401297320 for my case?

Thank you

[ordinarygulp]

Just came across this issue and the psql statements above helped, but can we get an official fix for this?

[rubo77]

This is the reason I made pull request #1946

That one seemed wrong and was closed.

It would be nice if an Admin could comment on this issue if the solution by @ananace removing the constraint would cause Problems later maybe on an Update

[rkfg]

Same for local thumbnails failing sometimes with this error:

Traceback (most recent call last):
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/http/server.py", line 76, in wrapped_request_handler
    await h(self, request)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/rest/media/v1/thumbnail_resource.py", line 64, in _async_render_GET
    request, media_id, width, height, method, m_type
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/rest/media/v1/thumbnail_resource.py", line 179, in _select_or_generate_local_thumbnail
    url_cache=media_info["url_cache"],
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/rest/media/v1/media_repository.py", line 500, in generate_local_exact_thumbnail
    media_id, t_width, t_height, t_type, t_method, t_len
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 598, in _simple_insert
    yield self.runInteraction(desc, self._simple_insert_txn, table, values)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 492, in runInteraction
    **kwargs
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 540, in runWithConnection
    result = yield self._db_pool.runWithConnection(inner_func, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/threadpool.py", line 250, in inContext
    result = inContext.theWork()
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/threadpool.py", line 266, in <lambda>
    inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/context.py", line 122, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/context.py", line 85, in callWithContext
    return func(*args,**kw)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/compat.py", line 464, in reraise
    raise exception.with_traceback(traceback)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 537, in inner_func
    return func(conn, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 409, in _new_transaction
    r = func(txn, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 617, in _simple_insert_txn
    txn.execute(sql, vals)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 137, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 163, in _do_execute
    return func(sql, *args)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "local_media_repository_thumbn_media_id_thumbnail_width_thum_key"

I believe this should fix it:

ALTER TABLE local_media_repository_thumbnails DROP CONSTRAINT local_media_repository_thumbn_media_id_thumbnail_width_thum_key;
CREATE INDEX local_media_repository_thumbn_media_id_thumbnail_width_thum_key ON local_media_repository_thumbnails (media_id, thumbnail_width, thumbnail_height, thumbnail_type);

[rubo77]

@rkfg please create a pr with the fix

[msrd0]

It feels more like a workaround than as a fix tbh

[richvdh]

For links: #7124 is a work-in-progress in fixing this.

[532910]

I got:

psycopg2.IntegrityError: duplicate key value violates unique constraint "local_media_repository_thumbn_media_id_thumbnail_width_thum_key"
DETAIL:  Key (media_id, thumbnail_width, thumbnail_height, thumbnail_type)=(pwJGenkxEQAsZUFtmdCjriRH, 32, 32, image/png) already exists.

for my userpic on my homeserver:

https://matrix.domain.tld/_matrix/media/r0/thumbnail/domain.tld/<ID>?width=32&height=32

Is this the same issue, or another one?

[richvdh]

@532910 I don't think so. this issue affects remote thumbnails only, afaik.

Suggest you file a separate issue with a full stacktrace.

[brycied00d]

I believe @532910 you're seeing a regression of https://github.com/matrix-org/synapse/pull/1816. I also experienced this, my database schema did not match what was in the PR. Altering the CONSTRAINT to include the method resolved the database error for me.

(I know I really ought to open a new issue, but I'm entirely too busy right now, I'm sorry.)

[532910]

7923

[richvdh]

fixed by #7124.

[f0x52]

fixed by #7124.

I'm still getting these errors in v1.21.0 which afaik includes that commit?

[richvdh]

sigh. Please could you share the logs, with the stack trace?

[f0x52]

Oct 13 18:16:27 terra matrix-synapse[1297781]: DETAIL:  Key (media_origin, media_id, thumbnail_width, thumbnail_height, thumbnail_type, thumbnail_method)=(matrix.org, CENSORED, 14, 14, image/png, crop) already exists.
Oct 13 18:16:27 terra matrix-synapse[1297781]: psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "remote_media_repository_thumbn_media_origin_id_width_height_met"
Oct 13 18:16:27 terra matrix-synapse[1297781]:     return func(sql, *args)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/storage/database.py", line 238, in _do_execute
Oct 13 18:16:27 terra matrix-synapse[1297781]:     self._do_execute(self.txn.execute, sql, *args)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/storage/database.py", line 212, in execute
Oct 13 18:16:27 terra matrix-synapse[1297781]:     txn.execute(sql, vals)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/storage/database.py", line 748, in simple_insert_txn
Oct 13 18:16:27 terra matrix-synapse[1297781]:     r = func(cursor, *args, **kwargs)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/storage/database.py", line 447, in new_transaction
Oct 13 18:16:27 terra matrix-synapse[1297781]:     return func(conn, *args, **kwargs)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/storage/database.py", line 641, in inner_func
Oct 13 18:16:27 terra matrix-synapse[1297781]:     result = func(conn, *args, **kw)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
Oct 13 18:16:27 terra matrix-synapse[1297781]:     raise exception.with_traceback(traceback)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/twisted/python/compat.py", line 464, in reraise
Oct 13 18:16:27 terra matrix-synapse[1297781]:     compat.reraise(excValue, excTraceback)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
Oct 13 18:16:27 terra matrix-synapse[1297781]:     return func(*args,**kw)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/twisted/python/context.py", line 85, in callWithContext
Oct 13 18:16:27 terra matrix-synapse[1297781]:     return self.currentContext().callWithContext(ctx, func, *args, **kw)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/twisted/python/context.py", line 122, in callWithContext
Oct 13 18:16:27 terra matrix-synapse[1297781]:     inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/twisted/python/threadpool.py", line 266, in <lambda>
Oct 13 18:16:27 terra matrix-synapse[1297781]:     result = inContext.theWork()
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/twisted/python/threadpool.py", line 250, in inContext
Oct 13 18:16:27 terra matrix-synapse[1297781]:     return await make_deferred_yieldable(
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/storage/database.py", line 646, in runWithConnection
Oct 13 18:16:27 terra matrix-synapse[1297781]:     result = await self.runWithConnection(
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/storage/database.py", line 569, in runInteraction
Oct 13 18:16:27 terra matrix-synapse[1297781]:     await self.runInteraction(desc, self.simple_insert_txn, table, values)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/storage/database.py", line 727, in simple_insert
Oct 13 18:16:27 terra matrix-synapse[1297781]:     await self.db_pool.simple_insert(
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/storage/databases/main/media_repository.py", line 362, in store_remote_media_thumbnail
Oct 13 18:16:27 terra matrix-synapse[1297781]:     await self.store.store_remote_media_thumbnail(
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/rest/media/v1/media_repository.py", line 589, in generate_remote_exact_thumbnail
Oct 13 18:16:27 terra matrix-synapse[1297781]:     file_path = await self.media_repo.generate_remote_exact_thumbnail(
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/rest/media/v1/thumbnail_resource.py", line 225, in _select_or_generate_remote_thumbnail
Oct 13 18:16:27 terra matrix-synapse[1297781]:     await self._select_or_generate_remote_thumbnail(
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/rest/media/v1/thumbnail_resource.py", line 66, in _async_render_GET
Oct 13 18:16:27 terra matrix-synapse[1297781]:     callback_return = await raw_callback_return
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/http/server.py", line 258, in _async_render
Oct 13 18:16:27 terra matrix-synapse[1297781]:     callback_return = await self._async_render(request)
Oct 13 18:16:27 terra matrix-synapse[1297781]:   File "/home/synapse/env/lib/python3.8/site-packages/synapse/http/server.py", line 230, in _async_render_wrapper
Oct 13 18:16:27 terra matrix-synapse[1297781]: Traceback (most recent call last):
Oct 13 18:16:27 terra matrix-synapse[1297781]: 2020-10-13 18:16:27,683 - synapse.http.server - 81 - ERROR - GET-474558 - Failed handle request via 'ThumbnailResource': <XForwardedForRequest at 0x7fc4bdb9aca0 method='GET' uri='/_matrix/m>
Oct 13 18:16:25 terra matrix-synapse[1297781]: 2020-10-13 18:16:25,170 - synapse.rest.media.v1._base - 229 - WARNING - GET-474527 - Failed to write to consumer: <class 'Exception'> Consumer asked us to stop producing

[deepbluev7]

It seems like the migrations didn't delete the duplicate entries. The following two queries should fix that, if you run into those issues with the background update:

DELETE FROM local_media_repository_thumbnails a USING (
      SELECT MIN(ctid) as ctid, media_id, thumbnail_width, thumbnail_height, thumbnail_type, thumbnail_method
        FROM local_media_repository_thumbnails
        GROUP BY media_id, thumbnail_width, thumbnail_height, thumbnail_type, thumbnail_method HAVING COUNT(*) > 1
      ) b
      WHERE a.media_id = b.media_id
      AND a.thumbnail_width = b.thumbnail_width
      AND a.thumbnail_height = b.thumbnail_height
      AND a.thumbnail_type = b.thumbnail_type
      AND a.thumbnail_method = b.thumbnail_method
      AND a.ctid <> b.ctid;

DELETE FROM remote_media_cache_thumbnails a USING (
      SELECT MIN(ctid) as ctid, media_origin, media_id, thumbnail_width, thumbnail_height, thumbnail_type, thumbnail_method
        FROM remote_media_cache_thumbnails
        GROUP BY media_origin, media_id, thumbnail_width, thumbnail_height, thumbnail_type, thumbnail_method HAVING COUNT(*) > 1
      ) b
      WHERE a.media_id = b.media_id
      AND a.media_origin = b.media_origin
      AND a.thumbnail_width = b.thumbnail_width
      AND a.thumbnail_height = b.thumbnail_height
      AND a.thumbnail_type = b.thumbnail_type
      AND a.thumbnail_method = b.thumbnail_method
      AND a.ctid <> b.ctid;

They delete the duplicates and should allow the migrations to pass, although I'm not sure, if that has any consequences.

[richvdh]

@deepbluev7 I'm confused. I thought these exceptions were happening because the existing index (ADD CONSTRAINT local_media_repository_thumbn_media_id_thumbnail_width_thum_key UNIQUE (media_id, thumbnail_width, thumbnail_height, thumbnail_type)) was too strict. How can people have duplicates which prevent them running the migration?

That doesn't seem to be @f0x52's problem, anyway, because he's reporting an error with the new constraint. @f0x52: is it possible that you are just seeing two requests for the same thumbnail at the same time, and they are racing? I think that would be #6755, which is a somewhat different problem :/

[deepbluev7]

@richvdh It seems their databases contains duplicates, which is why the migration fails. They may have deleted the index manually or something, not sure, but I did verify, that the db contains duplicates and deleting them fixes the issue. At least running the above query seemed to fix it for both people reporting the issue. But yeah, that seems to not apply to @f0x52's issue. No idea about that one.

[richvdh]

If others have a problem with the migration, I'd like to know what their database schema looks like.

For now I'm going to assume this is correctly fixed, and @f0x52 is seeing #6755.

[alturiak]

@deepbluev7 @richvdh
That is correct, I did delete the index as proposed as a workaround at https://github.com/matrix-org/synapse/issues/2182#issuecomment-401297320, which probably lead to the duplicates in the first place. They wouldn't probably appear in unmodified installations.

[richvdh]

right, yes, people who have manually modified their database schema will now need to manually fix the resulting damage :).

[532910]

It looks like this is fixed. Anyway I don't see this error more.