SQLite issue merging backup with desktop data

[fattybenji]

Hello, thanks for the tool it's pretty cool (especially since Signal seems to have issues with backups...).

I'm trying to merge a small backup with all the data from the deskop version following an issue with restore crashing the app (yay, fun, lost everything).

I'm getting the following error when running the command:

Error : During sqlite3_prepare_v2(): malformed database schema (messages_on_insert_insert_mentions) - near ">>": syntax error
  Query: "SELECT MAX(MAX(json_extract(json, '$.received_at_ms')),MAX(received_at)) FROM messages"
Error : During sqlite3_prepare_v2(): malformed database schema (messages_on_insert_insert_mentions) - near ">>": syntax error
  Query: "SELECT rowid,id,e164,type,LOWER(uuid) AS 'uuid',groupId,IFNULL(json_extract(json,'$.isArchived'), false) AS 'is_archived',IFNULL(json_extract(json,'$.isPinned'), false) AS 'is_pinned',IFNULL(json_extract(json,'$.groupId'),'') AS 'json_groupId',IFNULL(json_extract(json,'$.derivedGroupV2Id'),'') AS 'derivedGroupV2Id',IFNULL(json_extract(json,'$.groupVersion'), 1) AS groupVersion FROM conversations WHERE json_extract(json, '$.messageCount') > 0"

Before the error message, I'm getting the following text:

<whole bunch of ids>
BACKUPFILE VERSION: 1
COUNTER: 1893640973
Reading backup file...
FRAME 3358 (100.0%)... Read entire backup file...

done!
Database version: 198

So it looks like my backup is OK, and the database is detected, but reading it fails.

I am having the issue with the latest version of the Signal desktop app (6.27.1 for M1 macOS), and the latest version of this tool. I also had it with a previous version of this tool. I'm running this tool in a docker image based on Ubuntu with sqlite version 3.37.2-2ubuntu0.1 but I also checked and had the same issue with the Fedora based docker image.

Any idea ?

[bepaald]

The malformed database schema usually means the sqlite version used to read the database is too outdated compared to the one used to create the database (the one used by Signal Desktop in this case). Looking at that sqlite version, I'd say that is a plausible theory in this case as well. I've heard this same problem happening before when the Fedora docker (the one linked on this site) is used, which is getting pretty old as well.

I think the first thing to do would be to update your sqlite3 version in any way you can. Maybe the Ubuntu docker can be updated? In the Fedora docker, I see the Fedora version listed on line 20, maybe it's as simple as changing that to 37, or whatever the current Fedora version is? (disclaimer: I have no experience with docker at all). If you have homebrew installed, building and running this program on macOs natively should also be simple (see here). Alternatively, you could use any other computer or virtual machine with any (recent) Linux or Windows version installed and compile/download the Windows executable.

Please let me know how it goes once you have a more recent sqlite3 version working.

Thanks!

[fattybenji]

It looks like it works with the macOS package ! It looks like I'm running version 3.39.2 and using a recent Ubuntu I get the 3.40.1. I still have an issue restoring but that's not because of your package, just an OOM from the Signal app 😢

Thank you very much !

[bepaald]

Thanks for reporting back!