Purge API - last message in the room will not be removed

[ukcb]

The purging of the history is blocked if any user in a room does not look into the system for an extended period of time. I would like to remove all that is older than 7 days, but many older messages remain. Can this be related to the read receipts?

I would like to force the purge of history. Please check that. Thank you.

debian stretch and matrix-synapse v0.28.0

[richvdh]

have you tried the purge api?

[ukcb]

Yes, of course I use the purge api. I delete everything that is older than 7 days, but I still see messages from last month. I always deleted the cache in Riot and in the browser.

[richvdh]

did you set "delete_local_events": true ?

[ukcb]

Yes, here is my script: #2964 (comment)

[ukcb]

For example, I see a message in the room:

{
  "origin_server_ts": 1524581143506,
  "sender": "@xxxxxx",
  "event_id": "$1524581143173RyuZS:xxxxxx",
  "unsigned": {
    "age": 1992015824
  },
  "content": {
    "body": "xxxxxx",
    "msgtype": "m.text"
  },
  "type": "m.room.message",
  "room_id": "!yQZxENSODHRTgwdfWO:xxxxxx"
}

This tells me the api while cleaning:

ROOM_ID: !yQZxENSODHRTgwdfWO:xxxxxx
{
    "errcode": "M_NOT_FOUND",
    "error": "there is no event to be purged"
}

The message is older than 7 days:

$ date
Thu May 17 18:18:52 CEST 2018

$ date --date='@1524581143'
Tue Apr 24 16:45:43 CEST 2018

I am currently using matrix v0.29.0 und riot v0.15.1

[richvdh]

well, that sounds like a bug. If you share some logs during the purge, we might be able to see what's going on. Please could you also update the subject of this issue to something more descriptive.

[ukcb]

This is the entry in the database table for the example above:

synapse=# SELECT event_id, origin_server_ts FROM events WHERE room_id = '!yQZxENSODHRTgwdfWO:xxxxxx' AND NOT outlier;
event_id                    | origin_server_ts 
----------------------------+------------------
 $1524581143173RyuZS:xxxxxx |    1524581143506

synapse=# SELECT event_id, origin_server_ts FROM events WHERE event_id = '$1524581143173RyuZS:xxxxxx' AND origin_server_ts < 1526062131179;
event_id                    | origin_server_ts 
----------------------------+------------------
 $1524581143173RyuZS:xxxxxx |    1524581143506

# 7 days ago (the value for the last query)
$ date --date='@1526062131'
Fri May 11 20:08:51 CEST 2018

synapse=# SELECT * FROM events WHERE room_id = '!yQZxENSODHRTgwdfWO:xxxxxx' AND NOT outlier;
stream_ordering | topological_ordering | event_id | type | room_id 
| content | unrecognized_keys | processed | outlier | depth | 
origin_server_ts | received_ts | sender | contains_url 
---------------------------------------------------------
1463 | 36 | $1524581143173RyuZS:xxxxxx | m.room.message | !yQZxENSODHRTgwdfWO:xxxxxx 
| {"body": "xxxxxx", "msgtype": "m.text"} |  | t | f | 36 | 
1524581143506 | 1524581143558 | @user:xxxxxx | f

I do not see anything unusual.

[ukcb]

I don't know if it's important: it's the last message in the room that's not removed.

synapse=# SELECT type FROM events WHERE room_id = '!yQZxENSODHRTgwdfWO:xxxxxx';
           type            
---------------------------
 m.room.power_levels
 m.room.aliases
 m.room.message   <---------- That's the only last message!
 m.room.canonical_alias
 m.room.history_visibility
 m.room.guest_access
 m.room.join_rules
 m.room.avatar
 m.room.history_visibility
 m.room.member
 m.room.related_groups
 m.room.member
 m.room.name
 m.room.topic
 m.room.member
 m.room.member
 m.room.create
 m.room.member
 m.room.avatar
 m.room.join_rules
 m.room.related_groups
(21 rows)

That's what I mean by forced purging. I also want to delete the last entry after a certain time.

[richvdh]

oh right. Yes the last message in the room will not be removed. Please update the subject of the issue to be clearer.

[ukcb]

In v0.30.0 it does not look any better either. There is something broken

#################################################################################################
2018-05-25 20:02:25,796 - synapse.http.server - 102 - ERROR - POST-379- Failed handle request via <function _async_render at 0x7fa705693c08>: <XForwardedForRequest at 0x7fa6dff1e680 method=POST uri=/_matrix/client/r0/admin/purge_history/!MGUaVpZzXIfxmhJrpV:xxxxxx?access_token=<redacted> clientproto=HTTP/1.1 site=8008>: Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/twisted/internet/defer.py", line 651, in _runCallbacks
    current.result = callback(current.result, *args, **kw)
  File "/usr/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1355, in gotResult
    _inlineCallbacks(r, g, deferred)
  File "/usr/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1297, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/lib/python2.7/dist-packages/twisted/python/failure.py", line 389, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
--- <exception caught here> ---
  File "/usr/lib/python2.7/dist-packages/synapse/http/server.py", line 79, in wrapped_request_handler
    yield h(self, request)
  File "/usr/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1297, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/lib/python2.7/dist-packages/twisted/python/failure.py", line 389, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/lib/python2.7/dist-packages/synapse/http/server.py", line 305, in _async_render
    callback_return = yield callback(request, **kwargs)
  File "/usr/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1297, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/lib/python2.7/dist-packages/twisted/python/failure.py", line 389, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/lib/python2.7/dist-packages/synapse/rest/client/v1/admin.py", line 179, in on_POST
    room_event_after_stream_ordering,
  File "/usr/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1297, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/lib/python2.7/dist-packages/twisted/python/failure.py", line 389, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 320, in runInteraction
    result = yield self.runWithConnection(inner_func, *args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/twisted/internet/defer.py", line 1297, in _inlineCallbacks
    result = result.throwExceptionIntoGenerator(g)
  File "/usr/lib/python2.7/dist-packages/twisted/python/failure.py", line 389, in throwExceptionIntoGenerator
    return g.throw(self.type, self.value, self.tb)
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 365, in runWithConnection
    inner_func, *args, **kwargs
  File "/usr/lib/python2.7/dist-packages/twisted/python/threadpool.py", line 246, in inContext
    result = inContext.theWork()
  File "/usr/lib/python2.7/dist-packages/twisted/python/threadpool.py", line 262, in <lambda>
    inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
  File "/usr/lib/python2.7/dist-packages/twisted/python/context.py", line 118, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/usr/lib/python2.7/dist-packages/twisted/python/context.py", line 81, in callWithContext
    return func(*args,**kw)
  File "/usr/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py", line 307, in _runWithConnection
    compat.reraise(excValue, excTraceback)
  File "/usr/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py", line 298, in _runWithConnection
    result = func(conn, *args, **kw)
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 361, in inner_func
    return func(conn, *args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 316, in inner_func
    func, *args, **kwargs
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 241, in _new_transaction
    r = func(txn, *args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 853, in _simple_select_one_txn
    txn.execute(select_sql, keyvalues.values())
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 82, in execute
    self._do_execute(self.txn.execute, sql, *args)
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 112, in _do_execute
    sql, *args
psycopg2.ProgrammingError: operator does not exist: text = record
LINE 1: ..., topological_ordering FROM events WHERE event_id = (2498, 3...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
#############################################################################################################

[richvdh]

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

this looks like a separate issue. Please file a separate bug

[ukcb]

Ok, I opened a new issue #3300

[richvdh]

@babolivier am I dreaming, or did you fix something like this recently?

[babolivier]

@babolivier am I dreaming, or did you fix something like this recently?

Nope. The purge will not remove the latest message in the room because it's the last forward extremity, and removing it would create all sort of not-so-fun stuff. The best way forward would be to redact this event, but that's a bit of a faff currently, and would need further thoughts.
I'm a bit surprised the doc doesn't mention it, I'll amend it.

[babolivier]

This issue looks like it was due to an omission in our documentation - that's now fixed in #6891. Should we close it now that it has been merged?

[richvdh]

let's.