after upgrade to 0.1.9 mautrix-whatsapp fails to start

[renaudallard]

After upgrade to 0.1.9 mautrix-whatsapp fails to start with this error message. I am using the default sqlite db.
[Oct 29, 2021 08:24:57] [DEBUG] Running database upgrades
[Oct 29, 2021 08:24:57] [Database/Upgrade/INFO] Database currently on v21, latest: v24
[Oct 29, 2021 08:24:57] [Database/Upgrade/INFO] Upgrading database to v22: Remove message content from local database
[Oct 29, 2021 08:25:01] [FATAL] Failed to initialize database: FOREIGN KEY constraint failed

[rreuvekamp]

I'm having this issue too. Haven't updated in a while (few months), so that might explain.

Edit: Searching through the #mautrix-whatsapp Matrix room, tulir said that running the following SQL query should return no results, but it does for me, which might explain the issue.

SELECT DISTINCT(chat_jid, chat_receiver) FROM message LEFT JOIN portal ON portal.jid=message.chat_jid AND portal.receiver=message.chat_receiver WHERE portal.jid IS NULL;

Not posting the results as they're basically my buddies phone numbers.

[renaudallard]

I don't have the same results as you as it seems.

SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> SELECT DISTINCT(chat_jid, chat_receiver) FROM message LEFT JOIN portal ON portal.jid=message.chat_jid AND portal.receiver=message.chat_receiver WHERE portal.jid IS NULL;
Error: row value misused

[jdfranel]

I have the same error as @renaudallard .

[Nov  2, 2021 08:18:19] [INFO] Initializing mautrix-whatsapp 0.1.9 (Oct 28 2021, 13:18:18)
[Nov  2, 2021 08:18:19] [DEBUG] Initializing database connection
[Nov  2, 2021 08:18:19] [DEBUG] Initializing state store
[Nov  2, 2021 08:18:19] [DEBUG] Initializing Matrix event processor
[Nov  2, 2021 08:18:19] [DEBUG] Initializing Matrix event handler
[Nov  2, 2021 08:18:19] [DEBUG] Bridge built with end-to-bridge encryption, but disabled in config
[Nov  2, 2021 08:18:19] [INFO] Bridge initialization complete, starting...
[Nov  2, 2021 08:18:19] [DEBUG] Running database upgrades
[Nov  2, 2021 08:18:19] [Database/Upgrade/INFO] Database currently on v21, latest: v24
[Nov  2, 2021 08:18:19] [Database/Upgrade/INFO] Upgrading database to v22: Remove message content from local database
[Nov  2, 2021 08:18:20] [FATAL] Failed to initialize database: FOREIGN KEY constraint failed

[Eskuero]

Same issue, I was on 0.1.8 and I compiled 0.1.9 but it fails to upgrade

[Nov  2, 2021 10:36:36] [INFO] Initializing mautrix-whatsapp 0.1.9+dev.unknown (unknown)
[Nov  2, 2021 10:36:36] [DEBUG] Initializing database connection
[Nov  2, 2021 10:36:36] [DEBUG] Initializing state store
[Nov  2, 2021 10:36:36] [DEBUG] Initializing Matrix event processor
[Nov  2, 2021 10:36:36] [DEBUG] Initializing Matrix event handler
[Nov  2, 2021 10:36:36] [DEBUG] Bridge built with end-to-bridge encryption, but disabled in config
[Nov  2, 2021 10:36:36] [INFO] Bridge initialization complete, starting...
[Nov  2, 2021 10:36:36] [DEBUG] Running database upgrades
[Nov  2, 2021 10:36:36] [Database/Upgrade/INFO] Database currently on v21, latest: v24
[Nov  2, 2021 10:36:36] [Database/Upgrade/INFO] Upgrading database to v22: Remove message content from local database
[Nov  2, 2021 10:36:36] [FATAL] Failed to initialize database: FOREIGN KEY constraint failed

[renaudallard]

This issue seems quite common. Unfortunately, I will need to block the package for mautrix-whatsapp on OpenBSD at the former version until this issue can be solved.

[Eskuero]

I saw you said 0.1.9 was the last to target previous Whatsapp API but could there be a 0.1.9.1 since this is breaking the last update?

[renaudallard]

I agree that releasing 0.1.9.1 would be a good idea for package maintainers. 0.1.9 is broken and cannot be packaged as is.

[Eskuero]

It seems like there's no branch other than master and I think he might have started developing towards new multidevice API so I guess the creation of a new "legacy" branch or something of the sort would be needed to backport and tag this.

[renaudallard]

Just for confirmation, diff db44ef2 solves the issue.

[Eskuero]

Note that I applied this patch over the 0.1.9 tag but because the update previously failed halfway it seems that it couldn't continue correctly:

....
[Nov  2, 2021 11:16:59] [DEBUG] Running database upgrades
[Nov  2, 2021 11:16:59] [Database/Upgrade/INFO] Database currently on v21, latest: v24
[Nov  2, 2021 11:16:59] [Database/Upgrade/INFO] Upgrading database to v22: Remove message content from local database
[Nov  2, 2021 11:16:59] [FATAL] Failed to initialize database: there is already another table or index with this name: old_message

If you were a fool like me and only had a week old backup you may need to intervene with SQLite directly:

sqlite> .open mautrix-whatsapp.db         
sqlite> DROP TABLE old_message;
sqlite> .close

Then the remaining updates worked fine:

....
[Nov  2, 2021 11:23:00] [Database/Upgrade/INFO] Database currently on v21, latest: v24
[Nov  2, 2021 11:23:00] [Database/Upgrade/INFO] Upgrading database to v22: Remove message content from local database
[Nov  2, 2021 11:23:00] [Database/Upgrade/INFO] Upgrading database to v23: Replace VARCHAR(255) with TEXT in the database
[Nov  2, 2021 11:23:00] [Database/Upgrade/INFO] Upgrading database to v24: Replace VARCHAR(255) with TEXT in the crypto database
.....
[Nov  2, 2021 11:23:00] [INFO] Bridge started!

[jdfranel]

I pulled 0.1.10 and everything is fine now.
Thanks @tulir