DB upgrade failed

[MatthiasGrandl]

Nov 02 11:53:25 customer-10164-1 matrix-mautrix-whatsapp[1360949]: 2024-11-02T10:53:25.161Z FTL Failed to migrate legacy database error="pq: insert or update on table \"message\" violates foreign key constraint \"message_room_fkey\"" db_section=main pq_error={"code":"foreign_key_violation","constraint":"message_room_fkey","detail":"Key (bridge_id, room_id, room_receiver)=(, [email protected], 4915775353114) is not present in table \"portal\".","file":"ri_triggers.c","line":"2608","routine":"ri_ReportViolation","schema":"public","severity":"ERROR","table":"message"}

This bridge has been running for years and is managed with https://github.com/spantaleev/matrix-docker-ansible-deploy

[romano21A]

I have the same issue. It prevents the bridge from starting.

When downgrading to v0.10.9 it works again
(I'm also using the ansible project mentioned above. I had to checkout commit 12ed373d to get v0.10.9 working, so it's not impossible that it's a configuration issue in the ansible project)

[romano21A]

I still the same errors on v0.11.1
There is also a "transaction took to long" error right before it:

Nov 21 17:30:41 vps2 matrix-mautrix-whatsapp[490416]: 2024-11-21T16:30:41.279Z WRN legacymigrate.go:153:CheckLegacyDB() > Transaction took long action="migrate legacy db" db_txn_id=A5wBuCdGUX4g duration_seconds=2.4931105909999998
Nov 21 17:30:41 vps2 matrix-mautrix-whatsapp[490416]: 2024-11-21T16:30:41.279Z FTL Failed to migrate legacy database error="pq: insert or update on table \"message\" violates foreign key constraint \"message_room_fkey\"" db_section=main pq_error={"code":"foreign_key_violation","constraint":"message_room_fkey","detail":"Key (bridge_id, room_id, room_receiver)=(, [email protected], ) is not present in table \"portal\".","file":"ri_triggers.c","line":"2608","routine":"ri_ReportViolation","schema":"public","severity":"ERROR","table":"message"}

[romano21A]

I managed to resolve it by manually editing the database. The problem is that some messages reference a portal which doesn't exist. My guess is that this could be caused by deleting a bridged room in Matrix? It would fit with the messages I found.

These were the commands I ran:

-- show the chat_jid's for the offending messages (they seem to be phone numbers)
SELECT DISTINCT m.chat_jid FROM message m WHERE NOT EXISTS (SELECT 1 FROM portal p WHERE m.chat_jid = p.jid);
-- show the unix timestamp for the newest message with a given chat_jid 
SELECT MAX(timestamp) FROM message WHERE chat_jid = 'XXXXXXXXXX';
-- deletes all problematic messages
DELETE FROM message m WHERE NOT EXISTS (SELECT 1 FROM portal p WHERE m.chat_jid = p.jid);

It obviously deletes stuff (but probably nothing important if the corresponding matrix rooms are already gone anyway), so I made backup first.