Database inconsistency from some device IDs

[bdelwood]

After migrating using syn2mas, I got the following error from graphql when looking at the device panel in the user webui:

error: Error: Database inconsistency on table compat_sessions column device_id row 01GBDKE9WE9BA835G0HBW13FZ2

Running mas-cli manage kill-sessions led to a similar error:

2024-05-04T18:11:13.373610Z ERROR cli.manage.kill_sessions{user.username="elwood"}:db.compat_session.lookup{compat_session.id=01GBDKE9WE9BA835G0HBW13FZ2 db.statement="\n                SELECT compat_session_id\n                     , device_id\n                     , user_id\n                     , user_session_id\n                     , created_at\n                     , finished_at\n                     , is_synapse_admin\n                     , user_agent\n                     , last_active_at\n                     , last_active_ip as \"last_active_ip: IpAddr\"\n                FROM compat_sessions\n                WHERE compat_session_id = $1\n            "}: mas_storage_pg::compat::session: error=Database inconsistency on table compat_sessions column device_id row 01GBDKE9WE9BA835G0HBW13FZ2
Error: Database inconsistency on table compat_sessions column device_id row 01GBDKE9WE9BA835G0HBW13FZ2

Caused by:
    Device ID contains invalid characters

I had some really old sessions, generated by the Mautrix bridges, with device IDs like Facebook Messenger Bridge. Dropping the rows with those device IDs in compat_access_tokens and compat_sessions seems to have cleared it up and not broken anything else.

[cleverer]

I also have this issue… Leftover tokens from mautrix-telegram and mautrix-whatsapp.

Instead of deleting the records, I changed the device ID using the following SQL, and then could successfully log out through the UI:

UPDATE compat_sessions SET device_id = 'TELBR' || md5(random()::text) WHERE device_id = 'Telegram Bridge';
UPDATE compat_sessions SET device_id = 'WAB' || md5(random()::text) WHERE device_id = 'WhatsApp Bridge';

I think this is related to #2033 and/or #2244?

Edit: Hmm seems to not have fully cleaned it up. I still have those sessions show up in Element clients :/

[matrixbot]

For your information, this issue has been copied over to the Element fork of matrix-authentication-service: element-hq/matrix-authentication-service#2724