467 - ERROR - update_client_ips-xxxx- Failed to insert client IP

[scanno]

Description

User IP's and session data not updating and seeing the following errors in the homeserver.log since 2019-10-29

2019-12-14 19:36:20,232 - synapse.storage.data_stores.main.client_ips - 467 - ERROR - update_client_ips-2423- Failed to insert client IP (('@redacted', 'redacted', '::ffff:xxx.yyy.zzz.zzz'), ('Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Riot/1.5.6 Chrome/78.0.3904.126 Electron/7.1.3 Safari/537.36', 'redacted', redacted)): InvalidColumnReference('there is no unique or exclusion constraint matching the ON CONFLICT specification\n',)

and

2019-12-14 19:36:06,698 - synapse.federation.federation_server - 793 - ERROR - PUT-15344- Failed to handle edu 'm.device_list_update'
Traceback (most recent call last):
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/federation/federation_server.py", line 789, in on_edu
    await handler(origin, content)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/site-packages/synapse/handlers/device.py", line 575, in incoming_device_list_update
    yield self._handle_device_updates(user_id)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/site-packages/synapse/util/metrics.py", line 82, in measured_func
    r = yield func(self, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/site-packages/synapse/handlers/device.py", line 604, in _handle_device_updates
    yield self.user_device_resync(user_id)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/site-packages/synapse/handlers/device.py", line 724, in user_device_resync
    yield self.store.update_remote_device_list_cache(user_id, devices, stream_id)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/site-packages/synapse/storage/database.py", line 456, in runInteraction
    **kwargs
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/site-packages/synapse/storage/database.py", line 504, in runWithConnection
    self._db_pool.runWithConnection(inner_func, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/python/threadpool.py", line 250, in inContext
    result = inContext.theWork()
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/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.6/site-packages/twisted/python/context.py", line 85, in callWithContext
    return func(*args,**kw)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/python/compat.py", line 464, in reraise
    raise exception.with_traceback(traceback)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 501, in inner_func
    return func(conn, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 339, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/data_stores/main/devices.py", line 958, in _update_remote_device_list_cache_txn
    lock=False,
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 697, in simple_upsert_txn
    txn, table, keyvalues, values, insertion_values=insertion_values
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 810, in simple_upsert_txn_native_upsert
    txn.execute(sql, list(allvalues.values()))
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 142, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 168, in _do_execute
    return func(sql, *args)
psycopg2.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification

Trying to get a list of available accounts using _synapse/admin/v2/users?from=0&limit=10&guests=false generates a server error:

{
    "errcode": "M_UNKNOWN",
    "error": "Internal server error"
}

with the following trace:

2019-12-14 19:50:56,933 - synapse.http.server - 109 - ERROR - GET-16427- Failed handle request via 'UsersRestServletV2': <SynapseRequest at 0x7f2ead7a47b8 method='GET' uri='/_synapse/admin/v2/users?from=0&limit=10&guests=false' clientproto='HTTP/1.1' site=8448>
Traceback (most recent call last):
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/http/server.py", line 77, in wrapped_request_handler
    await h(self, request)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/http/server.py", line 326, in _async_render
    callback_return = await callback_return
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/rest/admin/users.py", line 96, in on_GET
    start, limit, user_id, guests, deactivated
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/site-packages/synapse/handlers/admin.py", line 84, in get_users_paginate
    start, limit, name, guests, deactivated
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/site-packages/synapse/storage/database.py", line 456, in runInteraction
    **kwargs
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/internet/defer.py", line 1416, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/site-packages/synapse/storage/database.py", line 504, in runWithConnection
    self._db_pool.runWithConnection(inner_func, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/python/threadpool.py", line 250, in inContext
    result = inContext.theWork()
  File "/opt/venvs/matrix-synapse/lib/python3.6/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.6/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.6/site-packages/twisted/python/context.py", line 85, in callWithContext
    return func(*args,**kw)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/python/compat.py", line 464, in reraise
    raise exception.with_traceback(traceback)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 501, in inner_func
    return func(conn, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 339, in new_transaction
    r = func(cursor, *args, **kwargs)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 1419, in simple_select_list_paginate_txn
    txn.execute(sql, arg_list + [limit, start])
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 142, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/opt/venvs/matrix-synapse/lib/python3.6/site-packages/synapse/storage/database.py", line 168, in _do_execute
    return func(sql, *args)
psycopg2.errors.UndefinedFunction: operator does not exist: smallint = boolean
LINE 1: ... user_type, deactivated FROM users WHERE is_guest = false AN...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

also trying to query an account by using _synapse/admin/v1/whois/@<userid> results in an empty result:

{
    "user_id": "@<userid>",
    "devices": {
        "": {
            "sessions": [
                {
                    "connections": []
                }
            ]
        }
    }
}

Version information

• Version:

{
    "server_version": "1.7.0",
    "python_version": "3.6.9"
}

Running postgres 12 as database.

• Install method:
  Using package manager

• Platform:
  AMD64 - Ubuntu 18.04.3

[aaronraimist]

Related: #4503

[richvdh]

odd. can you show the results of \d device_lists_remote_extremeties on your database?

Trying to get a list of available accounts using _synapse/admin/v2/users?from=0&limit=10&guests=false generates a server error

there's no reason to believe this is related. please open a separate issue for this.

[scanno]

synapse=# \d device_lists_remote_extremeties
  Table "public.device_lists_remote_extremeties"
  Column   | Type | Collation | Nullable | Default 
-----------+------+-----------+----------+---------
 user_id   | text |           | not null | 
 stream_id | text |           | not null | 

[scanno]

Could it be that the upgrade to sysnapse 1.5 went wrong and the update of the devices table went wrong?

[richvdh]

Hrm.

The indexes on that table were added back in Synapse 0.33.9, by #4132. Why would Synapse 1.5 be relevant?

Do you happen to remember which version of Synapse you used when you first installed it? (was it before or after 1.0.0?)

Also: did you start with postgres, or did you migrate from sqlite?

[scanno]

I have been using synapse way before v1.0 and migrated from sqlite to postgress also before 1.0.

Can't remember exactly with what version I started to be honest. That must be before 0.33.9😬

[richvdh]

ok, then this sounds like a duplicate of #4877.

The workaround for now is to manually do:

CREATE UNIQUE INDEX device_lists_remote_extremeties_unique_idx ON public.device_lists_remote_extremeties USING btree (user_id);

[scanno]

@richvdh Still have the same errors after creating the index

[richvdh]

with respect, I don't believe you.

You may be getting similar errors from other tables; see #4877 for more details.

[scanno]

@richvdh Thx seems solved by manually adding all the missing indexes