Room stats: UniqueViolation violates unique constraint "room_stats_earliest_token_idx" D

[erikjohnston]

This is turning up a lot on matrix.org:

UniqueViolation: duplicate key value violates unique constraint "room_stats_earliest_token_idx"
DETAIL:  Key (room_id)=(<redacted>) already exists.

  File "synapse/storage/background_updates.py", line 107, in _run_background_updates
    self.BACKGROUND_UPDATE_DURATION_MS
  File "twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/storage/background_updates.py", line 182, in do_next_background_update
    res = yield self._do_background_update(update_name, desired_duration_ms)
  File "twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/storage/background_updates.py", line 215, in _do_background_update
    items_updated = yield update_handler(progress, batch_size)
  File "twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/storage/stats.py", line 273, in _populate_stats_process_rooms
    yield self.runInteraction("update_room_stats", _fetch_data)
  File "twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/storage/_base.py", line 477, in runInteraction
    **kwargs
  File "twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "twisted/python/failure.py", line 512, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "synapse/storage/_base.py", line 525, in runWithConnection
    result = yield self._db_pool.runWithConnection(inner_func, *args, **kwargs)
  File "twisted/python/threadpool.py", line 250, in inContext
    result = inContext.theWork()
  File "twisted/python/threadpool.py", line 266, in <lambda>
    inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "twisted/python/context.py", line 122, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "twisted/python/context.py", line 85, in callWithContext
    return func(*args,**kw)
  File "twisted/enterprise/adbapi.py", line 306, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "twisted/python/compat.py", line 464, in reraise
    raise exception.with_traceback(traceback)
  File "twisted/enterprise/adbapi.py", line 297, in _runWithConnection
    result = func(conn, *args, **kw)
  File "synapse/storage/_base.py", line 522, in inner_func
    return func(conn, *args, **kwargs)
  File "synapse/storage/_base.py", line 394, in _new_transaction
    r = func(txn, *args, **kwargs)
  File "synapse/storage/stats.py", line 270, in _fetch_data
    {"room_id": room_id, "token": current_token},
  File "synapse/storage/_base.py", line 598, in _simple_insert_txn
    txn.execute(sql, vals)
  File "synapse/storage/_base.py", line 128, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "synapse/storage/_base.py", line 154, in _do_execute
    return func(sql, *args)

Sentry link: https://sentry.matrix.org/sentry/synapse-matrixorg/issues/758/?

[erikjohnston]

This was caused by a room being in the room_stats_earliest_token and the temporary rooms table. This mean that the server would pull the room from the table, calculate stats and then try and insert, causing the unique constraint violation.

This likely happened if the synapse died between the insert and deletion, since they did not happen in the same transaction. The fix here is to a) remove existing entries in the temporary table that have already been processed and b) do the insert and deletion in one transaction.