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

"No create event in state" because of CardinalityViolation when requesting keys on join #12736

Closed
freshprince opened this issue May 14, 2022 · 9 comments · Fixed by #15423
Closed
Labels
A-Federated-Join joins over federation generally suck O-Occasional Affects or can be seen by some users regularly or most users rarely S-Major Major functionality / product severely impaired, no satisfactory workaround. T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues.

Comments

@freshprince
Copy link

Description

When trying to join #techlore:techlore.net my homeserver fails to store the signing keys which makes it impossible to validate the events which results in SynapseError: 400 - No create event in state.

Steps to reproduce

  • try to join #techlore:techlore.net

After send_join completed synapse started fetching server keys from notary servers:

2022-05-14 16:08:16,304 - synapse.crypto.keyring - 664 - INFO - PerspectivesKeyFetcher-3660 - Requesting keys [_FetchKeyRequest(server_name='techlore.net', minimum_valid_until_ts=1639675292186, key_ids=['ed25519:a_TFLD']), _FetchKeyRequest(server_name='techlore.net', minimum_valid_until_ts=1647717845542, key_ids=['ed25519:a_sAzp']), _FetchKeyRequest(server_name='matrix.satanic.gay', minimum_valid_until_ts=1650299647970, key_ids=['ed25519:a_mPko']), _FetchKeyRequest(server_name='satanic.gay', minimum_valid_until_ts=1650300891594, key_ids=['ed25519:a_roaQ']), _FetchKeyRequest(server_name='pragma-messenger.ch', minimum_valid_until_ts=1651516568031, key_ids=['ed25519:a_KOtB']), _FetchKeyRequest(server_name='matrix.afaregensburg.org', minimum_valid_until_ts=1648935406606, key_ids=['ed25519:a_HOZK']), _FetchKeyRequest(server_name='pixelsafe.io', minimum_valid_until_ts=1644401935122, key_ids=['ed25519:a_fpGA']), _FetchKeyRequest(server_name='davidberthel.de', minimum_valid_until_ts=1640873349151, key_ids=['ed25519:a_AAvs']), _FetchKeyRequest(server_name='silkroadtools.com', minimum_valid_until_ts=1636055258620, key_ids=['ed25519:a_zcuT']), _FetchKeyRequest(server_name='thighhighs.io', minimum_valid_until_ts=1648470955102, key_ids=['ed25519:a_vJls']), _FetchKeyRequest(server_name='nitro.chat', minimum_valid_until_ts=1652480619015, key_ids=['ed25519:a_FXKA']), _FetchKeyRequest(server_name='matrix.arul.io', minimum_valid_until_ts=1641120946935, key_ids=['ed25519:a_sESq']), _FetchKeyRequest(server_name='emri.online', minimum_valid_until_ts=1645585361568, key_ids=['ed25519:a_gdWy']), _FetchKeyRequest(server_name='amogus.ddns.net', minimum_valid_until_ts=1649776218532, key_ids=['ed25519:a_RtOq']), _FetchKeyRequest(server_name='radicle.community', minimum_valid_until_ts=1649875566847, key_ids=['ed25519:a_KMYd']), _FetchKeyRequest(server_name='c2t.uk', minimum_valid_until_ts=1648917415105, key_ids=['ed25519:a_LHVQ']), _FetchKeyRequest(server_name='euclids.ems.host', minimum_valid_until_ts=1641089870075, key_ids=['ed25519:1ja6yE']), _FetchKeyRequest(server_name='matrix.ckto.ovh', minimum_valid_until_ts=1643726213455, key_ids=['ed25519:a_Qusf']), _FetchKeyRequest(server_name='ouchcollective.net', minimum_valid_until_ts=1651173287275, key_ids=['ed25519:a_hCsp']), _FetchKeyRequest(server_name='milkte.ch', minimum_valid_until_ts=1642234632123, key_ids=['ed25519:a_tdzR']), _FetchKeyRequest(server_name='matrix.pixelpaper.org', minimum_valid_until_ts=1644524238066, key_ids=['ed25519:a_PteU']), _FetchKeyRequest(server_name='cutefunny.art', minimum_valid_until_ts=1649211827457, key_ids=['ed25519:a_nwuR']), _FetchKeyRequest(server_name='matrix.aristoi.uk.eu.org', minimum_valid_until_ts=1648921109350, key_ids=['ed25519:a_stvG']), _FetchKeyRequest(server_name='matrix.evildayz.com', minimum_valid_until_ts=1649244061107, key_ids=['ed25519:a_VHMP']), _FetchKeyRequest(server_name='dark.fi', minimum_valid_until_ts=1650293315844, key_ids=['ed25519:a_apab']), _FetchKeyRequest(server_name='laubersheimer.eu', minimum_valid_until_ts=1649748144003, key_ids=['ed25519:a_PHpn']), _FetchKeyRequest(server_name='linuxdelta.com', minimum_valid_until_ts=1641832132678, key_ids=['ed25519:a_Uzcs']), _FetchKeyRequest(server_name='anontier.nl', minimum_valid_until_ts=1652112225052, key_ids=['ed25519:a_heIQ']), _FetchKeyRequest(server_name='usethe.tools', minimum_valid_until_ts=1649724714593, key_ids=['ed25519:a_EaMg']), _FetchKeyRequest(server_name='exonip.de', minimum_valid_until_ts=1637185553134, key_ids=['ed25519:a_epAt']), _FetchKeyRequest(server_name='artemislena.eu', minimum_valid_until_ts=1651609786452, key_ids=['ed25519:a_ibrU']), _FetchKeyRequest(server_name='matrix.lolispace.moe', minimum_valid_until_ts=1649070714482, key_ids=['ed25519:a_hDKj']), _FetchKeyRequest(server_name='asozial.org', minimum_valid_until_ts=1645369021465, key_ids=['ed25519:MeIzWE']), _FetchKeyRequest(server_name='flieger.chat', minimum_valid_until_ts=1648853581752, key_ids=['ed25519:a_gahC']), _FetchKeyRequest(server_name='freehold.earth', minimum_valid_until_ts=1651252333942, key_ids=['ed25519:DM0ecB']), _FetchKeyRequest(server_name='heftyza.uk', minimum_valid_until_ts=1648843721155, key_ids=['ed25519:a_DAlW']), _FetchKeyRequest(server_name='matrix.arsenm.dev', minimum_valid_until_ts=1627187137719, key_ids=['ed25519:a_TzAM']), _FetchKeyRequest(server_name='matrix.arsenm.dev', minimum_valid_until_ts=1627186270675, key_ids=['ed25519:ads3pX']), _FetchKeyRequest(server_name='matrix.honbra.com', minimum_valid_until_ts=1643925616229, key_ids=['ed25519:a_kGkM']), _FetchKeyRequest(server_name='matrix.honbra.com', minimum_valid_until_ts=1643923190348, key_ids=['ed25519:a_wuLu']), _FetchKeyRequest(server_name='bcow.xyz', minimum_valid_until_ts=1634682606188, key_ids=['ed25519:a_KLxZ']), _FetchKeyRequest(server_name='glasgow.social', minimum_valid_until_ts=1651347257898, key_ids=['ed25519:a_fOjb']), _FetchKeyRequest(server_name='matrix-jp.net', minimum_valid_until_ts=1628302623138, key_ids=['ed25519:a_KsRz']), _FetchKeyRequest(server_name='jupiterbroadcasting.com', minimum_valid_until_ts=1651060828131, key_ids=['ed25519:a_moYh']), _FetchKeyRequest(server_name='jaminit.co.uk', minimum_valid_until_ts=1650452596248, key_ids=['ed25519:xuGosy']), _FetchKeyRequest(server_name='jaminit.co.uk', minimum_valid_until_ts=1650366941091, key_ids=['ed25519:aQNzX4tN']), _FetchKeyRequest(server_name='jameskitt616.de', minimum_valid_until_ts=1622463609001, key_ids=['ed25519:a_ICBF']), _FetchKeyRequest(server_name='grahe.eu', minimum_valid_until_ts=1623248427852, key_ids=['ed25519:a_VPIv']), _FetchKeyRequest(server_name='henning.ems.host', minimum_valid_until_ts=1652522619352, key_ids=['ed25519:g4riyM']), _FetchKeyRequest(server_name='bandersnatch.xyz', minimum_valid_until_ts=1651182430175, key_ids=['ed25519:a_ZZfT']), _FetchKeyRequest(server_name='jx-stack.xyz', minimum_valid_until_ts=1647162750728, key_ids=['ed25519:a_pIte']), _FetchKeyRequest(server_name='mint.lgbt', minimum_valid_until_ts=1641813813859, key_ids=['ed25519:a_apXQ']), _FetchKeyRequest(server_name='katietso.dev', minimum_valid_until_ts=1631743289615, key_ids=['ed25519:sByxFP']), _FetchKeyRequest(server_name='jagow15.org', minimum_valid_until_ts=1645816538289, key_ids=['ed25519:a_zPlt']), _FetchKeyRequest(server_name='1w1.one', minimum_valid_until_ts=1650211480722, key_ids=['ed25519:a_DHFf']), _FetchKeyRequest(server_name='matrix.gearlandia.haus', minimum_valid_until_ts=1649828727277, key_ids=['ed25519:a_uIST']), _FetchKeyRequest(server_name='server.matrix4ulm.de', minimum_valid_until_ts=1644602136094, key_ids=['ed25519:a_PkwZ']), _FetchKeyRequest(server_name='maeli.net', minimum_valid_until_ts=1651852084539, key_ids=['ed25519:a_twoa']), _FetchKeyRequest(server_name='lx-is.lol', minimum_valid_until_ts=1651946605268, key_ids=['ed25519:a_CHdj']), _FetchKeyRequest(server_name='lx-is.lol', minimum_valid_until_ts=1650140378238, key_ids=['ed25519:a_fQKG']), _FetchKeyRequest(server_name='manerakai.com', minimum_valid_until_ts=1649574789094, key_ids=['ed25519:a_yPqn']), _FetchKeyRequest(server_name='theprivatebay.tech', minimum_valid_until_ts=1652432006214, key_ids=['ed25519:a_sysc']), _FetchKeyRequest(server_name='theprivatebay.tech', minimum_valid_until_ts=1650406623764, key_ids=['ed25519:a_JQji']), _FetchKeyRequest(server_name='theprivatebay.tech', minimum_valid_until_ts=1650386261138, key_ids=['ed25519:a_reQo']), _FetchKeyRequest(server_name='theprivatebay.tech', minimum_valid_until_ts=1650387813877, key_ids=['ed25519:a_aZuJ']), _FetchKeyRequest(server_name='theprivatebay.tech', minimum_valid_until_ts=1650436092314, key_ids=['ed25519:a_FWQx']), _FetchKeyRequest(server_name='matrix.secureprivacy.tech', minimum_valid_until_ts=1635508381648, key_ids=['ed25519:a_CznT']), _FetchKeyRequest(server_name='matrix.nerd2nerd.org', minimum_valid_until_ts=1643985898072, key_ids=['ed25519:a_CbpD']), _FetchKeyRequest(server_name='highdesertliving.online', minimum_valid_until_ts=1629343279335, key_ids=['ed25519:a_vWeP']), _FetchKeyRequest(server_name='sharp.ems.host', minimum_valid_until_ts=1631801918264, key_ids=['ed25519:YSLy0M']), _FetchKeyRequest(server_name='30bq4kgcbdtr885himilrvbzy.ems.host', minimum_valid_until_ts=1647994337120, key_ids=['ed25519:OjJ7cD']), _FetchKeyRequest(server_name='digitale-gesellschaft.ch', minimum_valid_until_ts=1650047297177, key_ids=['ed25519:a_MVfW']), _FetchKeyRequest(server_name='mcginn.us', minimum_valid_until_ts=1651450163293, key_ids=['ed25519:a_qmcP']), _FetchKeyRequest(server_name='baytech.community', minimum_valid_until_ts=1638903288790, key_ids=['ed25519:a_zqgF']), _FetchKeyRequest(server_name='envs-is-normie.glitch.me:443', minimum_valid_until_ts=1649805474691, key_ids=['ed25519:6trm82dk']), _FetchKeyRequest(server_name='envs-is-normie.glitch.me:443', minimum_valid_until_ts=1650180166934, key_ids=['ed25519:VDpN6wRv']), _FetchKeyRequest(server_name='hackwithht.ml', minimum_valid_until_ts=1641687385974, key_ids=['ed25519:5Bijr19Y']), _FetchKeyRequest(server_name='thisisplate.hackwithht.ml', minimum_valid_until_ts=1651098441596, key_ids=['ed25519:FAhjA2']), _FetchKeyRequest(server_name='bytes4u.de', minimum_valid_until_ts=1648981635499, key_ids=['ed25519:a_cZkY']), _FetchKeyRequest(server_name='fulltermprivacy.com', minimum_valid_until_ts=1650593180797, key_ids=['ed25519:a_kZFJ']), _FetchKeyRequest(server_name='stage3.die.one', minimum_valid_until_ts=1637202185756, key_ids=['ed25519:FSnuPg']), _FetchKeyRequest(server_name='mtrx.nz', minimum_valid_until_ts=1648881292739, key_ids=['ed25519:a_jbcK']), _FetchKeyRequest(server_name='faelix.im', minimum_valid_until_ts=1650002170721, key_ids=['ed25519:a_pOLQ']), _FetchKeyRequest(server_name='sammydepresso.ems.host', minimum_valid_until_ts=1632400467488, key_ids=['ed25519:XGP6Zk']), _FetchKeyRequest(server_name='matrix.juggler.jp', minimum_valid_until_ts=1652421901589, key_ids=['ed25519:a_jQrd']), _FetchKeyRequest(server_name='chat.trot.soy', minimum_valid_until_ts=1640835319491, key_ids=['ed25519:KhHn6N']), _FetchKeyRequest(server_name='gentoo.guru', minimum_valid_until_ts=1649965050262, key_ids=['ed25519:a_FqxS']), _FetchKeyRequest(server_name='horton.ems.host', minimum_valid_until_ts=1640316872959, key_ids=['ed25519:NNKhSA']), _FetchKeyRequest(server_name='lucidminion.com', minimum_valid_until_ts=1641938942897, key_ids=['ed25519:a_rPGw']), _FetchKeyRequest(server_name='lucidminion.com', minimum_valid_until_ts=1641556497536, key_ids=['ed25519:a_zcuT']), _FetchKeyRequest(server_name='matrix.nogafam.es', minimum_valid_until_ts=1650716072081, key_ids=['ed25519:a_XEnV']), _FetchKeyRequest(server_name='freedom.madliberteaproject.com', minimum_valid_until_ts=1626782041788, key_ids=['ed25519:a_CSfD']), _FetchKeyRequest(server_name='freiburg.social', minimum_valid_until_ts=1644268488655, key_ids=['ed25519:a_ibXO']), _FetchKeyRequest(server_name='tristans.cloud', minimum_valid_until_ts=1650214891099, key_ids=['ed25519:a_aQFy']), _FetchKeyRequest(server_name='kreatea.space', minimum_valid_until_ts=1643256439297, key_ids=['ed25519:a_wxTS']), _FetchKeyRequest(server_name='matrix.conebros.com', minimum_valid_until_ts=1623045754506, key_ids=['ed25519:a_neeN']), _FetchKeyRequest(server_name='berlin.freedombox.rocks', minimum_valid_until_ts=1621492928989, key_ids=['ed25519:a_tNXr']), _FetchKeyRequest(server_name='homespot.dev', minimum_valid_until_ts=1640184956527, key_ids=['ed25519:a_wtYW']), _FetchKeyRequest(server_name='chat.xbdm.fun', minimum_valid_until_ts=1646710995021, key_ids=['ed25519:a_FPHf']), _FetchKeyRequest(server_name='data.haus', minimum_valid_until_ts=1650819207870, key_ids=['ed25519:a_Bnxi'])] from notary server matrix.org
[...]
2022-05-14 16:08:23,336 - synapse.crypto.keyring - 623 - ERROR - PerspectivesKeyFetcher-3660 - Unable to get key from 'matrix.org': CardinalityViolation ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
Traceback (most recent call last):
  File "/usr/lib/python3.10/site-packages/synapse/crypto/keyring.py", line 615, in get_key
    return await self.get_server_verify_key_v2_indirect(
  File "/usr/lib/python3.10/site-packages/synapse/crypto/keyring.py", line 737, in get_server_verify_key_v2_indirect
    await self.store.store_server_verify_keys(
  File "/usr/lib/python3.10/site-packages/synapse/storage/databases/main/keys.py", line 126, in store_server_verify_keys
    await self.db_pool.simple_upsert_many(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 1309, in simple_upsert_many
    await self.runInteraction(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 814, in runInteraction
    return await delay_cancellation(_runInteraction())
  File "/usr/lib/python3.10/site-packages/twisted/internet/defer.py", line 1656, in _inlineCallbacks
    result = current_context.run(
  File "/usr/lib/python3.10/site-packages/twisted/python/failure.py", line 489, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 786, in _runInteraction
    result = await self.runWithConnection(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 909, in runWithConnection
    return await make_deferred_yieldable(
  File "/usr/lib/python3.10/site-packages/twisted/python/threadpool.py", line 244, in inContext
    result = inContext.theWork()  # type: ignore[attr-defined]
  File "/usr/lib/python3.10/site-packages/twisted/python/threadpool.py", line 260, in <lambda>
    inContext.theWork = lambda: context.call(  # type: ignore[attr-defined]
  File "/usr/lib/python3.10/site-packages/twisted/python/context.py", line 117, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/usr/lib/python3.10/site-packages/twisted/python/context.py", line 82, in callWithContext
    return func(*args, **kw)
  File "/usr/lib/python3.10/site-packages/twisted/enterprise/adbapi.py", line 282, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 902, in inner_func
    return func(db_conn, *args, **kwargs)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 650, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 1345, in simple_upsert_many_txn
    return self.simple_upsert_many_txn_native_upsert(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 1441, in simple_upsert_many_txn_native_upsert
    txn.execute_values(sql, args, fetch=False)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 331, in execute_values
    return self._do_execute(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 379, in _do_execute
    return func(sql, *args, **kwargs)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 332, in <lambda>
    lambda the_sql: execute_values(self.txn, the_sql, values, fetch=fetch),
  File "/usr/lib/python3.10/site-packages/psycopg2/extras.py", line 1270, in execute_values
    cur.execute(b''.join(parts))
psycopg2.errors.CardinalityViolation: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

This happens for all notary servers.

Then when processing events this happens:

2022-05-14 16:07:49,850 - synapse.federation.federation_client - 905 - INFO - POST-263685 - Processing from send_join 8473 events
2022-05-14 16:08:30,948 - synapse.federation.federation_base - 70 - WARNING - POST-263685 - Signature check failed for $F86FNSggvXyWWs-t32sv0DEbtrk_fQjPAU88kxHSNhY: 403: event id $F86FNSggvXyWWs-t32sv0DEbtrk_fQjPAU88kxHSNhY: unable to verify signature for sender euclids.ems.host: 401: Failed to find any key to satisfy: _FetchKeyRequest(server_name='euclids.ems.host', minimum_valid_until_ts=1641089870075, key_ids=['ed25519:1ja6yE'])
2022-05-14 16:08:30,967 - synapse.federation.federation_base - 70 - WARNING - POST-263685 - Signature check failed for $AbR3tsqRbdeLpnBchOyAJe572yY8hZgIsU6w0rHQINc: 403: event id $AbR3tsqRbdeLpnBchOyAJe572yY8hZgIsU6w0rHQINc: unable to verify signature for sender jameskitt616.de: 401: Failed to find any key to satisfy: _FetchKeyRequest(server_name='jameskitt616.de', minimum_valid_until_ts=1622463609001, key_ids=['ed25519:a_ICBF'])
2022-05-14 16:08:30,983 - synapse.federation.federation_base - 70 - WARNING - POST-263685 - Signature check failed for $DbLaHz8U3XY_xXxuV5E2sT1e1_h6SsqgdMBW2dLIzTM: 403: event id $DbLaHz8U3XY_xXxuV5E2sT1e1_h6SsqgdMBW2dLIzTM: unable to verify signature for sender heftyza.uk: 401: Failed to find any key to satisfy: _FetchKeyRequest(server_name='heftyza.uk', minimum_valid_until_ts=1648843721155, key_ids=['ed25519:a_DAlW'])
2022-05-14 16:08:30,990 - synapse.federation.federation_base - 70 - WARNING - POST-263685 - Signature check failed for $CP-UwrB1tT1WRp0UpFnIu58Qz62kOU9Ig60aaAu7FMA: 403: event id $CP-UwrB1tT1WRp0UpFnIu58Qz62kOU9Ig60aaAu7FMA: unable to verify signature for sender heftyza.uk: 401: Failed to find any key to satisfy: _FetchKeyRequest(server_name='heftyza.uk', minimum_valid_until_ts=1648827272203, key_ids=['ed25519:a_DAlW'])
2022-05-14 16:08:30,996 - synapse.federation.federation_base - 70 - WARNING - POST-263685 - Signature check failed for $Qi97tZ5I04wQ_1P9RTiwtLl2ApQXNDnu0A1pSueTDcw: 403: event id $Qi97tZ5I04wQ_1P9RTiwtLl2ApQXNDnu0A1pSueTDcw: unable to verify signature for sender heftyza.uk: 401: Failed to find any key to satisfy: _FetchKeyRequest(server_name='heftyza.uk', minimum_valid_until_ts=1648827136243, key_ids=['ed25519:a_DAlW'])
2022-05-14 16:08:31,017 - synapse.federation.federation_base - 70 - WARNING - POST-263685 - Signature check failed for $7-gbgUZ4GeuZwC2heVtDPbVcIaP_NJdtFPvLfWF-czw: 403: event id $7-gbgUZ4GeuZwC2heVtDPbVcIaP_NJdtFPvLfWF-czw: unable to verify signature for sender 30bq4kgcbdtr885himilrvbzy.ems.host: 401: Failed to find any key to satisfy: _FetchKeyRequest(server_name='30bq4kgcbdtr885himilrvbzy.ems.host', minimum_valid_until_ts=1647994337120, key_ids=['ed25519:OjJ7cD'])
2022-05-14 16:08:31,024 - synapse.federation.federation_base - 70 - WARNING - POST-263685 - Signature check failed for $kXrb0xmrZqFv1v2C_IW0sY-hWTlaTrrsXtXjI16ZELE: 403: event id $kXrb0xmrZqFv1v2C_IW0sY-hWTlaTrrsXtXjI16ZELE: unable to verify signature for sender 30bq4kgcbdtr885himilrvbzy.ems.host: 401: Failed to find any key to satisfy: _FetchKeyRequest(server_name='30bq4kgcbdtr885himilrvbzy.ems.host', minimum_valid_until_ts=1636047358759, key_ids=['ed25519:OjJ7cD'])
[...]
2022-05-14 16:08:56,194 - synapse.http.server - 104 - INFO - POST-263685 - <XForwardedForRequest at 0x7f93e6b7c640 method='POST' uri='/_matrix/client/r0/join/%23techlore%3Atechlore.net' clientproto='HTTP/1.1' site='8008'> SynapseError: 400 - No create event in state

Here is the failing SQL statement from postgresql log:

2022-05-14T14:08:23.30667 daemon.info: postgres14: 2022-05-14 14:08:23.305 GMT [31559] ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
2022-05-14T14:08:23.30685 daemon.info: postgres14: 2022-05-14 14:08:23.305 GMT [31559] HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
2022-05-14T14:08:23.30693 daemon.info: postgres14: 2022-05-14 14:08:23.305 GMT [31559] STATEMENT:  INSERT INTO server_signature_keys (server_name, key_id, from_server, ts_added_ms, ts_valid_until_ms, verify_key) VALUES ('usethe.tools','ed25519:a_EaMg','matrix.org',1652537297072,1652508774657,'\x9089cd802eb37f627b35f2cab087abd5e5f31f289a03761d3a07ef4067f67b65'::bytea),('flieger.chat','ed25519:a_gahC','matrix.org',1652537297072,1652570988716,'\xea6c4d388e8eec25cf905c6df212924c39dbf08883b114e6191db76fddcb8f80'::bytea),('techlore.net','ed25519:a_TFLD','matrix.org',1652537297072,1639808703392,'\x60ea01842844e11f64b25bd3028dfc72cdafae1682b982b6f3e181dbe815aa85'::bytea),('theprivatebay.tech','ed25519:a_FWQx','matrix.org',1652537297072,1650493712948,'\xdca9d16a0a51fd4eb595db92d4df3753dd495583004d0a2346e0554d8000357e'::bytea),('anontier.nl','ed25519:a_heIQ','matrix.org',1652537297072,1652573697214,'\xe24003bd0bf853c040794fbf75c2972bfa04c33815b6a2798d8fe28d936954fd'::bytea),('katietso.dev','ed25519:sByxFP','matrix.org',1652537297072,1646...
2022-05-14T14:08:23.30724 daemon.info: postgres14: 181650627,'\x3bef9175b7023f4a35fe02a9d6e822844cbbaa32e37449328cf9ebb45002536f'::bytea),('fulltermprivacy.com','ed25519:a_kZFJ','matrix.org',1652537297072,1652561332351,'\xe65636b9ef3f28506eaf49b667c9de6b103d911340571406f0be785a78979feb'::bytea),('silkroadtools.com','ed25519:a_zcuT','matrix.org',1652537297072,1636288401812,'\xbfe02723a2e71cb87a8f353594aa1f69a9d0d5f2794b2ee024dde0d49555c59e'::bytea),('bytes4u.de','ed25519:a_cZkY','matrix.org',1652537297072,1652608366351,'\xbe8b6282b72547a49c4d87182cfe69bf6b3e2d17b51a81491df60c98dab0487d'::bytea),('matrix.nogafam.es','ed25519:a_XEnV','matrix.org',1652537297072,1652587217031,'\x0053481c5d5637f20e571fb041dc7ff6c5380581a5eb7071778e8179a7ac4597'::bytea),('theprivatebay.tech','ed25519:a_sysc','matrix.org',1652537297072,1652605091189,'\xd1ac6803f1c710d02250b56d30bd8663cf63815843cddc4273b4aace16fa664b'::bytea),('freedom.madliberteaproject.com','ed25519:a_CSfD','matrix.org',1652537297072,1644597362301,'\xcb1b8432903781933e439c5f9b1cda961f...
2022-05-14T14:08:23.30747 daemon.info: postgres14: 1d427a5368696b5db74f66f363ddbf'::bytea),('satanic.gay','ed25519:a_roaQ','matrix.org',1652537297072,1650936359938,'\x9415beeec6e8826ee817e376c36ff929dab26e27544a24a5471572a400f00ef1'::bytea),('bandersnatch.xyz','ed25519:a_ZZfT','matrix.org',1652537297072,1652564648133,'\x91cb485eca1cc66ff4b0602164f3f6e536ff9070debd1bc3bd10f07259ee8c5b'::bytea),('faelix.im','ed25519:a_pOLQ','matrix.org',1652537297072,1652602772285,'\x5d7fb37d433ee736357fd93dafe3df7ab063570990793f1438519c19271747a7'::bytea),('radicle.community','ed25519:a_KMYd','matrix.org',1652537297072,1652550949990,'\x75d7ac666ce369c660c612b32f13f3490b1920e04f61d3b992e8bbb98cc87ff3'::bytea),('mint.lgbt','ed25519:a_apXQ','matrix.org',1652537297072,1647084358110,'\xfa82ab066179dba399ed0cad21a3614b89fe26ce3c0516ce97e98c6cdc76bc14'::bytea),('bcow.xyz','ed25519:a_KLxZ','matrix.org',1652537297072,1652544023838,'\x9f9754357a1ce89f94ddeef966ff4fceec25db19d4edfa47e9cdde81b548d854'::bytea),('bcow.xyz','ed25519:2bpeHI','matrix.org',165253...
2022-05-14T14:08:23.30781 daemon.info: postgres14: 7297072,1621261273000,'\x1c81dbda4210f5518e11dae89549d9296c1ba21b2c07fbf1b8ca19bf5ec598b7'::bytea),('kreatea.space','ed25519:a_wxTS','matrix.org',1652537297072,1652538380152,'\x1901d974309e476478d1a274229ca66436b6fc1dd4082e092b22cb9a48ea5b36'::bytea),('linuxdelta.com','ed25519:a_Uzcs','matrix.org',1652537297072,1643894738652,'\x561b777b2bf8e0dca3d0ddd44bec17e1c29a693b4d627d4fd1a0c661a173fdbe'::bytea),('techlore.net','ed25519:a_sAzp','matrix.org',1652537297072,1652577818278,'\x38d64697b4c58f13fc3f08315c3dd22be6b00e69468b96a2030a5b18c0af8533'::bytea),('matrix.arsenm.dev','ed25519:ads3pX','matrix.org',1652537297072,1627788036360,'\x4c63a26585c73045820faae0aa226176b83b3b89cde3cf230cf723fd5f5bd312'::bytea),('matrix.arsenm.dev','ed25519:a_TzAM','matrix.org',1652537297072,1601024554498,'\xe05be8be8562f46ecc210b8bb5fe35e41336664668c91ed747bc290475d22fdc'::bytea),('envs-is-normie.glitch.me:443','ed25519:VDpN6wRv','matrix.org',1652537297072,1650784870640,'\x2f8e5ec1d96324de7fc1685314b6cc...
2022-05-14T14:08:23.30810 daemon.info: postgres14: d47fd5fe7683f8ec4183f9fe6241eea45e'::bytea),('mtrx.nz','ed25519:a_jbcK','matrix.org',1652537297072,1652555838966,'\x9962d052dc5d82e197c0c9aff29918dd073dea1f0fab003f1c6e83ae80d9ec82'::bytea),('thisisplate.hackwithht.ml','ed25519:FAhjA2','matrix.org',1652537297072,1652944204304,'\x76f322351c1f6e7670d6bbef58173260ad953f08b9e793b73588a33c39539bed'::bytea),('envs-is-normie.glitch.me:443','ed25519:6trm82dk','matrix.org',1652537297072,1650408450543,'\xdce848349d274077ca36f36299c99bc29e0576d4b5820bf8f365664502914a46'::bytea),('digitale-gesellschaft.ch','ed25519:a_MVfW','matrix.org',1652537297072,1652574444459,'\xedd4e7c8ecb9d0013b856ae06ed9694269168c79d9944626062ec6c72183d163'::bytea),('ouchcollective.net','ed25519:a_hCsp','matrix.org',1652537297072,1652575100950,'\xa23fc6f1c936b529cf3c6fe41b6361166238ddac93bd1ccb3bb42e6a666e475c'::bytea),('theprivatebay.tech','ed25519:a_JQji','matrix.org',1652537297072,1650492462164,'\xcec25674ea3b6dd6ab54efae1febeac9abf2e107e8b30659bc466013dd6ed004':...
2022-05-14T14:08:23.30834 daemon.info: postgres14: :bytea),('davidberthel.de','ed25519:a_AAvs','matrix.org',1652537297072,1652568951391,'\xff65abb4100c785ee0d11e95517a8ef4943a03de101347f8815c106d320fd6be'::bytea),('lucidminion.com','ed25519:a_rPGw','matrix.org',1652537297072,1642001902140,'\x49290bf9b312043ac6ccf2fa0e79b573787ed5e023a16887496e085a96423214'::bytea),('jx-stack.xyz','ed25519:a_pIte','matrix.org',1652537297072,1647247368858,'\xcf8b787efd347c58b287b3dcf5a938a6fb7a73c6c94dedf8f335b23226dac9af'::bytea),('matrix.evildayz.com','ed25519:a_VHMP','matrix.org',1652537297072,1652549357565,'\x337aeb3513d7b1d54d4d93857c8d9eae13f84c35db70d98197f39ddf2d55d9ea'::bytea),('thighhighs.io','ed25519:a_vJls','matrix.org',1652537297072,1649050331849,'\x1740cc13cd6b5641f8a28ecd5cb39971e0b27fa43b415604bd6a4a68c5c27fa2'::bytea),('matrix.arsenm.dev','ed25519:a_TzAM','matrix.org',1652537297072,1627273239740,'\xe05be8be8562f46ecc210b8bb5fe35e41336664668c91ed747bc290475d22fdc'::bytea),('jaminit.co.uk','ed25519:xuGosy','matrix.org',165253729707...
2022-05-14T14:08:23.30859 daemon.info: postgres14: 2,1652733233464,'\x54ec2213b3b7616078e72b43853a792153e9e7904c4482546a1a1167ee911cb6'::bytea),('c2t.uk','ed25519:a_LHVQ','matrix.org',1652537297072,1649595281803,'\xe5b5d8083dfd16457909963bc1a382e7e5ba897392db9c109edf250ffa7cf066'::bytea),('matrix.juggler.jp','ed25519:a_jQrd','matrix.org',1652537297072,1652560192571,'\x7d165d961c15c4b5c030b2abbe311ddfb5564d2f15a8a6b1632f216126e784da'::bytea),('30bq4kgcbdtr885himilrvbzy.ems.host','ed25519:OjJ7cD','matrix.org',1652537297072,1649053641782,'\x743513634abf6a417c7ba1e7fa93c5cc85d9a7861d369b23d88451992b1b2da9'::bytea),('matrix.afaregensburg.org','ed25519:a_HOZK','matrix.org',1652537297072,1652581045591,'\x2c5f93ab1f2316aaee0b73e19df2ce0ca2d53c75f1fa5331b81d3ff6150b2fa1'::bytea),('artemislena.eu','ed25519:a_ibrU','matrix.org',1652537297072,1652565637722,'\xf64000896d31d28fb4a4efb760a6a400f59492de7a297c71d83e675ee26d1f76'::bytea),('matrix.aristoi.uk.eu.org','ed25519:a_stvG','matrix.org',1652537297072,1649280145427,'\x7af6be5d42567b7bdb09...
2022-05-14T14:08:23.30893 daemon.info: postgres14: c8cda6f8b2fad171be853a0d9111dcb5ea2e60aedf82'::bytea),('tristans.cloud','ed25519:a_aQFy','matrix.org',1652537297072,1652596367803,'\xadcd3614b95db0640cca24858e314a0e2664f9d76d11bbfe1e6429b8a0848477'::bytea),('data.haus','ed25519:a_Bnxi','matrix.org',1652537297072,1652585917384,'\xdfe4227e5d6dfa99bc4fd9ab2974ede8a0efdb13f96592d52f295a43b3e9e9e5'::bytea),('asozial.org','ed25519:MeIzWE','matrix.org',1652537297072,1652648081742,'\x6f1ef68f10f2d1aff20eeb0da8486cc887e6842e61d232608fcc1cfcbae42725'::bytea),('stage3.die.one','ed25519:FSnuPg','matrix.org',1652537297072,1643741271469,'\x366ef7799979ef28a8feb3355abb8defd4f2a9c4ef9377e4866de34fa10a2126'::bytea),('matrix.ckto.ovh','ed25519:a_Qusf','matrix.org',1652537297072,1652504359814,'\xc6abf4104e7171889743ddc29196b5954e729e4c50f43cb1fe3a7dfd6c1a0f15'::bytea),('manerakai.com','ed25519:a_yPqn','matrix.org',1652537297072,1652601621617,'\xc959373a9eda053f20550e85bbd5bac7b0466e54813359594edf4b4fc2289b1b'::bytea),('amogus.ddns.net','ed25519:...
2022-05-14T14:08:23.30923 daemon.info: postgres14: a_RtOq','matrix.org',1652537297072,1649847213004,'\x40fb416835e403d98a2329a47a4c4445976ab1f9ae701738c9063293d22978f0'::bytea),('grahe.eu','ed25519:a_VPIv','matrix.org',1652537297072,1652577683790,'\x497d5cea5a8e9b17c100b66867e8a08f937e0c472c2e959e056f97d20ca1540e'::bytea),('henning.ems.host','ed25519:g4riyM','matrix.org',1652537297072,1652580435901,'\x91dd74e77549a0c2c510ea2713e2336fd02aba9feffc02cfbe14ffd3ca7ea4a7'::bytea),('theprivatebay.tech','ed25519:a_reQo','matrix.org',1652537297072,1650465056841,'\x63a4c4add8973a2ceac2427daa6d47199f30b36ab1e1af905e32b46458975db6'::bytea),('chat.trot.soy','ed25519:KhHn6N','matrix.org',1652537297072,1645840339073,'\xff5102bbb8159992fece3c6a994c3ccfd75f0ac951bed07a12c60f5fed60f9d1'::bytea),('euclids.ems.host','ed25519:1ja6yE','matrix.org',1652537297072,1643046766208,'\x9c7f81355923e56077d4ac204f37676c91a5d64afa5fc3dff23e1a80e13682c4'::bytea),('lucidminion.com','ed25519:a_zcuT','matrix.org',1652537297072,1641590753640,'\xbfe02723a2e71cb87a8f...
2022-05-14T14:08:23.30988 daemon.info: postgres14: 353594aa1f69a9d0d5f2794b2ee024dde0d49555c59e'::bytea),('milkte.ch','ed25519:a_tdzR','matrix.org',1652537297072,1647721541289,'\xd314323c9383493cc545bb3698f75e74623a8fa6b536d9eaf63049bf27191cc5'::bytea),('milkte.ch','ed25519:a_Bmsr','matrix.org',1652537297072,1628643113205,'\xaf6de15a4b3dff7d117f67eb5aaa76c92eb5f35bd30e49e5372d4a048ee14600'::bytea),('matrix.gearlandia.haus','ed25519:a_uIST','matrix.org',1652537297072,1651776124282,'\xe0f7551a2adb322dcb8f8ab2ac3821c943903cb059387e936d7bdd0d65a9ad1f'::bytea),('dark.fi','ed25519:a_apab','matrix.org',1652537297072,1652551850652,'\x83fb2358f383fbce037ae70e4c7fd9dcbba4e991ed23ee4c652cb4c751a1084e'::bytea),('jupiterbroadcasting.com','ed25519:a_moYh','matrix.org',1652537297072,1652582419931,'\x3f796084fea49ad0a8acd78e71f10cb57501790ec4d2b4d8ae85d17d478ce9c1'::bytea),('matrix.honbra.com','ed25519:a_kGkM','matrix.org',1652537297072,1645858225712,'\xab6ac2e2a891cbc6eea6657598cd9848d32c8414212df30ce7ab86a59476178f'::bytea),('lx-is.lol','ed2...
2022-05-14T14:08:23.31015 daemon.info: postgres14: 5519:a_fQKG','matrix.org',1652537297072,1650226441109,'\x7d3b7b13eee9b139583b5ca32ec8a958824fb8f5e7b877127e8156ce369e99f7'::bytea),('maeli.net','ed25519:a_twoa','matrix.org',1652537297072,1652579965864,'\x3708e0092c64bdbf487a3537fee76d5d9393ef497fbb1bfa9fad5fa52ef20101'::bytea),('emri.online','ed25519:a_gdWy','matrix.org',1652537297072,1652411314510,'\x02b24079fb1b1be7e10a87b7db6390603ebbc8dd210f75423de4c338c2defffc'::bytea),('matrix.honbra.com','ed25519:a_wuLu','matrix.org',1652537297072,1643967331425,'\xc12189711895843b678c846286cafc0451fd19ddcf2e182c95d6bc3cca556061'::bytea),('jagow15.org','ed25519:a_zPlt','matrix.org',1652537297072,1652571426179,'\x3bcee8db2fb2ac8c33d25e92d42dbfff617a5b0bf347ffed691249c5ae556270'::bytea),('jaminit.co.uk','ed25519:aQNzX4tN','matrix.org',1652537297072,1650549202720,'\x9a8b688a911d513b3384a8744fe4139cb2e2988187cb1d9a5e92636e5eba1833'::bytea),('freehold.earth','ed25519:DM0ecB','matrix.org',1652537297072,1652571922294,'\x7c7c3d8e723f60d100fcc886...
2022-05-14T14:08:23.31048 daemon.info: postgres14: c95d770c0caf4ac677b6ee61e82334aa2786188f'::bytea),('1w1.one','ed25519:a_DHFf','matrix.org',1652537297072,1650223352178,'\xda2a04c7047142f499e21144c47c5c54d89bf6eea2c1d8dc3238d7ecd8e9c332'::bytea),('pragma-messenger.ch','ed25519:a_KOtB','matrix.org',1652537297072,1652574962804,'\xeffbfe96fe47eeb937334009c5c8c331c5c9fdd2d696a508872f3690997253ec'::bytea),('matrix.lolispace.moe','ed25519:a_hDKj','matrix.org',1652537297072,1652588259383,'\xe00f0d4da37cf38385ffdee3c2e7d5f46fba50aa9b526a309fb38601149f145c'::bytea),('heftyza.uk','ed25519:a_DAlW','matrix.org',1652537297072,1648853724886,'\x5dc04ca95284e66ac7c75a6678fc4d19f7f9d0ca56505af468df406ced5b5946'::bytea),('homespot.dev','ed25519:a_wtYW','matrix.org',1652537297072,1652603277739,'\x761ac94e6ce58b55be022266940a8a1b50825224edfa9648f5bbf8dfcb1af4e5'::bytea),('horton.ems.host','ed25519:NNKhSA','matrix.org',1652537297072,1652551652600,'\x8b575f839599be7ab6fbe31a4b2d2cb0b44c0d24b7ec5cc62e2846eafaf95cb6'::bytea),('server.matrix4ulm.de','...
2022-05-14T14:08:23.31079 daemon.info: postgres14: ed25519:a_PkwZ','matrix.org',1652537297072,1652597616285,'\x4415bc16a33d65fa73202218a0bc00b7329f80d2e847da44be13da8b5c3f885b'::bytea),('freiburg.social','ed25519:a_ibXO','matrix.org',1652537297072,1652551668338,'\xba05ee32a052104270cf9d404cd4d6651cda2b652f7cc285657f19db2c3cf284'::bytea),('sharp.ems.host','ed25519:YSLy0M','matrix.org',1652537297072,1652590815474,'\x20fac0685299fc1a4da4977277139247e54e8dba5a3b3ab602664511cc14676c'::bytea),('exonip.de','ed25519:a_epAt','matrix.org',1652537297072,1648632544031,'\x81710bf059217d62f702e7e8b589c94645531aa6462dce5758b171e633e9e611'::bytea),('matrix.pixelpaper.org','ed25519:a_PteU','matrix.org',1652537297072,1650900362167,'\xc0fc8fb7afd37d3938333703d63817b2331d7372a8c580324d1a9230f8491dc5'::bytea),('chat.xbdm.fun','ed25519:a_FPHf','matrix.org',1652537297072,1648492395917,'\xe0bbd10202efc8164c713bab54454c4e66cd514ec8ff145f449fe54775be0371'::bytea),('sammydepresso.ems.host','ed25519:XGP6Zk','matrix.org',1652537297072,1632862501688,'\xfc0d...
2022-05-14T14:08:23.31104 daemon.info: postgres14: 8457373da44d7b50a4e6a1756aebf4720495bdaaef9f8aa9a073a40055a2'::bytea),('baytech.community','ed25519:a_zqgF','matrix.org',1652537297072,1652589458960,'\xe09cd456d2bf3d519a97e4f0612d94bd36cf5d5df8bd5c45834f66ae46cd60ff'::bytea),('nitro.chat','ed25519:a_FXKA','matrix.org',1652537297072,1652555659742,'\xe725a4ad326e95ae1772ec20b7de59da853b7662af597fa7c1cdec05ef173a82'::bytea),('matrix.nerd2nerd.org','ed25519:a_CbpD','matrix.org',1652537297072,1652602791924,'\xa407b7fc7c203ecc99342ea3008f3d724d8479f916454d0c9445c5ad7c755666'::bytea),('jameskitt616.de','ed25519:a_ICBF','matrix.org',1652537297072,1623318138102,'\xf13617c2690da86734ddb8834a2ade10476056623b453758c9918926ffdbd194'::bytea),('matrix.arul.io','ed25519:a_sESq','matrix.org',1652537297072,1641633715896,'\xba887a516d7f7eda7468201c03908c80a4daf7f9b1783a07377ce0e766e830b7'::bytea),('laubersheimer.eu','ed25519:a_PHpn','matrix.org',1652537297072,1652545938728,'\x655214531b9b08256f1ec9e9ce76eee68a0310f2597d6f2d95964f967b22131f'::byt...
2022-05-14T14:08:23.31129 daemon.info: postgres14: ea),('hackwithht.ml','ed25519:5Bijr19Y','matrix.org',1652537297072,1651260495169,'\xabf565bd7d61b5d80b93887330d9c1cef4ad5d2e1dee3fe938a978227673419d'::bytea),('matrix.secureprivacy.tech','ed25519:a_CznT','matrix.org',1652537297072,1637305576813,'\x1e23424788fa4d298fbc6e0c003f4ea2f6a98ddfff42e47f3f641dcda512c662'::bytea),('gentoo.guru','ed25519:a_FqxS','matrix.org',1652537297072,1650062499686,'\xd12f44f8132582bbbaa0420a242bb5cf58406760ce3d6df5aa8792737ca22aaf'::bytea),('lx-is.lol','ed25519:a_CHdj','matrix.org',1652537297072,1651983178816,'\x7dd68b7ccba788eb774bc3e336c9af54a8f10c61fee3ffe6163695a68bfadce9'::bytea),('matrix.satanic.gay','ed25519:a_mPko','matrix.org',1652537297072,1650385667440,'\xd530344b51c9586997ae1be1fb88845113b00672b8ee6f4387f952bd82578d36'::bytea),('pixelsafe.io','ed25519:a_fpGA','matrix.org',1652537297072,1652300427394,'\xc7c0157c56638b0bf6d5812ff4c6cb79f90c2b27d040d857f251a11741deaafd'::bytea),('highdesertliving.online','ed25519:a_vWeP','matrix.org',1652537...
2022-05-14T14:08:23.31161 daemon.info: postgres14: 297072,1629428339899,'\xfb40c8e4ebb73ee53c7398017096b2aeab15c1bbe7975c971f189d914c680507'::bytea),('glasgow.social','ed25519:a_fOjb','matrix.org',1652537297072,1652563096833,'\x6ca0212571e18bdc8874fa74cc6a5ee6aae35705283a8815dac3b1234bb26e13'::bytea),('cutefunny.art','ed25519:a_nwuR','matrix.org',1652537297072,1652601371682,'\xa22f34ec081d49b9540aa8d23ee48fa482e0fcd07b160f9856eae73d08327343'::bytea),('matrix-jp.net','ed25519:a_KsRz','matrix.org',1652537297072,1632496725551,'\x29206f1de356d35dd81a64df5eaa7b98e6e2416a42e5ff30d1dc4a97c755eb86'::bytea),('berlin.freedombox.rocks','ed25519:a_tNXr','matrix.org',1652537297072,1640223613795,'\x732e6cd2e3d1838eea554f551406f49ffbea5a23fea6531397d9dc42b5cfe054'::bytea) ON CONFLICT (server_name, key_id) DO UPDATE SET from_server=EXCLUDED.from_server, ts_added_ms=EXCLUDED.ts_added_ms, ts_valid_until_ms=EXCLUDED.ts_valid_until_ms, verify_key=EXCLUDED.verify_key

Version information

  • Homeserver: dunstkreis.ch

  • Version:

{
  "server_version": "1.59.0rc1",
  "python_version": "3.10.4"
}
  • Install method:
    package manager

  • Platform:
    void linux 5.15.32 x86_64 postgresql 14.1

@DMRobertson DMRobertson added the T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues. label May 16, 2022
@DMRobertson
Copy link
Contributor

Thanks for the thorough report. Are you able to roll back your homeserver to an older version, to see if this is introduced in the 1.59 release candidate?

@DMRobertson DMRobertson added S-Major Major functionality / product severely impaired, no satisfactory workaround. A-Federated-Join joins over federation generally suck labels May 16, 2022
@freshprince
Copy link
Author

I get the same behaviour after downgrading to 1.58.1.

Here's the traceback:

2022-05-16 14:37:43,498 - synapse.crypto.keyring - 623 - ERROR - PerspectivesKeyFetcher-0 - Unable to get key from 'matrix.org': CardinalityViolation ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
Traceback (most recent call last):
  File "/usr/lib/python3.10/site-packages/synapse/crypto/keyring.py", line 615, in get_key
    return await self.get_server_verify_key_v2_indirect(
  File "/usr/lib/python3.10/site-packages/synapse/crypto/keyring.py", line 737, in get_server_verify_key_v2_indirect
    await self.store.store_server_verify_keys(
  File "/usr/lib/python3.10/site-packages/synapse/storage/databases/main/keys.py", line 123, in store_server_verify_keys
    await self.db_pool.simple_upsert_many(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 1291, in simple_upsert_many
    await self.runInteraction(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 796, in runInteraction
    return await delay_cancellation(_runInteraction())
  File "/usr/lib/python3.10/site-packages/twisted/internet/defer.py", line 1656, in _inlineCallbacks
    result = current_context.run(
  File "/usr/lib/python3.10/site-packages/twisted/python/failure.py", line 489, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 768, in _runInteraction
    result = await self.runWithConnection(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 891, in runWithConnection
    return await make_deferred_yieldable(
  File "/usr/lib/python3.10/site-packages/twisted/python/threadpool.py", line 244, in inContext
    result = inContext.theWork()  # type: ignore[attr-defined]
  File "/usr/lib/python3.10/site-packages/twisted/python/threadpool.py", line 260, in <lambda>
    inContext.theWork = lambda: context.call(  # type: ignore[attr-defined]
  File "/usr/lib/python3.10/site-packages/twisted/python/context.py", line 117, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/usr/lib/python3.10/site-packages/twisted/python/context.py", line 82, in callWithContext
    return func(*args, **kw)
  File "/usr/lib/python3.10/site-packages/twisted/enterprise/adbapi.py", line 282, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 884, in inner_func
    return func(db_conn, *args, **kwargs)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 632, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 1327, in simple_upsert_many_txn
    return self.simple_upsert_many_txn_native_upsert(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 1423, in simple_upsert_many_txn_native_upsert
    txn.execute_values(sql, args, fetch=False)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 327, in execute_values
    return self._do_execute(
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 366, in _do_execute
    return func(sql, *args)
  File "/usr/lib/python3.10/site-packages/synapse/storage/database.py", line 328, in <lambda>
    lambda the_sql: execute_values(self.txn, the_sql, values, fetch=fetch),
  File "/usr/lib/python3.10/site-packages/psycopg2/extras.py", line 1270, in execute_values
    cur.execute(b''.join(parts))
psycopg2.errors.CardinalityViolation: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

@richvdh
Copy link
Member

richvdh commented May 19, 2022

Reformatting and sorting the failing postgres query slightly:

INSERT INTO server_signature_keys (server_name, key_id, from_server, ts_added_ms, ts_valid_until_ms, verify_key) VALUES
    ('1w1.one','ed25519:a_DHFf','matrix.org',1652537297072,1650223352178,'\xda2a04c7047142f499e21144c47c5c54d89bf6eea2c1d8dc3238d7ecd8e9c332'::bytea),
    ('30bq4kgcbdtr885himilrvbzy.ems.host','ed25519:OjJ7cD','matrix.org',1652537297072,1649053641782,'\x743513634abf6a417c7ba1e7fa93c5cc85d9a7861d369b23d88451992b1b2da9'::bytea),
    ('amogus.ddns.net','ed25519:a_RtOq','matrix.org',1652537297072,1649847213004,'\x40fb416835e403d98a2329a47a4c4445976ab1f9ae701738c9063293d22978f0'::bytea),
    ('anontier.nl','ed25519:a_heIQ','matrix.org',1652537297072,1652573697214,'\xe24003bd0bf853c040794fbf75c2972bfa04c33815b6a2798d8fe28d936954fd'::bytea),
    ('artemislena.eu','ed25519:a_ibrU','matrix.org',1652537297072,1652565637722,'\xf64000896d31d28fb4a4efb760a6a400f59492de7a297c71d83e675ee26d1f76'::bytea),
    ('asozial.org','ed25519:MeIzWE','matrix.org',1652537297072,1652648081742,'\x6f1ef68f10f2d1aff20eeb0da8486cc887e6842e61d232608fcc1cfcbae42725'::bytea),
    ('bandersnatch.xyz','ed25519:a_ZZfT','matrix.org',1652537297072,1652564648133,'\x91cb485eca1cc66ff4b0602164f3f6e536ff9070debd1bc3bd10f07259ee8c5b'::bytea),
    ('baytech.community','ed25519:a_zqgF','matrix.org',1652537297072,1652589458960,'\xe09cd456d2bf3d519a97e4f0612d94bd36cf5d5df8bd5c45834f66ae46cd60ff'::bytea),
    ('bcow.xyz','ed25519:2bpeHI','matrix.org',1652537297072,1621261273000,'\x1c81dbda4210f5518e11dae89549d9296c1ba21b2c07fbf1b8ca19bf5ec598b7'::bytea),
    ('bcow.xyz','ed25519:a_KLxZ','matrix.org',1652537297072,1652544023838,'\x9f9754357a1ce89f94ddeef966ff4fceec25db19d4edfa47e9cdde81b548d854'::bytea),
    ('berlin.freedombox.rocks','ed25519:a_tNXr','matrix.org',1652537297072,1640223613795,'\x732e6cd2e3d1838eea554f551406f49ffbea5a23fea6531397d9dc42b5cfe054'::bytea),
    ('bytes4u.de','ed25519:a_cZkY','matrix.org',1652537297072,1652608366351,'\xbe8b6282b72547a49c4d87182cfe69bf6b3e2d17b51a81491df60c98dab0487d'::bytea),
    ('c2t.uk','ed25519:a_LHVQ','matrix.org',1652537297072,1649595281803,'\xe5b5d8083dfd16457909963bc1a382e7e5ba897392db9c109edf250ffa7cf066'::bytea),
    ('chat.trot.soy','ed25519:KhHn6N','matrix.org',1652537297072,1645840339073,'\xff5102bbb8159992fece3c6a994c3ccfd75f0ac951bed07a12c60f5fed60f9d1'::bytea),
    ('chat.xbdm.fun','ed25519:a_FPHf','matrix.org',1652537297072,1648492395917,'\xe0bbd10202efc8164c713bab54454c4e66cd514ec8ff145f449fe54775be0371'::bytea),
    ('cutefunny.art','ed25519:a_nwuR','matrix.org',1652537297072,1652601371682,'\xa22f34ec081d49b9540aa8d23ee48fa482e0fcd07b160f9856eae73d08327343'::bytea),
    ('dark.fi','ed25519:a_apab','matrix.org',1652537297072,1652551850652,'\x83fb2358f383fbce037ae70e4c7fd9dcbba4e991ed23ee4c652cb4c751a1084e'::bytea),
    ('data.haus','ed25519:a_Bnxi','matrix.org',1652537297072,1652585917384,'\xdfe4227e5d6dfa99bc4fd9ab2974ede8a0efdb13f96592d52f295a43b3e9e9e5'::bytea),
    ('davidberthel.de','ed25519:a_AAvs','matrix.org',1652537297072,1652568951391,'\xff65abb4100c785ee0d11e95517a8ef4943a03de101347f8815c106d320fd6be'::bytea),
    ('digitale-gesellschaft.ch','ed25519:a_MVfW','matrix.org',1652537297072,1652574444459,'\xedd4e7c8ecb9d0013b856ae06ed9694269168c79d9944626062ec6c72183d163'::bytea),
    ('emri.online','ed25519:a_gdWy','matrix.org',1652537297072,1652411314510,'\x02b24079fb1b1be7e10a87b7db6390603ebbc8dd210f75423de4c338c2defffc'::bytea),
    ('envs-is-normie.glitch.me:443','ed25519:6trm82dk','matrix.org',1652537297072,1650408450543,'\xdce848349d274077ca36f36299c99bc29e0576d4b5820bf8f365664502914a46'::bytea),
    ('envs-is-normie.glitch.me:443','ed25519:VDpN6wRv','matrix.org',1652537297072,1650784870640,'\x2f8e5ec1d96324de7fc1685314b6ccd47fd5fe7683f8ec4183f9fe6241eea45e'::bytea),
    ('euclids.ems.host','ed25519:1ja6yE','matrix.org',1652537297072,1643046766208,'\x9c7f81355923e56077d4ac204f37676c91a5d64afa5fc3dff23e1a80e13682c4'::bytea),
    ('exonip.de','ed25519:a_epAt','matrix.org',1652537297072,1648632544031,'\x81710bf059217d62f702e7e8b589c94645531aa6462dce5758b171e633e9e611'::bytea),
    ('faelix.im','ed25519:a_pOLQ','matrix.org',1652537297072,1652602772285,'\x5d7fb37d433ee736357fd93dafe3df7ab063570990793f1438519c19271747a7'::bytea),
    ('flieger.chat','ed25519:a_gahC','matrix.org',1652537297072,1652570988716,'\xea6c4d388e8eec25cf905c6df212924c39dbf08883b114e6191db76fddcb8f80'::bytea),
    ('freedom.madliberteaproject.com','ed25519:a_CSfD','matrix.org',1652537297072,1644597362301,'\xcb1b8432903781933e439c5f9b1cda961f1d427a5368696b5db74f66f363ddbf'::bytea),
    ('freehold.earth','ed25519:DM0ecB','matrix.org',1652537297072,1652571922294,'\x7c7c3d8e723f60d100fcc886c95d770c0caf4ac677b6ee61e82334aa2786188f'::bytea),
    ('freiburg.social','ed25519:a_ibXO','matrix.org',1652537297072,1652551668338,'\xba05ee32a052104270cf9d404cd4d6651cda2b652f7cc285657f19db2c3cf284'::bytea),
    ('fulltermprivacy.com','ed25519:a_kZFJ','matrix.org',1652537297072,1652561332351,'\xe65636b9ef3f28506eaf49b667c9de6b103d911340571406f0be785a78979feb'::bytea),
    ('gentoo.guru','ed25519:a_FqxS','matrix.org',1652537297072,1650062499686,'\xd12f44f8132582bbbaa0420a242bb5cf58406760ce3d6df5aa8792737ca22aaf'::bytea),
    ('glasgow.social','ed25519:a_fOjb','matrix.org',1652537297072,1652563096833,'\x6ca0212571e18bdc8874fa74cc6a5ee6aae35705283a8815dac3b1234bb26e13'::bytea),
    ('grahe.eu','ed25519:a_VPIv','matrix.org',1652537297072,1652577683790,'\x497d5cea5a8e9b17c100b66867e8a08f937e0c472c2e959e056f97d20ca1540e'::bytea),
    ('hackwithht.ml','ed25519:5Bijr19Y','matrix.org',1652537297072,1651260495169,'\xabf565bd7d61b5d80b93887330d9c1cef4ad5d2e1dee3fe938a978227673419d'::bytea),
    ('heftyza.uk','ed25519:a_DAlW','matrix.org',1652537297072,1648853724886,'\x5dc04ca95284e66ac7c75a6678fc4d19f7f9d0ca56505af468df406ced5b5946'::bytea),
    ('henning.ems.host','ed25519:g4riyM','matrix.org',1652537297072,1652580435901,'\x91dd74e77549a0c2c510ea2713e2336fd02aba9feffc02cfbe14ffd3ca7ea4a7'::bytea),
    ('highdesertliving.online','ed25519:a_vWeP','matrix.org',1652537297072,1629428339899,'\xfb40c8e4ebb73ee53c7398017096b2aeab15c1bbe7975c971f189d914c680507'::bytea),
    ('homespot.dev','ed25519:a_wtYW','matrix.org',1652537297072,1652603277739,'\x761ac94e6ce58b55be022266940a8a1b50825224edfa9648f5bbf8dfcb1af4e5'::bytea),
    ('horton.ems.host','ed25519:NNKhSA','matrix.org',1652537297072,1652551652600,'\x8b575f839599be7ab6fbe31a4b2d2cb0b44c0d24b7ec5cc62e2846eafaf95cb6'::bytea),
    ('jagow15.org','ed25519:a_zPlt','matrix.org',1652537297072,1652571426179,'\x3bcee8db2fb2ac8c33d25e92d42dbfff617a5b0bf347ffed691249c5ae556270'::bytea),
    ('jameskitt616.de','ed25519:a_ICBF','matrix.org',1652537297072,1623318138102,'\xf13617c2690da86734ddb8834a2ade10476056623b453758c9918926ffdbd194'::bytea),
    ('jaminit.co.uk','ed25519:aQNzX4tN','matrix.org',1652537297072,1650549202720,'\x9a8b688a911d513b3384a8744fe4139cb2e2988187cb1d9a5e92636e5eba1833'::bytea),
    ('jaminit.co.uk','ed25519:xuGosy','matrix.org',1652537297072,1652733233464,'\x54ec2213b3b7616078e72b43853a792153e9e7904c4482546a1a1167ee911cb6'::bytea),
    ('jupiterbroadcasting.com','ed25519:a_moYh','matrix.org',1652537297072,1652582419931,'\x3f796084fea49ad0a8acd78e71f10cb57501790ec4d2b4d8ae85d17d478ce9c1'::bytea),
    ('jx-stack.xyz','ed25519:a_pIte','matrix.org',1652537297072,1647247368858,'\xcf8b787efd347c58b287b3dcf5a938a6fb7a73c6c94dedf8f335b23226dac9af'::bytea),
    ('katietso.dev','ed25519:sByxFP','matrix.org',1652537297072,1646181650627,'\x3bef9175b7023f4a35fe02a9d6e822844cbbaa32e37449328cf9ebb45002536f'::bytea),
    ('kreatea.space','ed25519:a_wxTS','matrix.org',1652537297072,1652538380152,'\x1901d974309e476478d1a274229ca66436b6fc1dd4082e092b22cb9a48ea5b36'::bytea),
    ('laubersheimer.eu','ed25519:a_PHpn','matrix.org',1652537297072,1652545938728,'\x655214531b9b08256f1ec9e9ce76eee68a0310f2597d6f2d95964f967b22131f'::bytea),
    ('linuxdelta.com','ed25519:a_Uzcs','matrix.org',1652537297072,1643894738652,'\x561b777b2bf8e0dca3d0ddd44bec17e1c29a693b4d627d4fd1a0c661a173fdbe'::bytea),
    ('lucidminion.com','ed25519:a_rPGw','matrix.org',1652537297072,1642001902140,'\x49290bf9b312043ac6ccf2fa0e79b573787ed5e023a16887496e085a96423214'::bytea),
    ('lucidminion.com','ed25519:a_zcuT','matrix.org',1652537297072,1641590753640,'\xbfe02723a2e71cb87a8f353594aa1f69a9d0d5f2794b2ee024dde0d49555c59e'::bytea),
    ('lx-is.lol','ed25519:a_CHdj','matrix.org',1652537297072,1651983178816,'\x7dd68b7ccba788eb774bc3e336c9af54a8f10c61fee3ffe6163695a68bfadce9'::bytea),
    ('lx-is.lol','ed25519:a_fQKG','matrix.org',1652537297072,1650226441109,'\x7d3b7b13eee9b139583b5ca32ec8a958824fb8f5e7b877127e8156ce369e99f7'::bytea),
    ('maeli.net','ed25519:a_twoa','matrix.org',1652537297072,1652579965864,'\x3708e0092c64bdbf487a3537fee76d5d9393ef497fbb1bfa9fad5fa52ef20101'::bytea),
    ('manerakai.com','ed25519:a_yPqn','matrix.org',1652537297072,1652601621617,'\xc959373a9eda053f20550e85bbd5bac7b0466e54813359594edf4b4fc2289b1b'::bytea),
    ('matrix.afaregensburg.org','ed25519:a_HOZK','matrix.org',1652537297072,1652581045591,'\x2c5f93ab1f2316aaee0b73e19df2ce0ca2d53c75f1fa5331b81d3ff6150b2fa1'::bytea),
    ('matrix.aristoi.uk.eu.org','ed25519:a_stvG','matrix.org',1652537297072,1649280145427,'\x7af6be5d42567b7bdb09c8cda6f8b2fad171be853a0d9111dcb5ea2e60aedf82'::bytea),
    ('matrix.arsenm.dev','ed25519:ads3pX','matrix.org',1652537297072,1627788036360,'\x4c63a26585c73045820faae0aa226176b83b3b89cde3cf230cf723fd5f5bd312'::bytea),
    ('matrix.arsenm.dev','ed25519:a_TzAM','matrix.org',1652537297072,1601024554498,'\xe05be8be8562f46ecc210b8bb5fe35e41336664668c91ed747bc290475d22fdc'::bytea),
    ('matrix.arsenm.dev','ed25519:a_TzAM','matrix.org',1652537297072,1627273239740,'\xe05be8be8562f46ecc210b8bb5fe35e41336664668c91ed747bc290475d22fdc'::bytea),
    ('matrix.arul.io','ed25519:a_sESq','matrix.org',1652537297072,1641633715896,'\xba887a516d7f7eda7468201c03908c80a4daf7f9b1783a07377ce0e766e830b7'::bytea),
    ('matrix.ckto.ovh','ed25519:a_Qusf','matrix.org',1652537297072,1652504359814,'\xc6abf4104e7171889743ddc29196b5954e729e4c50f43cb1fe3a7dfd6c1a0f15'::bytea),
    ('matrix.evildayz.com','ed25519:a_VHMP','matrix.org',1652537297072,1652549357565,'\x337aeb3513d7b1d54d4d93857c8d9eae13f84c35db70d98197f39ddf2d55d9ea'::bytea),
    ('matrix.gearlandia.haus','ed25519:a_uIST','matrix.org',1652537297072,1651776124282,'\xe0f7551a2adb322dcb8f8ab2ac3821c943903cb059387e936d7bdd0d65a9ad1f'::bytea),
    ('matrix.honbra.com','ed25519:a_kGkM','matrix.org',1652537297072,1645858225712,'\xab6ac2e2a891cbc6eea6657598cd9848d32c8414212df30ce7ab86a59476178f'::bytea),
    ('matrix.honbra.com','ed25519:a_wuLu','matrix.org',1652537297072,1643967331425,'\xc12189711895843b678c846286cafc0451fd19ddcf2e182c95d6bc3cca556061'::bytea),
    ('matrix-jp.net','ed25519:a_KsRz','matrix.org',1652537297072,1632496725551,'\x29206f1de356d35dd81a64df5eaa7b98e6e2416a42e5ff30d1dc4a97c755eb86'::bytea),
    ('matrix.juggler.jp','ed25519:a_jQrd','matrix.org',1652537297072,1652560192571,'\x7d165d961c15c4b5c030b2abbe311ddfb5564d2f15a8a6b1632f216126e784da'::bytea),
    ('matrix.lolispace.moe','ed25519:a_hDKj','matrix.org',1652537297072,1652588259383,'\xe00f0d4da37cf38385ffdee3c2e7d5f46fba50aa9b526a309fb38601149f145c'::bytea),
    ('matrix.nerd2nerd.org','ed25519:a_CbpD','matrix.org',1652537297072,1652602791924,'\xa407b7fc7c203ecc99342ea3008f3d724d8479f916454d0c9445c5ad7c755666'::bytea),
    ('matrix.nogafam.es','ed25519:a_XEnV','matrix.org',1652537297072,1652587217031,'\x0053481c5d5637f20e571fb041dc7ff6c5380581a5eb7071778e8179a7ac4597'::bytea),
    ('matrix.pixelpaper.org','ed25519:a_PteU','matrix.org',1652537297072,1650900362167,'\xc0fc8fb7afd37d3938333703d63817b2331d7372a8c580324d1a9230f8491dc5'::bytea),
    ('matrix.satanic.gay','ed25519:a_mPko','matrix.org',1652537297072,1650385667440,'\xd530344b51c9586997ae1be1fb88845113b00672b8ee6f4387f952bd82578d36'::bytea),
    ('matrix.secureprivacy.tech','ed25519:a_CznT','matrix.org',1652537297072,1637305576813,'\x1e23424788fa4d298fbc6e0c003f4ea2f6a98ddfff42e47f3f641dcda512c662'::bytea),
    ('milkte.ch','ed25519:a_Bmsr','matrix.org',1652537297072,1628643113205,'\xaf6de15a4b3dff7d117f67eb5aaa76c92eb5f35bd30e49e5372d4a048ee14600'::bytea),
    ('milkte.ch','ed25519:a_tdzR','matrix.org',1652537297072,1647721541289,'\xd314323c9383493cc545bb3698f75e74623a8fa6b536d9eaf63049bf27191cc5'::bytea),
    ('mint.lgbt','ed25519:a_apXQ','matrix.org',1652537297072,1647084358110,'\xfa82ab066179dba399ed0cad21a3614b89fe26ce3c0516ce97e98c6cdc76bc14'::bytea),
    ('mtrx.nz','ed25519:a_jbcK','matrix.org',1652537297072,1652555838966,'\x9962d052dc5d82e197c0c9aff29918dd073dea1f0fab003f1c6e83ae80d9ec82'::bytea),
    ('nitro.chat','ed25519:a_FXKA','matrix.org',1652537297072,1652555659742,'\xe725a4ad326e95ae1772ec20b7de59da853b7662af597fa7c1cdec05ef173a82'::bytea),
    ('ouchcollective.net','ed25519:a_hCsp','matrix.org',1652537297072,1652575100950,'\xa23fc6f1c936b529cf3c6fe41b6361166238ddac93bd1ccb3bb42e6a666e475c'::bytea),
    ('pixelsafe.io','ed25519:a_fpGA','matrix.org',1652537297072,1652300427394,'\xc7c0157c56638b0bf6d5812ff4c6cb79f90c2b27d040d857f251a11741deaafd'::bytea),
    ('pragma-messenger.ch','ed25519:a_KOtB','matrix.org',1652537297072,1652574962804,'\xeffbfe96fe47eeb937334009c5c8c331c5c9fdd2d696a508872f3690997253ec'::bytea),
    ('radicle.community','ed25519:a_KMYd','matrix.org',1652537297072,1652550949990,'\x75d7ac666ce369c660c612b32f13f3490b1920e04f61d3b992e8bbb98cc87ff3'::bytea),
    ('sammydepresso.ems.host','ed25519:XGP6Zk','matrix.org',1652537297072,1632862501688,'\xfc0d8457373da44d7b50a4e6a1756aebf4720495bdaaef9f8aa9a073a40055a2'::bytea),
    ('satanic.gay','ed25519:a_roaQ','matrix.org',1652537297072,1650936359938,'\x9415beeec6e8826ee817e376c36ff929dab26e27544a24a5471572a400f00ef1'::bytea),
    ('server.matrix4ulm.de','ed25519:a_PkwZ','matrix.org',1652537297072,1652597616285,'\x4415bc16a33d65fa73202218a0bc00b7329f80d2e847da44be13da8b5c3f885b'::bytea),
    ('sharp.ems.host','ed25519:YSLy0M','matrix.org',1652537297072,1652590815474,'\x20fac0685299fc1a4da4977277139247e54e8dba5a3b3ab602664511cc14676c'::bytea),
    ('silkroadtools.com','ed25519:a_zcuT','matrix.org',1652537297072,1636288401812,'\xbfe02723a2e71cb87a8f353594aa1f69a9d0d5f2794b2ee024dde0d49555c59e'::bytea),
    ('stage3.die.one','ed25519:FSnuPg','matrix.org',1652537297072,1643741271469,'\x366ef7799979ef28a8feb3355abb8defd4f2a9c4ef9377e4866de34fa10a2126'::bytea),
    ('techlore.net','ed25519:a_sAzp','matrix.org',1652537297072,1652577818278,'\x38d64697b4c58f13fc3f08315c3dd22be6b00e69468b96a2030a5b18c0af8533'::bytea),
    ('techlore.net','ed25519:a_TFLD','matrix.org',1652537297072,1639808703392,'\x60ea01842844e11f64b25bd3028dfc72cdafae1682b982b6f3e181dbe815aa85'::bytea),
    ('theprivatebay.tech','ed25519:a_FWQx','matrix.org',1652537297072,1650493712948,'\xdca9d16a0a51fd4eb595db92d4df3753dd495583004d0a2346e0554d8000357e'::bytea),
    ('theprivatebay.tech','ed25519:a_JQji','matrix.org',1652537297072,1650492462164,'\xcec25674ea3b6dd6ab54efae1febeac9abf2e107e8b30659bc466013dd6ed004'::bytea),
    ('theprivatebay.tech','ed25519:a_reQo','matrix.org',1652537297072,1650465056841,'\x63a4c4add8973a2ceac2427daa6d47199f30b36ab1e1af905e32b46458975db6'::bytea),
    ('theprivatebay.tech','ed25519:a_sysc','matrix.org',1652537297072,1652605091189,'\xd1ac6803f1c710d02250b56d30bd8663cf63815843cddc4273b4aace16fa664b'::bytea),
    ('thighhighs.io','ed25519:a_vJls','matrix.org',1652537297072,1649050331849,'\x1740cc13cd6b5641f8a28ecd5cb39971e0b27fa43b415604bd6a4a68c5c27fa2'::bytea),
    ('thisisplate.hackwithht.ml','ed25519:FAhjA2','matrix.org',1652537297072,1652944204304,'\x76f322351c1f6e7670d6bbef58173260ad953f08b9e793b73588a33c39539bed'::bytea),
    ('tristans.cloud','ed25519:a_aQFy','matrix.org',1652537297072,1652596367803,'\xadcd3614b95db0640cca24858e314a0e2664f9d76d11bbfe1e6429b8a0848477'::bytea),
    ('usethe.tools','ed25519:a_EaMg','matrix.org',1652537297072,1652508774657,'\x9089cd802eb37f627b35f2cab087abd5e5f31f289a03761d3a07ef4067f67b65'::bytea)
 ON CONFLICT (server_name, key_id) DO UPDATE SET from_server=EXCLUDED.from_server, ts_added_ms=EXCLUDED.ts_added_ms, ts_valid_until_ms=EXCLUDED.ts_valid_until_ms, verify_key=EXCLUDED.verify_key

Note that there are multiple entries for 'matrix.arsenm.dev','ed25519:a_TzAM':

    ('matrix.arsenm.dev','ed25519:a_TzAM','matrix.org',1652537297072,1601024554498,'\xe05be8be8562f46ecc210b8bb5fe35e41336664668c91ed747bc290475d22fdc'::bytea),
    ('matrix.arsenm.dev','ed25519:a_TzAM','matrix.org',1652537297072,1627273239740,'\xe05be8be8562f46ecc210b8bb5fe35e41336664668c91ed747bc290475d22fdc'::bytea),

@DMRobertson
Copy link
Contributor

Does that mean we ought to do some de-duplication in store_server_verify_keys? Or perhaps in simple_upsert_many?

@richvdh
Copy link
Member

richvdh commented May 19, 2022

We're actually looking for ed25519:a_TzAM and ed25519:ads3pX from this server, but issuing a query for those keys to matrix.org returns ed25519:a_TzAM twice (with the two different valid_until_ts shown above):

curl https://matrix.org/_matrix/key/v2/query -d '{"server_keys": {"matrix.arsenm.dev": {"ed25519:a_TzAM": {}, "ed25519:ads3pX": {}}}}' | jq .
{
  "server_keys": [
    {
      "old_verify_keys": {
        "ed25519:a_TzAM": {
          "expired_ts": 1601024554498,
          "key": "4FvovoVi9G7MIQuLtf415BM2ZkZoyR7XR7wpBHXSL9w"
        }
      },
      "server_name": "matrix.arsenm.dev",
      "signatures": {
        "matrix.arsenm.dev": {
          "ed25519:ads3pX": "TIwJM/GR0MOjIgLozTB0KexowbuMG8DRGu9DAQbMcVDAgUNwWVglce/9LPTrfq+g7qj73Ml3mAaUj9MBD6cmDQ"
        },
        "matrix.org": {
          "ed25519:auto": "xuIzHyUzHqz7NL5t34GTCXSSh/rxhVOxktzJxT+LLRxY+9FlHQOJoRUPZJnBY2QjzgHmhWW3+JAoKOcoIlZQBw"
        }
      },
      "valid_until_ts": 1627788036360,
      "verify_keys": {
        "ed25519:ads3pX": {
          "key": "TGOiZYXHMEWCD6rgqiJhdrg7O4nN488jDPcj/V9b0xI"
        }
      }
    },
    {
      "old_verify_keys": {},
      "server_name": "matrix.arsenm.dev",
      "signatures": {
        "matrix.arsenm.dev": {
          "ed25519:a_TzAM": "oQgYwB7VF/PAc0Y/VeNBKIAeNGxGnKKdamacfqpmQLfm3IGjAUSDBwlamlZRgAT7Cp9yb1dqVPxQcfB2akWqBg"
        },
        "matrix.org": {
          "ed25519:auto": "WMwuXWnDJuNbzCA3Pdc0ZfBgg0azw3SYl6SsksUrQXyKtu6uGAuiAOIjjXcuQP6hFB14ftgN6I8vkAd4sWqXDQ"
        }
      },
      "tls_fingerprints": [],
      "valid_until_ts": 1627273239740,
      "verify_keys": {
        "ed25519:a_TzAM": {
          "key": "4FvovoVi9G7MIQuLtf415BM2ZkZoyR7XR7wpBHXSL9w"
        }
      }
    }
  ]
}

... so I think there are two problems here:

  • matrix.org should not return two copies of the same key
  • get_server_verify_key_v2_indirect (or something) should de-duplicate (but which should it choose?)

@erikjohnston
Copy link
Member

Given this is a bit of a rare issue we're not going to prioritise fixing it right now (PRs, as always, are welcome). But if other people are seeing this then please shout and we can reconsider.

@H-Shay H-Shay added the O-Uncommon Most users are unlikely to come across this or unexpected workflow label Aug 25, 2022
@TheNamelessWonderer
Copy link

TheNamelessWonderer commented Apr 11, 2023

Same issue:

2023-04-11 22:00:07,881 - synapse.crypto.keyring - 646 - ERROR - PerspectivesKeyFetcher-496 - Unable to get key from 'matrix.org': CardinalityViolation ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/synapse/crypto/keyring.py", line 638, in get_key
    return await self.get_server_verify_key_v2_indirect(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/crypto/keyring.py", line 760, in get_server_verify_key_v2_indirect
    await self.store.store_server_verify_keys(
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/databases/main/keys.py", line 126, in store_server_verify_keys
    await self.db_pool.simple_upsert_many(
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 1431, in simple_upsert_many
    await self.runInteraction(
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 902, in runInteraction
    return await delay_cancellation(_runInteraction())
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/twisted/internet/defer.py", line 1693, in _inlineCallbacks
    result = context.run(
             ^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/twisted/python/failure.py", line 518, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 868, in _runInteraction
    result = await self.runWithConnection(
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 997, in runWithConnection
    return await make_deferred_yieldable(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/twisted/python/threadpool.py", line 244, in inContext
    result = inContext.theWork()  # type: ignore[attr-defined]
             ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/twisted/python/threadpool.py", line 260, in <lambda>
    inContext.theWork = lambda: context.call(  # type: ignore[attr-defined]
                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/twisted/python/context.py", line 117, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/twisted/python/context.py", line 82, in callWithContext
    return func(*args, **kw)
           ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/twisted/enterprise/adbapi.py", line 282, in _runWithConnection
    result = func(conn, *args, **kw)
             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 990, in inner_func
    return func(db_conn, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 730, in new_transaction
    r = func(cursor, *args, **kwargs)
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 1463, in simple_upsert_many_txn
    return self.simple_upsert_many_txn_native_upsert(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 1561, in simple_upsert_many_txn_native_upsert
    txn.execute_values(sql, args, fetch=False)
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 392, in execute_values
    return self._do_execute(
           ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 446, in _do_execute
    return func(sql, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/synapse/storage/database.py", line 393, in <lambda>
    lambda the_sql: execute_values(self.txn, the_sql, values, fetch=fetch),
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/psycopg2/extras.py", line 1299, in execute_values
    cur.execute(b''.join(parts))
psycopg2.errors.CardinalityViolation: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

I think the room is #synapse:matrix.org. I noticed that I was randomly removed from the room so I tried to join it again which was successful but since then I have this error constantly in the log. Any solution?

Synapse 1.81.0

@TheNamelessWonderer
Copy link

Just tested if I leave #synapse:matrix.org the error disappears but it reappears if I rejoin the room even if I purge it using the synapse api.

@reivilibre reivilibre added O-Occasional Affects or can be seen by some users regularly or most users rarely and removed O-Uncommon Most users are unlikely to come across this or unexpected workflow labels Apr 12, 2023
@reivilibre
Copy link
Contributor

Perspective key server issue offshot to #15424.

The handling of the response (deduplication) will be done by #15423 — this should fix this bug but I still reckon it'd be good to figure out what the perspective key server response should look like. The spec is thin on details about how these cases should be handled.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
A-Federated-Join joins over federation generally suck O-Occasional Affects or can be seen by some users regularly or most users rarely S-Major Major functionality / product severely impaired, no satisfactory workaround. T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues.
Projects
None yet
7 participants